Importing Multiple Excel Files Into Access

[kkadakia]

I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to import them into Access using a button. The data contained in the excel files is similar, so there should no formatting issues while importing.

I searched through the forums and found the code by mmccarthy for importing excel files. I tried using that code, but it keeps giving me a error message "No files found"

Private Sub Command3_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 = "C:\Documents and Settings\KK\Desktop\Test\"
 
  '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"
  End If
 
  'cycle through the list of files
  For intFile = 1 To UBound(strFileList)
    filename = path & strFileList(intFile)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "tblClientMail", filename, True
  Next intFile
 
  DoCmd.SetWarnings True
 
End Sub
 

Can someone tell me what I might be doing wrong ??

Thanks.

[MMcCarthy]

So all these *.xls files are sitting in a folder called test on your desktop?

[puppydogbuddy]

Try placing the wildcard symbol in your last subdirectory in your file path:


path = "C:\Documen ts and Settings\KK\Des ktop\Test\*.*"

[kkadakia]

mmccarthy - Yes, all these .xls files on my desktop in a folder named Test. I had deleted the line Exit Sub below the MsgBox. Was that the correct thing to do ?

puppydog - I tried that, but still tells me No files found. When I then click on Ok, it tells me Run-time error '9': Subscript out of range. When I click on debug, it takes me to this line in the code - For intFile = 1 To UBound(strFileL ist)

Thanks.

[MMcCarthy]

mmccarthy - Yes, all these .xls files on my desktop in a folder named Test. I had deleted the line Exit Sub below the MsgBox. Was that the correct thing to do ?

puppydog - I tried that, but still tells me No files found. When I then click on Ok, it tells me Run-time error '9': Subscript out of range. When I click on debug, it takes me to this line in the code - For intFile = 1 To UBound(strFileL ist)

Thanks.

Set a breakpoint on line14 in the example. When the code breaks mouse over the strFile variable and see what value it contains.

[kkadakia]

I am not sure if this is what u wanted. But after creating the breakpoint, it tells me strFile = ""

How do i check the strFile variable value ?

[MMcCarthy]

I am not sure if this is what u wanted. But after creating the breakpoint, it tells me strFile = ""

Now with the same breakpoint mouse over path and see if it is set to where you think?

Next go to your desktop and open the test folder. Right click on any of the *.xls files and go to properties. Check the Location. Make sure it letter for letter corresponds to the path in your file.

Lastly are you sure these files have an *.xls extension.

[kkadakia]

Now with the same breakpoint mouse over path and see if it is set to where you think?

Next go to your desktop and open the test folder. Right click on any of the *.xls files and go to properties. Check the Location. Make sure it letter for letter corresponds to the path in your file.

Lastly are you sure these files have an *.xls extension.

When taking the mouse the path, it is exactly where i want to select the excel files from.

The properties of the folder also shows the same location.

All the files do have a .xls extension.


When my code breaks down, and i click on debug, it points to For intFile = 1 To UBound(strFileL ist)

[MMcCarthy]

When taking the mouse the path, it is exactly where i want to select the excel files from.

The properties of the folder also shows the same location.

All the files do have a .xls extension.


When my code breaks down, and i click on debug, it points to For intFile = 1 To UBound(strFileL ist)

Thats because you removed the End Sub. The code is continuing without any files available.

There doesn't seem to be any problem with the code I don't understand why the files can't be found.

[kkadakia]

Thats because you removed the End Sub. The code is continuing without any files available.

There doesn't seem to be any problem with the code I don't understand why the files can't be found.

So do i need to put the EndSub back ?

Also, i am using Access 07, though it is saved as Access 03. Could that be a issue ?