Importing multiple record formatted Excel files?

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

Guest

On a daily basis I want to import multiple excel files (w/one sheet each)
that are in record format. I would like to do this process via VBA but don't
know how. Any adice out there?
•After each excel file is imported successfully I want the file MOVED to
another folder.
•The excel files have different names daily but those files will be located
in a folder that contains just those files.
•I want the records in the excel files to be imported into an existing table
already containing same kind of data from days past - I'll make sure the
fields on the excel side will always contain the same data type but obviously
I can't assign a data type for each field on the excel side (one Date type
and the rest are either Text, Memo or No/Yes data types).
•The files will be emailed from multiple users that are outside in the field.

Appreciate any advice/direction and thanks in advance.
-Warren
 
To determine what Excel files are in the folder, use the Dir statement.

To move a file once you're done with it, use the Name statement

To import a spreadsheet into a table, use the TransferSpreadsheet method.

Something like the following untested air-code:

Dim strBackupFolder As String
Dim strFile As String
Dim strFolder As String

strBackupFolder = "...." ' Make sure you've got a terminating \ on it!
strFolder = "...." ' Make sure you've got a terminating \ on it!

' Get the first file in the folder
strFile = Dir$(strFolder & "*.xls")

Do While Len(strFile) > 0
' strFile will be the name of an Excel file in strFolder
' Import it into table MyTable.
' The code belows assumes that the first row
' of the spreadsheet contains Field names
DoCmd.TransferSpreadsheet _
TransferType := acImport, _
SpreadsheetType := acSpreadSheetTypeExcel8, _
TableName := "MyTable", _
FileName := strFolder & strFile, _
HasFieldNames := True

' Move the file once you're done with it
Name strFolder & strFile As strBackupFolder & strFile

' Get the next file
strFile = Dir$()
Loop


See
http://msdn.microsoft.com/library/en-us/vbaac11/html/acmthactTransferSpreadsheet_HV05186520.asp
for more details about the TransferSpreadsheet method.
 
Back
Top