Running Excel from Access VB causes error.

Question

I am running VBA code from Access (see subroutine below).  The entire subroutine executes as desired and the resultant Excel file is updated as desired.  However, after I return to my calling module, I get the screen from Microsoft Excel that says "We're sorry, but Microsoft Excel has encountered a problem and must close..."

I am obviously omitting something that I must do to satisfy Excel, but I don't know what. 

Thanks for your time and help in advance.

Option Compare Database

Option Explicit

        Dim objExcel As Excel.Application

        Dim xlWorkbook As Excel.Workbook

        Dim xlSheet As Excel.Worksheet

        Dim wsp As DAO.Workspace

        Dim dbAuditorSystem As DAO.Database

        Dim recContractor As DAO.Recordset

' Get template file name

' Copy template to specified output directory with new name

' Define Excel objects

' .Seek the data from the tblContractor table

' Open the new Excel Object for output

' modify the first worksheet it should be named "Data Control"

' Close everything up and return

  Public Sub GenerateWPFile(plngContractorID)

100     On Error GoTo Error_GenerateWPFile

110     goErr.PushStack "GenerateWPFile"

        Dim oFileFind As New clsFileFind

        Dim strTemplateFile As String

        Dim strOutputFile As String

        Dim strOutputFileName As String

        Dim strOutputDir As String

        Dim strWorkPhone As String

        Dim lngContractorID As Long

        goApp.HideAForm ("C")

        lngContractorID = plngContractorID

        strWorkPhone = ""

     ' Get the template file name

        MsgBox "Press the OK button and then select the WP Template file."

120     strTemplateFile = oFileFind.strFindFile("", "Excel spreadsheets (*.xls)|*.xls", "A:")

     ' Let User decide where to put the WP File

        MsgBox "Press the OK button and then select the directory where you want the new WP File written."

130     strOutputDir = oFileFind.strFindDir()

        If strOutputDir = "-Cancel" Then

            GoTo Exit_GenerateWPFile

        End If

     ' Get name of output file based on Employer Name and copy template to new directory using new name

140     strOutputFile = goCompany.ExportName

145     strOutputFileName = strOutputDir & strOutputFile & ".xls"

150     FileCopy Source:=strTemplateFile, Destination:=strOutputFileName

     ' Get Employer Data from System Contractor Table

        Set wsp = DBEngine.CreateWorkspace("GenerateWPFile", "Admin", "")

        Set dbAuditorSystem = wsp.OpenDatabase(goApp.SystemMDB, SS_SHARED, SS_READWRITE)

        Set recContractor = dbAuditorSystem.OpenRecordset("tblContractor")

160     recContractor.Index = "zlngContractorID"

170     recContractor.Seek "=", lngContractorID

180     If recContractor.NoMatch Then

190         MsgBox "Unable to find " & strOutputFile & " in System Table ... Call Mike Bromley"

200         GoTo Exit_GenerateWPFile

220     End If

     ' Prep the Excel output file

230     Set objExcel = CreateObject("Excel.Application")

235     objExcel.DisplayAlerts = False

240     Set xlWorkbook = objExcel.Workbooks.Open(strOutputFileName, , False)

250     Set xlSheet = xlWorkbook.Worksheets(1)

     ' Update the Excel output file with the values from the Contractor Table

260     With recContractor

270         xlSheet.Cells(5, 3).Value = !strName

280         xlSheet.Cells(12, 2).Value = !strInternational

290         xlSheet.Cells(7, 3).Value = !strAddress

300         xlSheet.Cells(8, 3).Value = !strCity & ", " & !strState

310         xlSheet.Cells(9, 3).Value = !strZip

320         strWorkPhone = "(" & Left(!strPhone, 3) & ") " & Mid(!strPhone, 4, 3) & "-" & Right(!strPhone, 4)

330         xlSheet.Cells(10, 3).Value = strWorkPhone

340         strWorkPhone = "(" & Left(!strFax, 3) & ") " & Mid(!strFax, 4, 3) & "-" & Right(!strFax, 4)

350         xlSheet.Cells(11, 3).Value = strWorkPhone

