Sleeper: Insert worksheet into one or many workbooks

[Sinjin]

First post here...
An Excel workbook (CAR) contains several sheets. Information from various cells on several of the sheets needs to be loaded into a SQL table. I have added a worksheet (called AdminOnly) to the base CAR template and linked the various cells so one row on AdminOnly contains data for SQL. The problem is that the Fiance Group will not let me add the AdminOnly worksheet to the CAR template.

My solution is to use the INDIRECT function on AdminOnly and insert this worksheet on the CAR files when sent in to corporate (all with different names and that can not be changed). I then have a macro to change the AdminOnly row to values and save the file to a folder for exporting into SQL. All of this works OK.

To my question - with the CAR files all have different names, how do I code the macro so that it looks at the other one or many files that I have open in Excel?

I hope this is clear! Thanks!!! Jani

[Jacob Hilderbrand]

You can check all the files open in a particular instance of Excel like this.


Dim Wkb As Workbook
For Each Wkb In Workbooks
'Do Something
Next

[Jacob Hilderbrand]

Jani

Were you able to get this code working properly? Do you still need assistance with it or can we mark it solved?

[Sinjin]

Thanks for checking up on this. No, I have not yet been able to get this to work but must admit I haven't had enough time to really delve into what I'm doing wrong. jms

[Jacob Hilderbrand]

You can try this macro to give you an idea. Post back with more specific information if you need additional assistance.


Dim Wkb As Workbook
For Each Wkb In Workbooks
MsgBox Wkb.Name
Next

You can then use the variable Wkb in the rest of your code as needed. Then move on to the next workbook with the loop.