Solved Using VBA to import all excel workbooks from a single folder? (1 Viewer)

[Sampoline]

I have the following code but it isn't importing the excel sheets into the specified table:

Sub Import()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "C://Documents"

strTable = "tblCSV"

strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames

      strFile = Dir()
Loop

End Sub

Code was written to a module and then called to a button on my form. Unless I'm not doing something with my button, wondering if the code above is wrong? What should I be adding to my button.. I just wrote:

Private Sub btnImport_Click()

    Import

End Sub

P.S. Fieldnames from access table and all excels are same.

Thanks.

 

[Galaxiom]

This path doesn't look right:
strPath = "C://Documents"

 

[Sampoline]

This path doesn't look right:
strPath = "C://Documents"

Sorry, I made a mistake while typing. It's supposed to be going to a network location where the spreadsheets are located. Still doesn't work after copying it directly from the address bar of file explorer.

 

[Galaxiom]

There is no slash between the strPath and "*.xlsx"

 

[Sampoline]

There is no slash between the strPath and "*.xlsx"

Hi, I'm not near a computer at the moment. But out of curiosity why would I need a slash between strPath and "*.xlsx"?

Isn't it just file path with the file extension at the end..there shouldn't be a slash there right.. or have I missed something?

 

[Minty]

Nope you are currently trying to look for

C:\Documents*.xlxs

You should be looking for

C:\Documents\*.xlxs

Fundamental difference. There is a great little function you should add to all your databases if you have any dealing with files and file paths.

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Using this will always add a \ to any string if it doesn't have one and you know where you stand.

Also adding in a
Debug.Print strFile
would have highlighted your problem early on.

 

[Isaac]

but it isn't importing the excel sheets into the specified table

What actually happens when you click the button?

 

[Cronk]

In addition to all the other aspects covered above, note that

strFile = Dir()

will return something like
"FileName.xlxs"

You then have to add back the path for the Transferspreadsheet. Something like

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "C:\documents\" & strTable, strPathFile, blnHasFieldNames

 

[Sampoline]

Nope you are currently trying to look for

C:\Documents*.xlxs

You should be looking for

C:\Documents\*.xlxs

Fundamental difference. There is a great little function you should add to all your databases if you have any dealing with files and file paths.

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Using this will always add a \ to any string if it doesn't have one and you know where you stand.

Also adding in a
Debug.Print strFile
would have highlighted your problem early on.

What actually happens when you click the button?

In addition to all the other aspects covered above, note that

strFile = Dir()

will return something like
"FileName.xlxs"

You then have to add back the path for the Transferspreadsheet. Something like

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "C:\documents\" & strTable, strPathFile, blnHasFieldNames

After adding the "\" between strPath and "*.xlsx", compiled and then clicked my button, I receive the following error:

I can confirm the path and file in the error are correct, so it should import from the excel sheets. But it isn't. Just to reiterate, the excels are on a network location. If that affects anything or capturing local objects?

 

[Minty]

So now you have your path correct, can you open the file?

As others have pointed out I'm not sure that your code will do what you think it will.
You aren't looping through the files.

Before trying to actually import them, do a simple test, simply list all the files to the immediate window using your routine.

' Your code goes here '
' Don't do any importing simply do

Debug.Print strFile

' then loop.

This is the only way to see what you are actually achieving.
Once you are getting the correct file names into a loop you can try to do the import.

Also, do you have permissions on that share to open the files?

 

[gemma-the-husky]

Can you REALLY not show us the path that you have blacked out in the error message.
That must be the culprit. Are you 100% sure it's the same? Does the real path perhaps have a space somewhere in it, that's not in your code?

Are you sure the user can see that folder? Can they not use a drive letter instead of the \\ syntax?
Can you browse for the folder, and then use the browsed for folder as the file source?

 

[Sampoline]

So now you have your path correct, can you open the file?

As others have pointed out I'm not sure that your code will do what you think it will.
You aren't looping through the files.

Before trying to actually import them, do a simple test, simply list all the files to the immediate window using your routine.

