By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Importing Multiple Excel Files Into Access

P: 13
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"
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2. Dim strFile As String 'Filename
  3. Dim strFileList() As String 'File Array
  4. Dim intFile As Integer 'File Number
  5. Dim filename As String
  6. Dim path As String
  7.  
  8.   DoCmd.SetWarnings False
  9.   path = "C:\Documents and Settings\KK\Desktop\Test\"
  10.  
  11.   'Loop through the folder & build file list
  12.   strFile = Dir(path & "*.xls")
  13.  
  14.   While strFile <> ""
  15.      'add files to the list
  16.      intFile = intFile + 1
  17.      ReDim Preserve strFileList(1 To intFile)
  18.      strFileList(intFile) = strFile
  19.       strFile = Dir()
  20.   Wend
  21.  
  22.   'see if any files were found
  23.   If intFile = 0 Then
  24.     MsgBox "No files found"
  25.   End If
  26.  
  27.   'cycle through the list of files
  28.   For intFile = 1 To UBound(strFileList)
  29.     filename = path & strFileList(intFile)
  30.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "tblClientMail", filename, True
  31.   Next intFile
  32.  
  33.   DoCmd.SetWarnings True
  34.  
  35. End Sub
  36.  
Can someone tell me what I might be doing wrong ??

Thanks.
May 17 '07 #1
Share this Question
Share on Google+
28 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
So all these *.xls files are sitting in a folder called test on your desktop?
May 17 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
Try placing the wildcard symbol in your last subdirectory in your file path:


path = "C:\Documents and Settings\KK\Desktop\Test\*.*"
May 17 '07 #3

P: 13
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.
May 17 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 17 '07 #5

P: 13
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 ?
May 17 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 17 '07 #7

P: 13
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)
May 17 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 17 '07 #9

P: 13
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 ?
May 17 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
May 17 '07 #11

P: 13
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 ?
May 17 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Where do i enter the End Sub statement ? before or after line 25 ?
before it under the msgbox
May 17 '07 #13

P: 13
before it under the msgbox
It gives a error message "Block If without End If"
May 17 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. 'see if any files were found
  2. If intFile = 0 Then
  3.     MsgBox "No files found"
  4.     End Sub
  5. End If
  6.  
May 17 '07 #15

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

Expand|Select|Wrap|Line Numbers
  1. 'see if any files were found
  2. If intFile = 0 Then
  3.     MsgBox "No files found"
  4.     End Sub
  5. End If
  6.  
When i inserted End Sub after the Msg, and ran it, it highlights the End Sub statement and displays that error.
May 17 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Exit Sub
not

Expand|Select|Wrap|Line Numbers
  1. End Sub
May 17 '07 #17

P: 13
Sorry my fault.

Should be

Expand|Select|Wrap|Line Numbers
  1. Exit Sub
not

Expand|Select|Wrap|Line Numbers
  1. End Sub
I still get the msg Files not found. I wonder why.
May 17 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 17 '07 #19

Denburt
Expert 100+
P: 1,356
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:

Expand|Select|Wrap|Line Numbers
  1.   MsgBox "No files found"
  2.     DoCmd.SetWarnings True
  3.    Exit Sub
  4.   End If
  5.  
May 17 '07 #20

Denburt
Expert 100+
P: 1,356
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.
May 17 '07 #21

P: 13
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.
May 18 '07 #22

P: 13
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.
May 18 '07 #23

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. 'use this wherever appropriate
  2. If GetFileD = False Then Exit Sub
  3.  
Expand|Select|Wrap|Line Numbers
  1. Public Function GetFileD() As Boolean
  2. Dim myDialog As FileDialog
  3.  Dim vrtSelectedItem As Variant
  4.  Dim strSearchPath As String
  5.  Dim Cnt As Integer
  6. Set myDialog = Application.FileDialog(msoFileDialogOpen)
  7.   Dim strDbName As String
  8. strSearchPath = strDbName
  9. With myDialog
  10.     .AllowMultiSelect = True
  11.      .Filters.Add "Excel Files", "*.xls", 1
  12.     .Title = "Please Locate the Files to Import!"
  13.     .InitialFileName = strSearchPath
  14.      If .Show = -1 Then
  15.  
  16.     For Each vrtSelectedItem In .SelectedItems
  17.         'The importit is a function you can use to import your files.
  18.         ImportIt (vrtSelectedItem)
  19.         GetFileD = True
  20.     Next vrtSelectedItem
  21.         MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
  22.         'The user pressed Cancel.
  23.         Else
  24.         End If
  25.    Set myDialog = Nothing
  26. End With
  27. End Function
  28.  
May 18 '07 #24

P: 13
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.

Expand|Select|Wrap|Line Numbers
  1. 'use this wherever appropriate
  2. If GetFileD = False Then Exit Sub
  3.  
Expand|Select|Wrap|Line Numbers
  1. Public Function GetFileD() As Boolean
  2. Dim myDialog As FileDialog
  3.  Dim vrtSelectedItem As Variant
  4.  Dim strSearchPath As String
  5.  Dim Cnt As Integer
  6. Set myDialog = Application.FileDialog(msoFileDialogOpen)
  7.   Dim strDbName As String
  8. strSearchPath = strDbName
  9. With myDialog
  10.     .AllowMultiSelect = True
  11.      .Filters.Add "Excel Files", "*.xls", 1
  12.     .Title = "Please Locate the Files to Import!"
  13.     .InitialFileName = strSearchPath
  14.      If .Show = -1 Then
  15.  
  16.     For Each vrtSelectedItem In .SelectedItems
  17.         'The importit is a function you can use to import your files.
  18.         ImportIt (vrtSelectedItem)
  19.         GetFileD = True
  20.     Next vrtSelectedItem
  21.         MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
  22.         'The user pressed Cancel.
  23.         Else
  24.         End If
  25.    Set myDialog = Nothing
  26. End With
  27. End Function
  28.  
Thanks.

But it tells me Expected End Sub...I inserted the End Sub statement at the end, but it still gives me that error.
May 18 '07 #25

Denburt
Expert 100+
P: 1,356
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.
May 18 '07 #26

P: 1
I tried the code. It works fine

Adam.
Sep 26 '07 #27

P: 24
i think you might need to use:

Expand|Select|Wrap|Line Numbers
  1. Dir$(curr_dir & "*.*")
not just dir
Sep 26 '07 #28

P: 9
i think you might need to use:

Expand|Select|Wrap|Line Numbers
  1. 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
Jan 15 '08 #29

Post your reply

Sign in to post your reply or Sign up for a free account.