Create Excel spreadsheet in Access 97

access United States
  • 19 years ago

    I am running into errors when I attempt to use the docmd.transferspreadsheet in Access 97 to create a simple (a simple transfer of the results of a query) spreadsheet.

    Excel does open, and the spreadsheet is visble, but one of two errors occur.

    The first:

    If I click anywhere in the spreadsheet itself (not the menus, etc), I get an IPF error. This occurs on both Win 95 and Win2K machines.

    The second:

    No matter where I create the spreadsheet, either on our network or on the user's hardrive, when the the db tries to execute the docmd.transferspread and attempts to create the spreadsheet (which may already exists from a previous run), the error say that the the file is locked and is read only. If I try to delete the existing xls file before creating it, I get "permission denied", even when the xls file is on the local C drive. This occurs on both Win 95 and Win2K machines.

    If someone has a complete sample code (other than the folllowing) that  just transfers the results of a query to a spreasdheet that is opened and viewable ans savable inside of Access 97, it would be GREATLY appreciated. I have researched these issues for many hours and am at a standstill.


    Private Sub cmdOpenXL_Click()

    Dim strpath As String
    Dim fso As New Scripting.FileSystemObject
       ExportFilter strCriteria
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tmp", _
           "C:\tmp1.xls", -1
       DoCmd.DeleteObject acQuery, "tmp"
    End Sub

    Sub GetExcel()
       Dim MyXL As Object
       Dim ExcelWasNotRunning As Boolean  

       On Error Resume Next    ' Defer error trapping.
       Set MyXL = GetObject(, "Excel.Application")
       If err.Number <> 0 Then ExcelWasNotRunning = True

       Set MyXL = GetObject("C:\tmp1.xls")

       MyXL.Application.Visible = True
       MyXL.Parent.Windows(1).Visible = True

       If ExcelWasNotRunning = True Then
           MyXL.Application.Quit acSaveYes
       End If

       Set MyXL = Nothing  ' Release reference to the application and spreadsheet.
    End Sub

    Sub DetectExcel()
       Const WM_USER = 1024
       Dim hwnd As Long

       hwnd = FindWindow("XLMAIN", 0)
       If hwnd = 0 Then    ' 0 means Excel not running.
           Exit Sub
           SendMessage hwnd, WM_USER + 18, 0, 0

    End If
    End Sub

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“C++ : Where friends have access to your private members.” - Gavin Russell Baker