Importing Multiple Excel Files to a single Table in Access

[Runawaygeek]

Hi all,

I know this subject has been covered and i have been reading a lot of those posts, but i cant find/get to work exactly what i am looking for.

I have a folder location that will get 6 new Excel files added to it each day, with names that mean nothing most of the time.
But the structure of all the files will be the same.

I wish to have a button on a form that will collect these 6 files and import them all onto the same table.

my current code looks like:

Private Sub Button_Import()

Call Import_Excel

End Sub


Function Import_Excel()
Dim myfile
Dim mypath

mypath = "C:\Folder\Input"
ChDir(MyPath)
myfile = Dir("C:\Folder\Input")
Do While myfile <> ""
  If myfile Like "*.xlsx" THEN
     
    DoCmd.TransferSpreadsheet acImport, ,  "Excel_Import", mypath & myfile
  End If
  myfile = Dir("C:\Folder\Input")
Loop
End Function

I get no errors, just nothing happens??

After Import the 6 files will be removed from the folder.

Any help would be great,
thanks,

 

[pbaldy]

Do you know how to set a breakpoint and step through the code? You can also add a message box or Debug.Print to see what

mypath & myfile

resolves to during the loop.

 

[Runawaygeek]

Hi,

I dont know what a break point is, I added a Message Box just to make sure the code was running.

ill give the debug.print a try

cheers

 

[pbaldy]

More info on both:

http://www.baldyweb.com/Debugging.htm

http://www.baldyweb.com/ImmediateWindow.htm

 

[Runawaygeek]

Thank you for that, they are really handy. (as you might have guessed i am pretty new VBA)

So, the window reports back, just the Folder path for both myfile and mypath, to which i guess, means the dir() is not returning the list of .XLSS files within the dir?

should i add *.XLS to the myfile = dir()

Thank you for your help,

 

[pbaldy]

I'm not sure your code will work, but I don't work much with iterating over files. I just tested Allen's ListFiles and it worked:

http://allenbrowne.com/ser-59.html

 

[Runawaygeek]

So to help debug, i wrote the following,

Dim mypath As String
Dim myfile As String

mypath = "C:\Folder\Input"
ChDir (mypath)

If Len(Dir(mypath)) = 0 Then
    MsgBox "No file"
Else
    MsgBox "Files Found"
End If

It tells me that no it cant see any files..

 

[pbaldy]

You'd have to add the backslash at the end.

 

[Runawaygeek]

Thank you, i added the \ but now i get an error

No Installable ISAM found

its looking for some .dll file, but i have no idea which or where

??

 

[Runawaygeek]

I'm not sure your code will work, but I don't work much with iterating over files. I just tested Allen's ListFiles and it worked:

allenbrowne com/ser-59

I am not sure how this is to be structured into my current code?

 

[Cronk]

The way to loop through all XLSX files in a directory is

myfile = Dir("C:\Folder\Input\*.xlsx")
do while myfile <> ""
(do whatever with myfile)
myfile = Dir
loop

 

[Runawaygeek]

Hi,

Thank you for helping, with your links and info I have debugged and now resolved my issue.

my working code is:

Private Sub Button_Import()

Call Import_Excel

End Sub


Function Import_Excel()
Dim myfile
Dim mypath

mypath = "C:\Folder\Input\"
myfile = Dir(mypath & "*.xlsx")

Do While myfile <> ""
  If myfile Like "zz*.xlsx" THEN
ELSE     
    DoCmd.TransferSpreadsheet acImport, ,  "Excel_Import", mypath & myfile
    Name mypath & myfile AS mypath & "zz" & myfile
End If
  myfile = Dir()
Loop
Msgbox "Load Finished"
End Function

I took out the chdir()
added *.xlsx to the myfile =
and had the Excel files renamed as they were processed so that the loop came to a stop. Where as before it was importing forever and i kept having to force close access.

Thanks everyone.