Create Excel spreadsheet in Access 97

• • Reply

  19 years ago

  by jgoody

  jgoody

  , United States

  Joined 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.
  

  
  

  
  HELP!
  

  
  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"
     GetExcel
  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
     err.Clear  
     DetectExcel
  

  
     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
     Else
         SendMessage hwnd, WM_USER + 18, 0, 0
  

  
  End If
  End Sub
  

  Report abuse