Importing Multiple Excel Files to a single Table in Access (1 Viewer)

Runawaygeek · Mar 28, 2016

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:

Code:

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 · Mar 28, 2016

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 · Mar 28, 2016

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 · Mar 28, 2016

More info on both:

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

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

Runawaygeek · Mar 28, 2016

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 · Mar 28, 2016

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 · Mar 28, 2016

So to help debug, i wrote the following,

Code:

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 · Mar 28, 2016

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

Runawaygeek · Mar 29, 2016

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 · Mar 29, 2016

pbaldy said:
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 · Mar 29, 2016

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 · Mar 29, 2016

Hi,

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

my working code is:

Code:

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.

Importing Multiple Excel Files to a single Table in Access (1 Viewer)

Runawaygeek

Registered User.

pbaldy

Wino Moderator

Runawaygeek

Registered User.

pbaldy

Wino Moderator

Runawaygeek

Registered User.

pbaldy

Wino Moderator

Runawaygeek

Registered User.

pbaldy

Wino Moderator

Runawaygeek

Registered User.

Runawaygeek

Registered User.

Cronk

Registered User.

Runawaygeek

Registered User.

Similar threads

Users who are viewing this thread