Importing files - prompt for file name (1 Viewer)

[kurt]

I am trying to import a file on a daily basis where the file name changes every day. I know how to create a macro to import a file using import specifications, but it must have the same file name. Is there a way for Access to prompt me for the file name?

 

[alexl]

It's probably easier to do this in code than in a macro. Use an inputbox to prompt for the file name; use that variable in the docmd.transfertext line of code that will follow.

 

[Simon C]

Attach the following event procedure to the On Click event of a button called 'Import_File'.

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.TransferText acImportDelim, , strTable, strFile_Path

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

 

[DarkFriend]

You can do the same thing on a form, rather than using Input Boxes. I can make the data entry easier because you can use combo boxes, lists, etc.. You just declare the variables to hold the values, and then replace those variables in the TransferText statement.

 

[SiGill]

I am trying to do this but I keep getting an error saying it can't find macro.
am I doing something wrong?