Access export to excel BUG

[MrDeej]

Hello!

I use this code to make a excel worksheet out of a qry

    MsgBox export_excel, vbInformation, "Finished"
    Application.FollowHyperlink CurrentProject.Path & "\uttak\Månetlig uttak - " & Me.tbx_dato_ta_ut_varer_innen & ".xls"
 

Public Function export_excel() As String
 
Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
 
   Dim sTemplate As String
   Dim sTempFile As String
   Dim sOutput As String
 
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim sSQL As String
   Dim lRecords As Long
   Dim iRow As Integer
   Dim iCol As Integer
   Dim iFld As Integer
 
   Const cTabTwo As Byte = 2
   Const cStartRow As Byte = 4
   Const cStartColumn As Byte = 3
 
 
   Application.SetOption "Error Trapping", 0
 
   sTemplate = CurrentProject.Path & "\templ\utløpstemplate.xls"
   sOutput = CurrentProject.Path & "\uttak\Månetlig uttak - " & Me.tbx_dato_ta_ut_varer_innen & ".xls"
   If Dir(sOutput) <> "" Then Kill sOutput
 
   FileCopy sTemplate, sOutput
 
 
   Set appExcel = Excel.Application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   Set wks = appExcel.Worksheets(cTabTwo)
 
   DoCmd.OpenQuery "IBD qry månetlig uttak excel", acViewPreview, acReadOnly
   sSQL = "SELECT * From [IBD qry månetlig uttak excel]"
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
   If Not rst.BOF Then rst.MoveFirst
 
   iCol = cStartColumn
   iRow = cStartRow
 
 
   Do Until rst.EOF
      iFld = 0
      lRecords = lRecords + 1
 
      For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
         wks.Cells(iRow, iCol) = rst.Fields(iFld)
 
         If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
            wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
         End If
 
         wks.Cells(iRow, iCol).WrapText = False
         iFld = iFld + 1
      Next
 
      wks.Rows(iRow).EntireRow.AutoFit
      iRow = iRow + 1
      rst.MoveNext
   Loop
 
   export_excel = "Totalt " & lRecords & " rader ble overført."
   DoCmd.Close acQuery, "IBD qry månetlig uttak excel"
 
exit_Here:
   On Error Resume Next
   Set wks = Nothing
   Set wbk = Nothing
   Set appExcel = Nothing
   Set rst = Nothing
   Set dbs = Nothing
   DoCmd.Hourglass False
 
End Function

This code works magnificent if i Run it once. The second time i rune it excel hangs like attached

The solution for this is to go inn in Task Manager and kill the excel.exe application before the second time i run the code. Then i get a error code inn access: "The Remote Server Machine doen not exist or is unavailiable". I press end and then the third time i run the code it works just as good as first time.

Any teories on how to make this code work perfect?

Btw: The code is not entirely home designed. I have lent some parts from other sources

[FishVal]

Try to instantiate Excel application with CreateObject.

Set appExcel = CreateObject("Excel.Application")
 

instead of

Set appExcel = Excel.Application
 

[MikeTheBike]

Hi

I am not sure about
Application.FollowHyperlink (never used it in Access, but it doesn't seem to work for me!)

but in stead try inserting this

appExcel.Visible = True

before 'exit_Here:'

and see if this helps


MTB

[MikeTheBike]

Hi FishVal

Set appExcel = CreateObject("Excel.Application")

Yes, this is how have always instantiated an instance of Excel, but the posted method works if a reference is set to Excel. However,with either method, the code does not Quit the excel app, therefore it is still active/open, but not visible, which could be causing problems the second time around (at least I think it might!).

??

MTB

[FishVal]

Hi FishVal

Set appExcel = CreateObject("Excel.Application")

Yes, this is how have always instantiated an instance of Excel, but the posted method works if a reference is set to Excel. However,with either method, the code does not Quit the excel app, therefore it is still active/open, but not visible, which could be causing problems the second time around (at least I think it might!).

??

MTB

Hi, MTB.

Yes your are right. Particularly this may cause problems this case.

Actually invoking Quit method doesn't cause immediate process termination.

• when Excel.Application was instantiated but no workbook was opened

  Set appExcel = Nothing

  kills it to death
• when Excel.Application was instantiated and workbook was opened

  appExcel.Quit
  Set appExcel = Nothing 

  sequence needed to let the process RIP

[Jim Doherty]

off topic slightly... the other guys have answered merely an observation...looking at the screenshot version of excel there you would benefit from sending the RS out using the .CopyFromRecordset method its fast and you could format the spreadsheet once the RS is applied to the sheet?

Jim

[MrDeej]

Try to instantiate Excel application with CreateObject.

Set appExcel = CreateObject("Excel.Application")
 

instead of

Set appExcel = Excel.Application
 

This did the trick. Thank you Fishval! :)

[FishVal]

This did the trick. Thank you Fishval! :)

Not a problem.

But be sure to kill excel process when no more needed.
See posts #4, #5.
And take a look at Jim's "invisible" post #6. Using WorkSheet.Range.CopyFromRecordset method will increase perfomance enormously.

Good luck.