Import all excel files within a given directory? (1 Viewer)

rsmonkey

Registered User.
Local time
Today, 05:02
Joined
Aug 14, 2006
Messages
298
Hi all,

as the title says i want to create a script that will import all excel files in a give directory and put them all into 1 temp table within access. All the excel spreadsheets are in exactly the same format.

I can import a single spreadsheet into access no problems but as far as i can see the doCmd.TransferSpreadSheet command is for a single spreadsheet and im gonna try and put it into a loop but im positive there must be a better way to do this.

anyhelp is much appreciated!

Cheers
 

rsmonkey

Registered User.
Local time
Today, 05:02
Joined
Aug 14, 2006
Messages
298
typical i got the bloody loop to work 20 mins after i post...

anywho incase some1 is interested:

Code:
Public Sub Command0_Click()

Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
 
  DoCmd.SetWarnings False
  path = "S:\DEPT463\09 Assets (TS)\01 Software\00 Software License Management\August 07\August 2007_RAG_HO & SHARED SERVICES\"
 
  'Loop through the folder & build file list
  strFile = Dir(path & "*.xls")
 
  While strFile <> ""
     'add files to the list
     intFile = intFile + 1
     ReDim Preserve strFileList(1 To intFile)
     strFileList(intFile) = strFile
      strFile = Dir()
  Wend
 
  'see if any files were found
  If intFile = 0 Then
    MsgBox "No files found"
    Exit Sub
  End If
 
  'cycle through the list of files
  For intFile = 1 To UBound(strFileList)
    filename = path & strFileList(intFile)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Temp_Base_Install_Data", filename, True
  Next intFile
 
  DoCmd.SetWarnings True
 
End Sub

job done
 

Tanner65

Registered User.
Local time
Today, 07:02
Joined
Aug 31, 2007
Messages
66
Awesome. I was actually looking for something like this!
 

tehNellie

Registered User.
Local time
Today, 13:02
Joined
Apr 3, 2007
Messages
751
Ta muchly, that's just saved me a few minutes work. I might try and enhance this a little to take into account sheets that contain the same data but where some muppet keeps changing the bloody column headings.
 

Users who are viewing this thread

Top Bottom