Transfer Multiple Spreadsheets

[Tony Pearson]

I have to transfer data from excell spreadsheet to a single access table. However each time I do the transfer it is from a new excell file (the format is always the same though) Currently I manually run a macro but have to write the file name each time. Is there a way I can do an automatic transfer which just asks the user which file they want to transfer from. The excell files are always in the same directory/folder.

I am very much a simpleton with VB so please keep any explanation simple!

Thanks

Tony

 

[PookaTech]

Here is just a little code. It will bring up a msgbox asking the user to input the file name and then do the correct import. It might error if your file names include spaces but I don't think so. I would probably advise saving your macro as VBA and then editing it a little. Add the lines above the docmd line. And then edit the transferspreadsheet method so that the "file name' is "YourDrive\YourDirectory\" & filename & ".xls" as shown below. Use the help file for the transferspreadsheet method; it is good.

Sub ImportXcel()

'Define variables
Dim strfilename As String


'Ask for file name and set equal to variable "strfilename"
strfilename = InputBox("What is the name of the file you wish to import from?")

'import spread sheet (Your macro should look something like this when saved as VBA, The following is one line of code)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "YourTableNameHere", "Drive\Directory\" & filename & ".xls", True, "UpperLeftCell,LowerRightCell"

End Sub

Hope this Helps,
Pookatech

 

[kupe]

Hi Tony Pearson

This is really simple code which Mile-O-Philes gave me recently and it works really well. This is less sophisticated than PookaTech's (which I will borrow, many thanks, PookaTech). Here you name the spreadsheets in the code - or rename the spreadsheets.

The excel pages I was moving over here were named IPrint and numbered 1-17, fairly obviously. They went into a solitary table called "Test".


Private Sub Command1_Click()

Dim intCounter As Integer
For intCounter = 1 To 17

DoCmd.TransferSpreadsheet acImport, 8, "Test" & intCounter, "T:\InformationPrint\" & intCounter & "_IPrint", True, ""

Next intCounter

End Sub

Cheers from a former WindyCityite now embayed in the Midlands - and homesick.