Using a macro to copy information from files with different names

[gruittbm]

Is there a way to have a macro copy information from a seperate weekly data file into my main workbook when the file name changes every week? The main part of the excel data file name remains the same, but is updated with the week at the end of the file (i.e. last week: "Weekly Data 3-30-2012", this week: "Weekly Data 4-06-2012"). The data copied is also in the same format every week so I'm trying to get the macro to copy the entire worksheet and paste the data into a worksheet in my workbook calculator...it's just the small change in file name that is throwing me off. Thanks!

 

[gruittbm]

Some additional info...Recording a macro gets me to this point:


Sub Weekly_Data()
'
'
'
Windows("Weekly Data 3-02-12_Webdoc.xls").Activate
Cells.Select
Selection.Copy
Windows("Workbook.xlsm").Activate
Cells.Select
ActiveSheet.Paste
Range("A5:C481").Select
End Sub


Pretty straight forward so far. The weekly data file comes over email once per week and so the date in the file I receive is always different which is where I run into the problem. Is there a way to activate an already open file with a macro without specifically calling a file name like ("Weekly Data 3-02-12_Webdoc.xls")?

Better yet...the file comes over Outlook. Is there a way to copy the data without even opening the file from Outlook directly into my active workbook?

Thanks for any help!

 

[gruittbm]

Is there a macro command that can target the only other open workbook on my desktop besides my calculator?

Anybody have any ideas?

 

[gruittbm]

Here's what I've come up with so far:

Sub Weekly_Data()
    Dim File As String
    File = "Weekly Data*.xls"
 
    Windows(File).Activate
    Sheets("Sheet1").Select
    Cells.Select
    Selection.Copy
    Windows("My Workbook.xlsm").Activate
    Sheets("Weekly Data").Select
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub


</PRE>

I'm getting "Runtime Error 9: Subscript Out of Range" on the third line of code: Windows(File).Activate
I tried changing the syntax of the second and third lines to see if that was the problem but no go. Here are the variations I tried:

<CODE>[FONT=Verdana]File = ("Weekly Data*.xls")[/FONT]
 
[FONT=Verdana]   Windows.Activate (File)[/FONT]</CODE>


</PRE>

Any thoughts?