TransferSpreadsheet acLink in VBA question

[PB7]

All, I have code using TransferSpreadsheet within VBA code.

But, I would like the folder location to vary, depending on the folder's name keyed in by the user into a form. One time, he may need to link to files in Folder1, the next day he would link to Folder2, and so on.

So, trying something here, Access VBA does not like this code:

Do.Cmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "One", "E:\" & "Forms![SelectForm]![Foldername]" & "\" & "One.xls", True

Access error says this is not a valid path, so obviously, my problem is the Forms![SelectForm]!Foldername] reference.

Would anyone know how to do this? To insert a variable folder name into this VBA code?

Thanks in advance to anyone who can help, thanks.

 

[Phildaburn]

Since you can't really see exactly what you're getting and Access isn't happy, try this untested code before your "Do.Cmd" line:

Dim PathFile as string
PathFile = "E:\" & Forms![SelectForm]![Foldername] & "\One.xls"
Debug.Print Path

If the immediate window shows you the entire path and file name, substitute what you've done in your "Do.Cmd" line with "PathFile" and see if it works.

 

[PB7]

Philaburn, thanks! Trying this solution now, and will let you know if successful.

 

[PB7]

Phildaburn, your solution works great! VBA code nows reads the folder per the form. Thanks very much.

 

[Phildaburn]

I'm delighted I could be of assistance.