• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Prompt user for file name and location through browser such as "File Open/Save" and import file as ""

I have a multi user Access 2000 application.  The buyers send out an excel spreadsheet "Open Purchase Order Report" from access.  The suppliers will update "Vendor Promise Date" and "Vendor Notes".  The supplier will return the excel spreadsheet via e-mail.

The process currently - 1 person opens the e-mail from the supplier and saves the excel spreadsheet as
"Linda.xls" to her desktop.  The person then runs a macro within the database which is directed to the desktop and then to "Linda.xls".  The macro will import the file and run updates from the spreadsheet -  this allows the next
report going out to the suppliers to include the notes and dates they have provided.

I would like to allow the buyers to open the e-mail and save the attachment as whatever name they desire.  Then
when they run the macro I need the database to:

Prompt for file name and location - through a browser - "Files Open/Save" - this is a must.
Then transfer spreadsheet into access as "Linda" - this should be set in code and not an option.
0
alanlott
Asked:
alanlott
  • 4
  • 3
1 Solution
 
thenelsonCommented:
Convert the macro (The person then runs a macro within the database which is directed to the desktop and then to "Linda.xls".  The macro will import the file and run updates from the spreadsheet) to code (Tools->Macro->Convert Macro to Visual Basic).  Then post the code here.  I can then suggest changes to the code to prompt for file name and location.

Nelson
0
 
alanlottAuthor Commented:
 
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "Linda"
    DoCmd.TransferSpreadsheet acImport, 8, "Linda", "", True, ""
    Call DeletetblLinda
    Call UpdatePurchaseOrderDates
    DoCmd.SetWarnings True
0
 
thenelsonCommented:
Insert a new module in your VBA.  Name it anything you wish -- I suggest naming it "modCallOpenSaveDialog".  Go to http://www.mvps.org/access/api/api0001.htm, copy everything from "'***************** Code Start **************" to "'************** Code End *****************" and paste it into the new module.

Substitute your code above with:

    Dim strFilter As String
    Dim lngFlags As Long
    Dim strFilePathName As String

    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "Linda"
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    strFilePathName = ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
        DialogTitle:="Select the file you want to convert:")
    DoCmd.TransferSpreadsheet acImport, 8, "Linda", strFilePathName, True, ""
    Call DeletetblLinda
    Call UpdatePurchaseOrderDates
    DoCmd.SetWarnings True

If I am truely understanding what you need then I think this should do it.  Let me know if there is anything you don't understand or anything you want to change that you can't figure out.  Since you saved the code from mvps.org in a module, you can use this from anywhere in your project by calling "ahtCommonFileOpenSave".

Hope that does it for you.

Nelson
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
alanlottAuthor Commented:
Nelson,

I copied the module in and your code - I get a syntax error on the line listed below.  Thank You Very Much!!!


'------------------------------------------------------------
' Linda
'
'------------------------------------------------------------
Function Linda()

Dim strFilter As String
    Dim lngFlags As Long
    Dim strFilePathName As String

    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "Linda"
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")

    ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")                                SYNTAX ERROR ON THIS LINE

    strFilePathName = ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
        DialogTitle:="Select the file you want to convert:")
    DoCmd.TransferSpreadsheet acImport, 8, "Linda", strFilePathName, True, ""
    Call DeletetblLinda
    Call UpdatePurchaseOrderDates
    DoCmd.SetWarnings True
   
End Function
0
 
thenelsonCommented:
Just delete that line.  I think I let me fingers do too much walking!  BTW: In the new module the function "TestIt" will show you how to call ahtCommonFileOpenSave with several different options.  You can test "TestIt" by placing your curser anywhere in the procedure and then pressing F5.
0
 
alanlottAuthor Commented:
Thank you very much - works excellent - you rule!
0
 
thenelsonCommented:
Glad I could help.  Could you tell my wife I rule?  Never mind -- wouldn't help anyway.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now