Transfer Spreadsheet ...

[AartiJ]

Hi there,

I have created a macro to import data from MS Excel file into a table in MS Access database. Each time I have to import data from a different file. Therefore, I cannot predefine the path of the spreadsheet for data import. Is there a way I can prompt the user to punch in the location of the file for import?

Thanks,
Aarti Jobanputra

 

[Tapew0rm]

I had the same problem today because the file name always had a diffrent date. This is what ended up working (only i was doing it for a text file.) Hope it helps

take your macro and convert it to a vba module.

Then do somthing along these lines with the code.

Private Sub Command27_Enter()

On Error GoTo Macro7_Err

Dim FileName As String

FileName = InputBox("please enter file name extension")

DoCmd.DeleteObject acTable, "Table Name" ' you may not need to delete the table I just did it this way for mine

DoCmd.TransferText acImportDelim, "File Name Specification", "Table Name", "\\server\share\Downloads\ FileName & ".txt", False, ""



Macro7_Exit:
Exit Sub

Macro7_Err:
MsgBox Error$
Resume Macro7_Exit
End Sub