Editing data in a seperate spreadsheet

Hello again.

I know this is do-able, I just can't seem to find a clear cut answer on it.

I have 2 excel workbooks (2 seperate files). one is generated as a .csv from a website. It is used to update another workbook, which is a .xlsx.

If I have a macro (from the 'personal' file, let's just say) running for the first workbook, how can I cause it to make changes to the 2nd workbook? does the other workbook need to be open? I want to use the information from the first workbook to update information on the 2nd.

I tried to dim an array on the 2nd workbook by using:
Code:

Please Login or Register  to view this content.

This is giving me a runtime error '9': Subscript out of range

I understand there is probably not a clean cut answer for this, so feel free to elaborate, or share a link where I can get information on what the proper methods are for referencing separate workbooks.

You can open the other workbook with code below that includes some basic error handling for testing that

• The file exists in a specified location
• The sheet name exists in the file

The code also disables ScreenUpdating and any event driven macros that may run when the second workbook has opened

While I it is strictly possible to modify a closed workbook with ADO code this technique has a limited scope, you will find that people will open workbooks in the background, manipulate then, and then save/close or just close them

Please Login or Register  to view this content.