360         xlSheet.Cells(13, 3).Value = !dtmScopeStartDate

370         xlSheet.Cells(2, 4).Value = !dtmRcvdDate

380         xlSheet.Cells(3, 4).Value = !dtmIntroLetterDate

390     End With

     ' Close up the database, save the Excel file and quit

400     CloseEverything

Exit_GenerateWPFile:

987     goApp.UnhideAForm ("C")

988     goErr.PopStack

989     Exit Sub

Error_GenerateWPFile:

990     Dim strError As String

991     Dim lngError As Long

992     Dim lngErl As Long

993     strError = Err.Description

994     lngError = Err.Number

995     lngErl = Erl

996     Select Case lngError

            Case Else

998             goErr.Handler pstrError:=strError, plngError:=lngError, plngErl:=lngErl

999     End Select

        Resume Exit_GenerateWPFile

  End Sub

      Private Sub CloseEverything()

1000        recContractor.Close

1010        dbAuditorSystem.Close

1020        xlWorkbook.Save

1025        xlWorkbook.Saved = True

1030        xlWorkbook.Close SaveChanges:=True

1050        objExcel.DisplayAlerts = True

1060        objExcel.Quit

1070        wsp.Close

1080        Set recContractor = Nothing

1090        Set dbAuditorSystem = Nothing

1100        Set wsp = Nothing

1110        Set objExcel = Nothing

1120        Set xlWorkbook = Nothing

1130        Set xlSheet = Nothing

      End Sub

Answer 1

Jeanette, progress!!!  Here is what I have done:

Created 3 Public Functions in a Class Module that:

 1) Prep MS Excel... Set Statements for Application, Workbook and Worksheet

2. Update the cell with the data passed to the function
3. Cleanup ... Closes and Set statements etc. in the suggested sequence.

Now, I sporadically get the MS Excel error screen.  As before, the resultant EXCEL Spreadsheet file is updated properly all the time.  If you want I will post the actual code for the 3 Functions.

Again, thanks for your time, and I will try any and all suggestions.  As an aside, I do not have Office 2007, or Access 2007, so I cannot test that software level to see if the results would be the same.

Mike Bromley

Answer 2

Jeanette, thanks again for your help.  I re-ordered the Set statements, as you suggested, but received the same result.  That is, the MS EXCEL file is properly updated, but my Access application gets the EXCEL error screen stating..."MS EXCEL has encountered a problem and must close.  We're sorry for the inconvenience..."

 Question... should I perhaps be using Office Automation from within a Class Module?  I am going to try this and report back my findings.

Answer 3

Change the order of cleaning up the references to the excel objects.

1.    The worksheet

2.    The workbook

3.    The excel application

Set xlSheet = Nothing

Set xlWorkbook = Nothing

Set objExcel = Nothing

---

Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia

Answer 4

Jeanette, thank you so much for your time and help.  Here is what I did:

First, I did as you suggested.  I moved all of the code from the Sub called CloseEverything and simply put it after the label Exit_GenerateWPFile.  Still got the same error after returning to the calling module, however, as before, all results were as desired in the updated Excel file itself.

Next, I moved the 6 Dim statements preceding the Public Sub GenerateWPFile (plngContractorID) to be inside the Public Sub.  Same result again.

Any ideas are greatly appreciated, and I sincerely appreciate your help here.

Mike Bromley

Answer 5

Move this code (below) from the private sub called CloseEverything to the

Exit_GenerateWPFile:  

 event of your sub called GenerateWPFile. Put it before the Exit Sub line.

 event of your sub called GenerateWPFile. Put it before the Exit Sub line.

recContractor.Close

dbAuditorSystem.Close

xlWorkbook.Save

xlWorkbook.Saved = True

xlWorkbook.Close SaveChanges:=True

objExcel.DisplayAlerts = True

objExcel.Quit

wsp.Close

Set recContractor = Nothing

Set dbAuditorSystem = Nothing

Set wsp = Nothing

Set objExcel = Nothing

Set xlWorkbook = Nothing

Set xlSheet = Nothing

---

Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia