importing multiple excel spreadsheets into access

[Guest]

I am trying to use the "transferspreadsheet" action through the use of a
macro in order to import multiple excel spreadsheets. Each week a new excel
spreadsheet will be created with the file name in the format
"weekof_mm_dd_yy.xls", and i would like to tell access to read in each week's
file. When the macro asks for a file name, what should I put here so that
this process will automatically read in these files?

thanks!

 

[yipppeee]

You're going to have to use VBA to accomplish this - not a macro.

 

[Guest]

and how would i do that?

You're going to have to use VBA to accomplish this - not a macro.

 

[Jim/Chris]

Use the same name in your macro but rename the weekly file
before hand
Here is the code to rename the file and run a
transferspreadsheet macro. I am assumint he directory only
has the one file in it. Watch out for wordwrap.

Use

Dim stDocName As String
Dim strfilename As String

strfilename = Dir("c:\testimport\*.*")
On Error GoTo Err_Command1_Click

Name "c:\testimport\" & strfilename As
"c:\testimport\NewName.xls"

stDocName = "transferspreadsheet"
DoCmd.RunMacro stDocName

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

Good luck

Jim