How to Link to an Excel File By Prompting the User for the FileNam

[ttp]

I have an Excel spreadsheet that is updated weekly. Weekly, new records are
added to the file that need to be compared to a data file in Access. Since
the file is changed weekly, I want to prompt the user to provide the file
name that needs to be linked to the Access database so that the queries and
assessments can be performed. But, I don't know how to write code to prompt
for this information. I believe that maybe I can write a module anc macro to
accomplish this. But, my experience with Access is very limited beyond
creating databases and queries. Can a module be written to accomplish this?
Or can anyone recommend a web site that will provide insight on how to
accomplish this? Any assistance would be appreciated. Thanks in advance.

 

[Steve Schapel]

Ttp,

Why not just link the spreadsheet from the Access database, and leave it
there? That way the current Excel data is immediately available within
the database whenever you need it.

 

[ttp]

I need to automate the process. The user wants to be able to press a button
and this button will allow them to select the correct file and use that file
in the queries within the macro.

 

[Steve Schapel]

Ttp,

Apologies. I understood from your original question that there was only
one Excel file, which was being periodically updated. I didn't realise
there were multiple Excel files that needed choosing between on any
given processing occasion.

 

[memanuel]

This would be my suggestion:

------------------------------------------
Private Sub ButtonName_Click()

Dim sFile As String
Dim xlApp As New Excel.Application
'note early binding so you will need to go to Tools > Reference and
check of the Microsoft Excel object library reference.

sFile = xlApp.GetOpenFilename("Excel (*.xls), *.xls", , "Select file", ,
False) 'the command to bring up a popup & select window

If sFile = "False" Then 'in case user clicks cancel
MsgBox "No file selected"
Exit Sub
End If

'the command to import the info into
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Name of
Table to Import to", sFile, True, "Sheet1!A:C"

MsgBox "Import complete"

End Sub
----------------------------------------

Note:
I was unable to get the popup window to appear at the foreground,
nonetheless the code is pretty solid.

Cheers!