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:\Documents and Settings\KK\Desktop\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(strFileList)

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(strFileList)

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(strFileList)

[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(strFileList)

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:

'see if any files were found
If intFile = 0 Then
    MsgBox "No files found"
    End Sub
End If
 

[kkadakia]

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

'see if any files were found
If intFile = 0 Then
    MsgBox "No files found"
    End Sub
End If
 

When i inserted End Sub after the Msg, and ran it, it highlights the End Sub statement and displays that error.

[MMcCarthy]

When i inserted End Sub after the Msg, and ran it, it highlights the End Sub statement and displays that error.

Sorry my fault.

Should be

Exit Sub

not

End Sub

[kkadakia]

Sorry my fault.

Should be

Exit Sub

not

End Sub

I still get the msg Files not found. I wonder why.

[MMcCarthy]

I still get the msg Files not found. I wonder why.

Did it compile OK?

Did you try running it in 03 or save a copy and convert it to 07 to see if you still have a problem.

[Denburt]

One quick point, dont forget to reset your message warning before you exit or you will find yourself saving changes deleting things completely by accident and never know it:

  MsgBox "No files found"
    DoCmd.SetWarnings True
   Exit Sub
  End If
 

[Denburt]

After you answer Mary's last post try checking the very first command and see where it stands, that will help.

Your code appears to be fine yet I have seen some strange and abnormal behavior with the DIR function before.

[kkadakia]

Did it comile OK?

Did you try running it in 03 or save a copy and convert it to 07 to see if you still have a problem.

Still does not work. I tried running in 03, but gives me same message.

When I created a break point, and ran the mouse over the path it shows " " instead of the actual path.

[kkadakia]

After you answer Mary's last post try checking the very first command and see where it stands, that will help.

Your code appears to be fine yet I have seen some strange and abnormal behavior with the DIR function before.

What do you recommend I do with the 1st line ?

Thanks.

[Denburt]

Obviously there is an issue with the path or file names somewhere. Try using the following function to select the files you want and I am sue that you will be able to move on to the next level.

'use this wherever appropriate
If GetFileD = False Then Exit Sub
 

Public Function GetFileD() As Boolean
Dim myDialog As FileDialog
 Dim vrtSelectedItem As Variant
 Dim strSearchPath As String
 Dim Cnt As Integer
Set myDialog = Application.FileDialog(msoFileDialogOpen)
  Dim strDbName As String
strSearchPath = strDbName
With myDialog
    .AllowMultiSelect = True
     .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Please Locate the Files to Import!"
    .InitialFileName = strSearchPath
     If .Show = -1 Then
 
    For Each vrtSelectedItem In .SelectedItems
        'The importit is a function you can use to import your files.
        ImportIt (vrtSelectedItem)
        GetFileD = True
    Next vrtSelectedItem
        MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
        'The user pressed Cancel.
        Else
        End If
   Set myDialog = Nothing
End With
End Function
 

[kkadakia]

Obviously there is an issue with the path or file names somewhere. Try using the following function to select the files you want and I am sue that you will be able to move on to the next level.

'use this wherever appropriate
If GetFileD = False Then Exit Sub
 

Public Function GetFileD() As Boolean
Dim myDialog As FileDialog
 Dim vrtSelectedItem As Variant
 Dim strSearchPath As String
 Dim Cnt As Integer
Set myDialog = Application.FileDialog(msoFileDialogOpen)
  Dim strDbName As String
strSearchPath = strDbName
With myDialog
    .AllowMultiSelect = True
     .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Please Locate the Files to Import!"
    .InitialFileName = strSearchPath
     If .Show = -1 Then
 
    For Each vrtSelectedItem In .SelectedItems
        'The importit is a function you can use to import your files.
        ImportIt (vrtSelectedItem)
        GetFileD = True
    Next vrtSelectedItem
        MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
        'The user pressed Cancel.
        Else
        End If
   Set myDialog = Nothing
End With
End Function
 

Thanks.

But it tells me Expected End Sub...I inserted the End Sub statement at the end, but it still gives me that error.

[Denburt]

ok it would appear that we need to back way up here. at the top of this form module it should look like:

Option Compare Database
Option Explicit

Then press the menu button Debug then the button Compile, if your a keyboard nut press alt. D, L and it should compile hopefully with no errors. If you do get an error then it should give you an error response with a button that says debug. Clicking on debug should highlight a particular line of code. What we need is this highlighted line and a line or two above and below so we can help diagnose the problem.

[Sandtyre]

I tried the code. It works fine

Adam.

[TSIGOS1]

i think you might need to use:

Dir$(curr_dir & "*.*")

not just dir

[bhapate1]

i think you might need to use:

Dir$(curr_dir & "*.*")

not just dir

This is a Simple code to import all the Excel files into Access.

Function Impo_allExcel()

Dim myfile
Dim mypath
mypath = "n:\importxls\aramiska\"
Do
myfile = Dir(mypath & "*.xls")
'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "aramiskaimport2", mypath & myfile
myfile = Dir
Loop Until myfile = ""

End Function