How do I import Excel data using dynamic path created/Input on Access Form

user_12968934 0
Viewed 343

In MS Access, I created a form with Form Name "Form" and created a Text Box named "Path".

When I enter a path in form with "c:Desktop.....xlsx". I should be able to import data from Excel to Access.

I tried using the Macro version in Access using the below code, but I encounter an error.

Function Import()
    On Error GoTo Import_Err
    Dim Str As String
        Str = Forms!Form!Path.Caption
            DoCmd.TransferSpreadsheet acImport, 10, "EM", Str, True, ""
    Import_Exit:
        Exit Function
    Import_Err:
        MsgBox Error$
        Resume Import_Exit
End Function

Error: "Object doesn't support this property or method".

As I plan to give the file to others to run, they will have a different path, so we need to prefix it in their system. So instead if we have form path, then users can input the path in form and run it.

ms-access vba excel
2 Answers

The Access Textbox control doesn't have a Caption property. But it does have two other properties:

AFAICT, Text returns the text currently in the textbox, while Value returns the last persisted value. If the user has modified the text in the textbox but the focus is still in the texbox, Text will return the user-entered new value, but Value will return the value before the user started editing.

Note that you can only get and set Text when the control has the focus.

user_1117940

Use this:

Str = Forms!Form.Path

I would (in your shoes) use the File Open Dialog instead letting users type the file with path:

        Set FD = Application.FileDialog(msoFileDialogOpen)
        Dim FileChosen As Integer
        FileChosen = FD.show
        FD.Title = "Choose workbook"
        FD.InitialView = msoFileDialogViewList    
        FD.Filters.Clear
        FD.Filters.add "Excel workbooks", "*.xlsx"
        FD.Filters.add "All files", "*.*"
        FD.FilterIndex = 1
        FD.ButtonName = "Choose this file"
        If FileChosen <> -1 Then
        'didn't choose anything (clicked on CANCEL)
            MsgBox "No file opened", vbCritical
            GoTo Exit_Function
        Else
            fileNamePath = FD.SelectedItems(1)
        End If
        Set FD = Nothing

and after that

DoCmd.TransferSpreadsheet acImport, 10, "EM", fileNamePath , True
user_75265640
Related