Using a constant and a variable in a Docmd.TransferSpreadsheet acImport statement RRS feed

  • Question

  • I am a self-taught newbie to VBA and am not sure whether I am going about what I want to accomplish in the right manner.  I have a procedure within my database that needs to import several excel spreadsheets into several tables. These spreadsheets will all have the same path, but different file names. I am wondering if, instead of writing x amount of docmd.transferspreadsheet statements for each source file and destination table, I could declare a constant for the path and use a variable for the source files and destination tables.  The code below is creating a Error code 3011:

    Public Const MYPATH = "c:\Users\user\Documents\"

    Dim source As Variant

    source = InputBox("Please provide name of source file.", "Source File")

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Broker_Data", MYPATH & "'" & source & "'", True

    here is the 3011 error:

    The database engine could not find the object 'c:\Users\user\Documents\'Broker Data.xlsx".  Make sure that the object exists and that you spell its name and the path name correctly. 

    I have confirmed that the object exists and that the path and object name are spelled correctly.  Can anyone help with this error or provide some feedback on a more efficient way to do this?

    Thanks so much!!


    Wednesday, May 10, 2017 11:51 PM

All replies

  • Not spelled correctly.

    The easiest way you can find out is by modifying your code slightly:

    dim strFile as string
    strFile = MYPATH & "'" & source & "'"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Broker_Data", strFile, True

    This will fail the same way, but next you set a breakpoint on the last line and inspect strFile. In the Immediate window type:
    c:\Users\user\Documents\'Broker Data.xlsx'

    Maybe you see it now?

    If not, open the Run box (Window key + R) and paste that value, and hit enter. You'll get an error.
    Then you realize the single quotes do not belong, and you fix your code, and life is good :-)

    -Tom. Microsoft Access MVP

    Thursday, May 11, 2017 12:47 AM
  • Also, to be more user-friendly than asking user to type a filename, you can offer a file-open dialog so user can pick the file using a familiar interface. You can set it up to use MYPATH as the starting folder.

    Another thing that's sometimes appropriate is that all files in a folder need to be processed. In that case you can use the Dir function to get a list of all files, one at a time, so you can process them.

    -Tom. Microsoft Access MVP

    Thursday, May 11, 2017 12:50 AM
  • Hi rstephan56,

    As Tom van Stiphout (MVP) already suggested you that your path is not correct.

    it contains single quotes around file name.

    you need to remove it.

    you can change it like below.

    Sub demo()
    Dim MYPATH As String
     MYPATH = "C:\Users\v-padee\Desktop\"
    Dim source As Variant
    source = InputBox("Please provide name of source file.", "Source File")
    Debug.Print (MYPATH & source)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Broker_Data", MYPATH & source, True
    End Sub

    below is the code example to get file path using open file dialog as suggested by Tom van Stiphout (MVP).

    Sub demo2()
     Dim f As Object
        Dim strFile As String
        Dim strFolder As String
        Dim varItem As Variant
        Set f = Application.FileDialog(3)
        f.AllowMultiSelect = True
        If f.Show Then
            For Each varItem In f.SelectedItems
                strFile = Dir(varItem)
                strFolder = Left(varItem, Len(varItem) - Len(strFile))
                MsgBox "Folder: " & strFolder & vbCrLf & _
                    "File: " & strFile
        End If
        Set f = Nothing
    End Sub

    you can use this file path with your code above.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 12, 2017 2:24 AM