importing multiple excel spreadsheets into access

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
Back
Top