+ Post New Thread
Results 1 to 5 of 5
Office Software Thread, Macro Problem in Technical; Hi all, im trying to set a excel template for data extract from SIMS. im having a problem because im ...
  1. #1
    jimbojames136's Avatar
    Join Date
    Mar 2011
    Location
    Hull
    Posts
    29
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Macro Problem

    Hi all, im trying to set a excel template for data extract from SIMS.

    im having a problem because im copying formatting and formulas from another workbook while creating the macro to do this. Therefore every time I run the report it needs the other workbook open.
    is there anyway of getting round this other than having to write every formula manually?

    ta
    James

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,487
    Thank Post
    1,445
    Thanked 1,182 Times in 809 Posts
    Rep Power
    709
    Quote Originally Posted by jimbojames136 View Post
    Hi all, im trying to set a excel template for data extract from SIMS.

    im having a problem because im copying formatting and formulas from another workbook while creating the macro to do this. Therefore every time I run the report it needs the other workbook open.
    is there anyway of getting round this other than having to write every formula manually?

    ta
    James
    Copy all the sheets you need from the other workbook in to your template workbook and hide them. Then you can copy from those sheets within the template workbook instead

  3. #3
    jimbojames136's Avatar
    Join Date
    Mar 2011
    Location
    Hull
    Posts
    29
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Rep Power
    0
    no this doesn't seem to work as it still references the other workbook within the formula?

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,487
    Thank Post
    1,445
    Thanked 1,182 Times in 809 Posts
    Rep Power
    709
    Quote Originally Posted by jimbojames136 View Post
    no this doesn't seem to work as it still references the other workbook within the formula?
    Then you need to edit the formula to reference the sheet in your template rather than the other workbook; if you maintain the same sheet names, you can do a find an replace to fix it like so:

    As an example, we'll assume the original workbook was called "Book1.xlsx" and the sheet was called "Sheet1" - if you look at a formula referencing the original workbook, you'll see the references begin '[Book1.xlsx]!Sheet1'

    To fix them, you simply remove the reference to the original workbook using find and replace, replacing the reference with a blank and making sure to match case and check within forumlas rather than values. In the example, this would look like so:

    fr_example.gif

    Note that the "Replace with" text box is completely blank - if you put a space in there you'll break the formulas.

    Final note - make a backup before you try this, just in case!

  5. #5
    jimbojames136's Avatar
    Join Date
    Mar 2011
    Location
    Hull
    Posts
    29
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Rep Power
    0
    also tried that, the macro still references the other workbook to copy over the information.
    it might be that i have the whole process wrong.

    i have designed my report in SIMS.
    taken a copy of the blank reportdata file.
    run SIMS report.
    edit report using record macro feature (this is where i copy formulas and formatting from another workbook)
    stop macro record.
    open VB script editor, take copy of code, and close without saving report)
    open copy of reportdata and past in VB code.
    close
    run report in sims again with the template pointing at your copy of reportdata.

    this could be completely wrong, its my first attempt at running a report like this.

    thanks
    James

SHARE:
+ Post New Thread

Similar Threads

  1. Problems Saving Macro Enable Excel Workbooks on a Network
    By Richard_Finnigan in forum Office Software
    Replies: 2
    Last Post: 20th February 2012, 05:04 PM
  2. Word 2010 macros problem
    By s_tu in forum Office Software
    Replies: 0
    Last Post: 13th July 2011, 02:38 PM
  3. Intermitent problems with logging on
    By alexknight in forum Wireless Networks
    Replies: 27
    Last Post: 22nd August 2005, 04:01 AM
  4. Problems with Google Earth
    By Dos_Box in forum Educational Software
    Replies: 8
    Last Post: 19th August 2005, 02:32 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •