Macro to import Excel files

[John Hipskind]

I would like to create a macro to import Excel data into
my Access DB.

My Excel template is named "Cost Proposal." As new cost
proposals are generated, the template file is Saved As
CP_001, CP_002, etc.--these are the files I want to
import.

The TransferSpreadsheet action requires that the imported
file's name be specified in the action. However, since
the imported file names vary, I would like to specify the
file name "on the fly."

Is there a way to work with TransferSpreadsheet to do
this? Or do I need to go another direction?

(I use a named Range in the spreadsheet, and can import
the data just fine manually, but I'd like other users to
just click a button.)

Thanks for your help.

John

 

[Ken Snell]

If "on the fly" means that you want the macro to ask you what the name of
the file is, you can do this in the FileName argument for the
TransferSpreadsheet macro:

= "C:\FolderName\" & InputBox("Enter the filename:")

This will ask you to enter the file name (without the path, as you'll have
hardcoded the path in the argument) and then it will go get that file.

If you want the macro to go to a specific folder and loop through all the
files it finds there, that can be done, but not by a macro. You need VBA
code to do this. Post back if you need info on how to do this.

 

[John Nurick]

Hi John,

I'm not sure whether you want to let the user specify the file at the
time, or whether you want to work out the names automatically.

If the former, you can use the code at
http://www.mvps.org/access/api/api0001.htm to display
the standard File Open dialog to get the filespec.

If the latter, you can use something along these lines to import all
files that match the name pattern:
Dim strFN As String

strFN = Dir("D:\Folder\subfolder\CP_???.xls")
While Len(strFN) > 0
DoCmd.TransferSpreadsheet blah strFN blah
'move file after transfer
Name strFN As "D:\Folder\otherfolder\" & strFN
strFN = Dir() 'get next filename
Loop

I would like to create a macro to import Excel data into
my Access DB.

My Excel template is named "Cost Proposal." As new cost
proposals are generated, the template file is Saved As
CP_001, CP_002, etc.--these are the files I want to
import.

The TransferSpreadsheet action requires that the imported
file's name be specified in the action. However, since
the imported file names vary, I would like to specify the
file name "on the fly."

Is there a way to work with TransferSpreadsheet to do
this? Or do I need to go another direction?

(I use a named Range in the spreadsheet, and can import
the data just fine manually, but I'd like other users to
just click a button.)

Thanks for your help.

John

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.