Help with Prompt user to input file name and location

[lookforsmt]

HI!

I have below code to prompt user to first input location and second prompt to input file name (excel filename "Text" to access table "tbl_Text"

I input the location and file name, but it gives me error

The action or method requires a File Name argument

Private Sub Import_File_Click()

On Error GoTo Err_Import_File_Click

Dim strFile_Path As String
Dim strTable As String

'Prompt user for file path
strFile_Path = InputBox("Please enter file path")
'Prompt user for name of table to create for imported data
strTable = InputBox("Please enter name of new table")

'Import file, using inputted file path and table name

DoCmd.TransferSpreadsheet acImport, , strFile_Path & strTable

Exit_Import_File_Click:
Exit Sub

Err_Import_File_Click:
If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"
Else
MsgBox Err.Description
End If
Resume Exit_Import_File_Click

End Sub

Can anyone advice me what i am doing wrong.

I am inputting location as E:\Access4.0\Reports and
input File name as Text.xlsx

 

[theDBguy]

Hi. Not sure but try either entering E:\Access4.0\Reports\ for the location or change the code to this:

DoCmd.TransferSpreadsheet acImport, , strFile_Path & "\" & strTable

 

[lookforsmt]

thanks for replying, i tried the suggestion gives me the same error

 

[theDBguy]

How about?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12xml, "TableName", strFile_Path & "\" & strTable

 

[lookforsmt]

Now i getting "Type mismatch"

 

[Gasman]

As mentioned you need the trailing "" for your path.

Why not use the Filedialog ?

https://rtmccormick.com/2013/08/22/select-a-file-with-file-dialog-in-ms-access-with-vba/

Plus I don't think the syntax is even correct?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

 

[theDBguy]

Hi. Check out the syntax for the TransferSpreadheet method in the Help files and try different formats.

 

[lookforsmt]

Thanks Gasman, i will try the "", i dont want to use file dialog method as the user needs to scroll through many files which is highly likely to select the wrong file.

TheDBGuy, i will try the different format if that works.

 

[theDBguy]

Here's a link to the TransferSpreadsheet syntax where you can also find a list of the different formats.

 

[isladogs]

Does the table already exist?

Try hard coding the paths as a test:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12xml, "YourTableName", "E:\Access4.0\Reports\Text.xlsx"

If that fails try altering the path to E:\Access4\Reports in case the 4.0 is an issue

 

[Gasman]

Thanks Gasman, i will try the "", i dont want to use file dialog method as the user needs to scroll through many files which is highly likely to select the wrong file.

TheDBGuy, i will try the different format if that works.

No don't try that, the site removed the \ from my post.

 

[lookforsmt]

Thanks isladDogs, I tried as suggested but still not able to get this through
I am getting a different error now

The Microsoft database engine cannot open or write to this file. E\Access4.0\Reports\'. It is already opened exclusively by another user or you need permission to view and write its data

 

[isladogs]

OK so check both of the following:
Is the Excel file already open? If so, close it.
Do you have permissions for that folder?

If still not solved, try linking the Excel file to your database & then use an append query to import the data to your table

 

[Gasman]

Thanks isladDogs, I tried as suggested but still not able to get this through
I am getting a different error now

Did you type that?, as there is no : and an errant ' ?

 

[isladogs]

Did you type that?, as there is no : and an errant ' ?

Well spotted. It should be what I typed in post #10

 

[theDBguy]

Hi. Here's your code with some recommended changes. Please try it out and let us know what happens.

Private Sub Import_File_Click()  

On Error GoTo Err_Import_File_Click  

Dim strFile_Path As String 
Dim strTable As String  

'Prompt user for file path 
strFile_Path = InputBox("Please enter file path and name (e.g. C:\Folder\File.xlsx)") 

'Prompt user for name of table to create for imported data 
strTable = InputBox("Please enter name of new table")  

'Import file, using inputted file path and table name 
If Dir(strFile_Path)<>"" Then
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12xml, strTable, strFile_Path
Else
     MsgBox "The file path you entered is not valid.", vbInformation, "Error"
End If

Exit_Import_File_Click: 
    Exit Sub  

Err_Import_File_Click: 
     If Err.Number = 3011 Then 
        MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path" 
     Else 
        MsgBox Err.Description 
    End If 
    Resume Exit_Import_File_Click  

End Sub

 

[Gasman]

You can set a default path for the FileDialog?

I'm thinking, if the user cannot select a correct file from the dialog, typing in the correct name and path is going to be a chore?

 

[lookforsmt]

thanks for the code, apologies for the delay. As mentioned earlier i wanted user to type the file name rather selecting from the FileDialog.

I have tried to modify my code and it allows me to import excel into access. The only problem is it disregards the file location in strPath and copies the file placed in Documents. (C:\Users\lookforsmt\Documents)

Private Sub Import_File_Click()

    On Error GoTo Err_Import_File_Click
    
    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim binHasFieldNames As Boolean
 
'Change this next line to True if the first row in Excel worksheet
'has field names

    binHasFieldNames = True

'    strPath = InputBox("Please enter file path")
    strPath = "E:\Access4.0\Reports\New folder\"
    strTable = InputBox("Please enter name of new table")

'    strFile = Dir(strPath & "test.xlsx")

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_test", strTable, True
    

Exit_Import_File_Click:
Exit Sub

Err_Import_File_Click:

If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"
Else
MsgBox Err.Description
End If
Resume Exit_Import_File_Click
End Sub

How can i change the location from Documents to strPath location.

 

[Cronk]

@lookforsmt, in #8 you wrote

i dont want to use file dialog method as the user needs to scroll through many files which is highly likely to select the wrong file.

I think you are successfully proving that it is harder to type in the right file and path.
In your statement

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_test", strTable, True

you are trying to import the contents of strTable. which you have prompted the user to enter the name of a new table.
Insert a few debug.prints in your code to see just what you are doing, eg

debug.print strTable, strFile

 

[lookforsmt]

Dear Cronk, where do i place the code in the module to debug.

in the IW should i type ?strTable or ? strFile