Import mulitple xls files form single folder

  • Thread starter Thread starter Maver1ck666
  • Start date Start date
M

Maver1ck666

Hi everyone,

I need to set up an event where when I click a button, the event fires and
imports all the xls files from a folder (which I have specified in a text box
on a form) into Access 03 using the file names (minus the .xls) as the table
names.

Each xls file has their own field headings.

Any suggestions please?
 
Cheers for that Doug.

Although the code works, I need a seperate table created for each
spreadhseet that is imported whereas this exaple only uses one.

Any suggestions on how to amend the code below so that that a new tale is
created using the xls file name but minus the .xls at the end?

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = [Forms]![frmSettings]![Directory] & "\"

If strPath = "" Then
MsgBox "No path was input.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tblalldata"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop

Kind regards,
Mav
 
strPathFile = strPath & strFile
strTable = Left(strFile, Len(strFile) - 4)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Maver1ck666 said:
Cheers for that Doug.

Although the code works, I need a seperate table created for each
spreadhseet that is imported whereas this exaple only uses one.

Any suggestions on how to amend the code below so that that a new tale is
created using the xls file name but minus the .xls at the end?

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = [Forms]![frmSettings]![Directory] & "\"

If strPath = "" Then
MsgBox "No path was input.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tblalldata"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop

Kind regards,
Mav

Douglas J. Steele said:
Check what Ken Snell has at
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm
Click to expand...
Click to expand...
 
Back
Top