Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    export to excel, changing file location (Access 2000)

    I am trying to develope a way for the user to choose where he or she has put an excel file that is linked to an access table. The data from the table is sent to the excel file through a query created from the menu Data>Get External Data>New Database Query in excel. I can attain the location of the file through user input using the code:

    Dim dlg As New CommonDialog
    With dlg
    .FileName = "*.xls"
    .InitDir = "C:"
    .Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
    .Flags = cdlHideReadOnly Or cdlFileMustExist Or cdlPathMustExist
    If .OpenDialog() = True Then
    Me.Master_Table_Field.Value = .FileName
    Else
    MsgBox "No file selected", vbInformation
    End If
    End With
    Set dlg = Nothing
    From there the file location is saved in a table. I can have the file open up with a click of a button using the following snippet of code:
    Dim Excel_File As DAO.Recordset
    Set Excel_File = dbs.OpenRecordset("Setup")

    lngResult = ShellExecute(hWndAccessApp, "Open", "" & Excel_File![Master_Table].Value, 0&, 0&, SW_SHOWMAXIMIZED)

    The problem I am having is after the file opens I get an error message saying [Microsoft][ODBC Microsoft Access Driver] could not find file. I don't know how to automatically change the file location that excel is getting its query data from.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: export to excel, changing file location (Access 2000)

    If I understand you correctly, the problem occurs in Excel, so your question is not about Access but about Excel / Microsoft Query. Or am I missing something?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: export to excel, changing file location (Access 2000)

    I think we need a bit more info here to really help you:
    - Are you really linking to an Excel file in the Access database?
    - Are there multiple copies of an Access database, and if so why?
    - Have you looked at using automation of Access from Excel as an alternative - the TransferSpreadsheet may be exactly what you are looking for.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •