Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Connecticut, USA
    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
    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
    Thanked 28 Times in 28 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
    Evergreen, CO, USA
    Thanked 58 Times in 58 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.

Posting Permissions

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