Get information from two different open workbooks with varying names...ack!

[raeannb]

I'm desperate! So here's the issue:

I have two workbooks that are updated regularly. The workbook names won't change from one update to the next, except for the value of a date at the end of the name (format 01Jan2011). In other words, one workbook is named "FirstWorkbook 13Sep2011.xlsx" and the other "SecondWorkbook 21Aug2011.xlsm." I want to use information from one workbook to update a table on the other. I have two branches of code, one collecting the data I want from the first WB, and the other displaying it in the second. My problem is that I can't figure out how to go from one WB to the other. I've searched for about 2 hours (Ack!) for something useful, with no luck. I've also tried a bajillion things, but here are a few examples of what did NOT work:

myWB = Windows("FirstWorkbook #########.xlsm").Name
mySummary = Windows("SecondWorkbook #########.xlsx").Name

I also tried replacing "Windows" with "Workbooks" and still had trouble.

I've also tried creating a new sub and calling it:

Sub GetFirstWorkbook ()
Dim WB As Workbook
For Each WB In Workbooks
If WB.Name Like "FirstWorkbook #########.xlsm" Then GoTo ActivateIt
Next WB
Exit Sub
ActivateIt:
Windows(WB.Name).Activate
End Sub

This works ok in isolation (during the data-gathering part), but doesn't work when I try to use it to go from one WB to the other.

There are probably more variations I've attempted, but my brain is fried like an egg and I can't remember them all. I'd LOVE help, and be eternally grateful to anyone who can save me from this nastiness.

Thank you!!

 

[Andrew Poulsom]

When you open the workbook assign it to an object variable. Thereafter you can use that variable in place of Workbooks(...). Example:

Dim wb1 As Workbook
Set wb1 = Workbooks.Open("C:/MyFolder/MyWorkbook.xls")
MsgBox wb1.Name

Change the path to suit.

 

[raeannb]

Awesome! I'm not entirely sure how that works (sorry! I'm a newbie), so to clarify - will that program open the workbooks for me? Or do I open them myself while the program is running (how does that work?)? Also - I have this macro saved on one of the workbooks that I'm using (the first one) so I need it open already in order to run the macro...right? Is there a better way to set it up?

Thank you!!!

 

[Andrew Poulsom]

You can refer to the workbook that contains your code as ThisWorkbook. You will need to open the other workbook in code, as I showed you above.

 

[raeannb]

Aweeeesome. You rock. I'm going to fiddle with that for a bit and see if it'll do the trick. Thank you!!

 

[raeannb]

Ok so now it's giving me trouble with the date at the end of the file name. I've tried it using "C:\...\FirstWorkbook #########.xlsx" as well as "\FirstWorkbook *" which I thought would give me the wild characters I need...apparently not. Any tips?

 

[Andrew Poulsom]

You can't use wildcards in workbook names. I thought your problem was referencing the workbooks, not opening them.

 

[raeannb]

Well...it's a combination of both. Do you have any tips? I've been googling and I've seen dozens of suggestions, but nothing I try seems to work. The latest attempt:

wb2 = Directory.GetFiles("C:\...\", "SecondWorkbook *")

Was supposed to work (according to http://www.dreamincode.net/forums/topic/58110-wild-card/), but I'm getting an "Object Required" error. Soooo unpleasant.

 

[Andrew Poulsom]

This may help you:

http://www.mrexcel.com/forum/showthread.php?t=569548

 

[raeannb]

Yes! I sort of pulled from that and from the other post I linked to earlier, and came up with this beeaauutiful (and so simple!) line of code:

myDir = "C:\....long path name here...\"
wb2 = Dir(myDir & "SecondWorkbook*.xlsx")

Thank you so so so very much for your help. I was pulling my hair out. Have a good evening!