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"
[CODE=vb]
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.SetWarnin gs False
path = "C:\Documen ts and Settings\KK\Des ktop\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(int File) = 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(strFileL ist)
filename = path & strFileList(int File)
DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel3, "tblClientMail" , filename, True
Next intFile

DoCmd.SetWarnin gs True

End Sub
[/CODE]
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 ?

[MMcCarthy]

So do i need to put the EndSub back ?

yes

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

This can always be an issue. My advice is to run a compile on the database (can be found in the debug menu) and see if any errors appear.

Is there any reason this file cannot be opened in 03 or converted to 07

[kkadakia]

yes


This can always be an issue. My advice is to run a compile on the database (can be found in the debug menu) and see if any errors appear.

Is there any reason this file cannot be opened in 03 or converted to 07

Where do i enter the End Sub statement ? before or after line 25 ?

[MMcCarthy]

Where do i enter the End Sub statement ? before or after line 25 ?

before it under the msgbox

[kkadakia]

before it under the msgbox

It gives a error message "Block If without End If"

[MMcCarthy]

It gives a error message "Block If without End If"

Does it indicate where this error is occuring. The only If statement in this code is the following:

[CODE=vb]
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
End Sub
End If
[/CODE]