Import Data From Different User's Documents Folder

[EnginerdUNH]

Hi,

I am working on a database where I am trying to add the functionality for people to be able to "batch add" data to the database from a .xlsx file that will be stored locally in a specific folder within their My Documents folder. Currently, I am using the following code and I am getting an error that no .xlsx file can be found.

Code: Select all

DoCmd.OpenQuery "qryDeleteVIRListImport"
            
            Dim strFolder As String
            Dim strFile As String
            strFolder = "C:\Users\" & User & "\Documents\VIR Database Imports"
            strFile = Dir(strFolder & "*xlsx")
            If strFile = "" Then
                MsgBox "There is no .xlsx file in the folder!", vbExclamation
                Exit Sub
            End If
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
            "VIRListImport", strFolder & strFile, True

Any help that you can give me to figure out why this code isn't working would be greatly appreciated.

[HansV]

There should be a backslash at the end of the path:

Code: Select all

            strFolder = "C:\Users\" & User & "\Documents\VIR Database Imports\"

[EnginerdUNH]

ahhh yes, I forget about that sometimes