TransferSpreadsheet Prompt User for File

[karl009]

Hi,
I have been looking into transferring data from Excel to Access, I have now been able to successfully do this using a Macro, however the filename / location is static in the Macro.
How can I prompt the user to select the file, like in the Get External Data wizard?
Here is the Macro converted into VBA;

 Function mac_Import_Excel()
On Error GoTo mac_Import_Excel_Err

    DoCmd.TransferSpreadsheet acImport, 10, "tbl_Price_Requests", "C:\Users\KARL\Desktop\Copy of PPR Input Form V3.xls", True, "ENQ"
    DoCmd.TransferSpreadsheet acImport, 10, "tbl_Price_Requests_Lines", "C:\Users\KARL\Desktop\Copy of PPR Input Form V3.xls", True, "LINES"


mac_Import_Excel_Exit:
    Exit Function

mac_Import_Excel_Err:
    MsgBox Error$
    Resume mac_Import_Excel_Exit

End Function

Many Thanks
Karl

 

[rainman89]

How about trying

filename= application.GetOpenFileName ()

or this for vba

http://www.access-programmers.co.uk/forums/showthread.php?p=917371#post917371

 

[karl009]

Hi,

I looked at the other post while it seems that this will help me with what am trying to do, I don’t quite understand it all, I looked at the website that explains the rest but am at a loss with what to do with all the code.

Also filename= application.GetOpenFileName () seems a simple way of doing things are there any examples.

Are there any example databases that use these two methods, this would help out greatly.

Many Thanks
Karl

 

[ghudson]

Check out my old Browse [Find a directory or file] sample to see how easy it is to do and use.