Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have a client who is using Exponenciel's Excel Templates in Sage's Act! database. He uses the templates to open up 2 spreadsheets, part filled with data from the database. He then fills in some more details on one of the spreadsheets which he then wants to copy and paste into the second spreadsheet. At the moment, he does this manually but wants to be able to do this automatically via pressing a button.

    All very easy, yes? A spot of VBA and Bob's your uncle! Well, no, as the Excel Templates open each spreadsheet in a separate instance of Excel (and Exponenciel tell me that it has to be this way), so they can't communicate with each other via VBA - or can they?

    Short of saving the second sheet, then picking it up in the instance with the first sheet (rather messy - much rather NOT save the second one until completed), I can't think how I'm going to do this. Does anyone have any suggestions?

    TIA

    Keith

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can grab a reference to the other workbook using GetObject and passing the full file path, but your copy and paste options will be limited, just as they are manually. You can however assign values from one to the other just as you would normally.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='786569' date='28-Jul-2009 16:52']You can grab a reference to the other workbook using GetObject and passing the full file path, but your copy and paste options will be limited, just as they are manually. You can however assign values from one to the other just as you would normally.[/quote]

    But if the other workbook has not been saved, then what? I was thinking that maybe I'd have to save (maybe automatically) the second workbook somewhere temporarily and then pick it up in the first instance of Excel. Then naturally, I'd have no problem cutting and pasting.

    So, I'd do:
    1) Open the first spreadsheet (instance 1)
    2) Open the second spreadsheet (instance 2), which contained code to automatically save it to a temporary location, and then immediately close the Excel instance
    3) First instance looks for the temporary spreadsheet and picks it up
    4) Do whatever is required to copy data from sheet 1 to sheet 2

    What do you think?

    Thanks.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I can't think of another choice offhand. Although you can use APIs to find the other window easily enough, I don't know how/if you could use that handle to return an automation object that you could use.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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