' Your code goes here '
' Don't do any importing simply do

Debug.Print strFile

' then loop.

This is the only way to see what you are actually achieving.
Once you are getting the correct file names into a loop you can try to do the import.

Also, do you have permissions on that share to open the files?

Can you REALLY not show us the path that you have blacked out in the error message.
That must be the culprit. Are you 100% sure it's the same? Does the real path perhaps have a space somewhere in it, that's not in your code?

Are you sure the user can see that folder? Can they not use a drive letter instead of the \\ syntax?
Can you browse for the folder, and then use the browsed for folder as the file source?

Okay I've solved the issue. So like I said it's on a network location. Reason I blacked it out because it has some sensitive info in the naming. What I did was map the network drive. Plus I was using "acSpreadsheetTypeExcel9" when '*.xlsx' should be "acSpreadsheetTypeExcel12".

Once I made these two changes the import started to work.

 

[Sampoline]

Out of curiosity, is there a way to not use a mapped network drive in the strPath to complete this. Because I've realised that everyone who uses the database would have to map their network drive to the drive letter I assign (in my case "W:\"). Which is not ideal of course. So at the moment it looks like this:

Just wondering is there some way to default a location path that everyone on the network can use.. because at the moment simply copying the address bar where the Excel sheets are located gives me the earlier error that was mentioned in post #9.

Thanks.

 

[gemma-the-husky]

I don't understand why users would have different drive letters for the same path. That ought to be part of your user login profile set up.

However, if you are stuck with it, one option is to have an inifile personal to each user.
Just have it in the same folder as the dbs front end.
In there they can set the path they want to use for the excel imports.

sort of like this

[settings]
excelpath=W:\

Then the process can read the strpath from the inifile, add a \ if it's not already there, and then run the process.
Test whether the path is valid first, and warn users if it's not.
Stuff like that.

There's lots of code around to read/write inifiles.

 

[Minty]

Personally, I would never use a mapped drive path always a UNC path if possible, simply because they aren't reliable unless mapped by a corporate group policy.

You should be able to use \\Server\Servershare\Yourfolder\File1.xlsx

The beauty of doing that is that you store \\Server\Servershare in a system table and look it up in your procedures, if you need to move the location you simply update the top-level path in one place in that table and it all keeps working. No need to recode.

 

[Sampoline]

I don't understand why users would have different drive letters for the same path. That ought to be part of your user login profile set up.

However, if you are stuck with it, one option is to have an inifile personal to each user.
Just have it in the same folder as the dbs front end.
In there they can set the path they want to use for the excel imports.

sort of like this

[settings]
excelpath=W:\

Then the process can read the strpath from the inifile, add a \ if it's not already there, and then run the process.
Test whether the path is valid first, and warn users if it's not.
Stuff like that.

There's lots of code around to read/write inifiles.

Yeah even I'm not too sure why user have different drive letters. I'm kinda new to the place, but from what I understand the user login profile isn't set up this way. I'll have to get that clarified.

 

[Sampoline]

Personally, I would never use a mapped drive path always a UNC path if possible, simply because they aren't reliable unless mapped by a corporate group policy.

You should be able to use \\Server\Servershare\Yourfolder\File1.xlsx

The beauty of doing that is that you store \\Server\Servershare in a system table and look it up in your procedures, if you need to move the location you simply update the top-level path in one place in that table and it all keeps working. No need to recode.

When I use the UNC path I receive the error shown in post #9.. any ideas why?

 

[Minty]

No - When you had that error you weren't formatting the strings correctly.
Have you tried since correcting that error?

If you can paste the UNC path into an explorer window and it works it should work in Access.

 

[Sampoline]

No - When you had that error you weren't formatting the strings correctly.
Have you tried since correcting that error?

If you can paste the UNC path into an explorer window and it works it should work in Access.

Funnily enough, not sure why my error was happening. But when I use the same path in a form and do an application.hyperlink "path" it works.. I'll keep working on it thanks