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

Sampoline

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
I have the following code but it isn't importing the excel sheets into the specified table:

Code:
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:

Code:
Private Sub btnImport_Click()

    Import

End Sub

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

Thanks.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:08
Joined
Jan 20, 2009
Messages
12,849
This path doesn't look right:
strPath = "C://Documents"
 

Sampoline

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:08
Joined
Jan 20, 2009
Messages
12,849
There is no slash between the strPath and "*.xlsx"
 

Sampoline

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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

AWF VIP
Local time
Today, 12:08
Joined
Jul 26, 2013
Messages
10,354
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.
Rich (BB code):
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.
 

Cronk

Registered User.
Local time
Today, 23:08
Joined
Jul 4, 2013
Messages
2,770
In addition to all the other aspects covered above, note that
Code:
strFile = Dir()
will return something like
"FileName.xlxs"

You then have to add back the path for the Transferspreadsheet. Something like
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "C:\documents\" & strTable, strPathFile, blnHasFieldNames
 

Sampoline

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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.
Rich (BB code):
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
Code:
strFile = Dir()
will return something like
"FileName.xlxs"

You then have to add back the path for the Transferspreadsheet. Something like
Code:
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:

1611101080749.png


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

AWF VIP
Local time
Today, 12:08
Joined
Jul 26, 2013
Messages
10,354
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.
Rich (BB code):
' 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?
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:08
Joined
Sep 12, 2006
Messages
15,613
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

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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.
Rich (BB code):
' 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

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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:

1611201074435.png


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

Super Moderator
Staff member
Local time
Today, 12:08
Joined
Sep 12, 2006
Messages
15,613
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

AWF VIP
Local time
Today, 12:08
Joined
Jul 26, 2013
Messages
10,354
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

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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

AWF VIP
Local time
Today, 12:08
Joined
Jul 26, 2013
Messages
10,354
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

Member
Local time
Today, 23:08
Joined
Oct 19, 2020
Messages
161
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
 

Users who are viewing this thread

Top Bottom