Importing Excel Files

[gbmarlysis]

Hi.

Access Version 2010
Excel Version 2010

I have 200 excel files in folder C:\Users\Lburch\Documents\Historic Data\Tedan Data in Excel Worksheets - Copy\AAA - Copy
The files only have one worksheet and the column format are the same. Worsheet name is different for every file though.
Headings in first row.

Can someone help me write a macro to import into 1 Access table so I don't have to do it manually. I have looked at a lot of code and cant seem to find a post that works.

Kind Regards

Luke

 

[namliam]

look at the dir function to read the file names

Docmd.transferspreadsheet to import the excel sheets

 

[gbmarlysis]

Hi Mailman,

Heres a challenge.

Are you able to write the code required. I've spent 48hours to only come up with numerous error codes and a handful of new swear words for VBA.

 

[Gasman]

A quick google would give you something like this.

Adapt to suit? That is what I normally do.

http://stackoverflow.com/questions/...iles-into-ms-access-2010-tables-automatically

 

[gbmarlysis]

Hi Gasman,

Yep have searched over 40 websites and trialled the code. I must be doing something wrong and have turned to the forum for some more detailed responses. Thanks though.

 

[Gasman]

Well that link pretty much gives you the code to do what you want, if I understand you correctly.?

You set the path to the files. His is hardcoded, but start off easy,
It then uses the dir() function to step through that folder getting all the excel files and transferring them to a table.?

I have just used similar process to get attachments for emails.

See my post here

 

[gbmarlysis]

Hi,

I put the code in but when I press run. It does nothing. :banghead:

 

[gbmarlysis]

Here is the code I have but returns No files found'

Sub Import_multiple_excel_files()
Const strPath As String = "C:\Users\Lburch\Documents\Data\Data in Excel Worksheets - Copy\AAA - Copy" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.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"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"Table1", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

 

[Gasman]

Well other than adding another level of complexity by using the array, it looks reasonable enough.

Why not walk through the code in the debugger and find out exactly what it is doing, not what you think it should be doing?

That is how I get to the bottom of problems like this.

I notice that you have not coded exactly as he did? You need the fullpath of the file, not just the local name. Unsure as to why you got your message 'No files found' though.

strFile = Dir(strPath & "*.xls")
 Do While Len(strFile) > 0
       strPathFile = strPath & strFile

 

[Gasman]

Try completing the path correctly by putting "\" at the end.

You will still need to use the full path of the file

 

[gbmarlysis]

Hi Gasman,

When i go to the folder where the files are and click on the path to copy and paste it doesnt have a "\" at the end ??

Not sure how to put full path if each file has a different name. My thinking is i can only direct it to the folder to where the files are.

 

[Gasman]

Your thinking is incorrect.

You need the "\" at the end for the full correct path of the folder, so as to be able to concatenate the file name as well.

EG c:\temp is the folder, but c:\tempaccess.xls will not exist because the system is looking for a file called tempaccess.xls in c:\

To indicate to the system that the folder path is complete, you need
c:\temp\ then when you add the file access.xls you would get c:\temp\access.xls and the system can identify the full path name and retrieve the file

If you had copied the guys code you would have seen that he concatenates the path and the file to do what I have just tried to explain. Only then will access/excel or any other program know where the file is.

The path, (notice the last character?)

strPath = "C:\Documents and Settings\myName\My Documents\Access Test\"

The FULL pathname (not just the local filename)

strPathFile = strPath & strFile

Is it making any sense now?

 

[gbmarlysis]

Hi Gasman,

Yep i get it i think, even though there are multiple file names you cant expect access to put a "\" before every one to make it complete the file path, so you need to do it. I added in and it at least started to run but got the error
runtime error 2391 field 'f18' doesnt exist in destination table.
As far as i'm aware all files are the same.

 

[Gasman]

Put out a debug.print of the full pathname in the loop and that will tell you which one is at fault.

Access does not lie, so one if not more are different.

 

[namliam]

When i go to the folder where the files are and click on the path to copy and paste it doesnt have a "\" at the end ??

Oh my ....

Hi Gasman,

Yep i get it i think, even though there are multiple file names you cant expect access to put a "\" before every one to make it complete the file path, so you need to do it. I added in and it at least started to run but got the error
runtime error 2391 field 'f18' doesnt exist in destination table.
As far as i'm aware all files are the same.

When any user has had data in columns beyond the normal "real" table/data excel will keep that as used space.
In this case some user has entered (and possibly deleted) data from column R where you normal data will only go up to and including column S. Resulting in field 18.

Either create a dummy extra column field 18 to import or remove the (now) empty column from the excel file.