Access vba help - Network Path

[p4nny]

Hi, Apologies - I'm unsure how to post code into the forum.


There is an issue with InputPath. It does not like the network/server name. It will work fine when it is local (my documents) I've also tried using the drive mapping letter. Any suggestions at all?

Here's my code:

Public Function Import_Multi_Excel_Files()

Dim InputFile As String
Dim InputPath As String

InputPath = "\\server\dfsroot$\Change\3\Impacts\testing"
InputFile = Dir(InputPath & "*.xlsx")





Do While InputFile <> ""

DoCmd.TransferSpreadsheet acImport, , "tests" & Format(Now(), "ddmmyy"), InputPath & InputFile, True, "Action Plan!"
InputFile = Dir
Loop

End Function

 

[stumac]

Missing a backslash on the end?

 

[stumac]

InputPath = [URL="file://server/dfsroot$/Change/3/Impacts/testing/"][COLOR=#000000]\\server\dfsroot$\Change\3\Impacts\testing[/COLOR][/URL][B][COLOR=#FF0000][/COLOR][/B][URL="file://server/dfsroot$/Change/3/Impacts/testing"][B][COLOR=#FF0000]\[/COLOR][/B][/URL]

to enter code on here put [ CODE] in front of it and [ /CODE] at the end (without the spaces)

 

[p4nny]

Hi

I've found the issue but unsure how to resolve it

My input path "\\server\dfsroot$\Change\3\Impacts\testing has a number of subfolders. Rather than name each subfolder explicitly, I would like to search "testing" and its subfolders

Does that make sense? Is it something you could possibly assist with?

Many thanks

 

[stumac]

It is a bit more complex when you need to loop subfolders, there are plenty of links online about doing it. I adapted the solution at the following link when I required it rather than trying to start from scratch:

https://answers.microsoft.com/en-us...bfolders/605972b8-f8f1-4719-9e02-fd79b32ea68e#

 

[Bodders]

You could also look at using the File Scripting Object (FSO)to do the work for you. You will need to set a reference to "Microsoft Scripting Runtime" in your VBA window.

Here is a link to someone using FSO for that purpose : https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba

Bodders