Invoke Excel from Access VBA fails

[Fred erickson]

The objective is to create an Excel file on the fly from
within an Access (2002) application. There is a need to
create multiple files from a single Access session.
The code works perfectly on the first pass but fails on
subsequent passes. It is necessary to close the database
and quit and restart Access for the code to run again.
After two or three re-runs it is necessary to restart the
computer to restore function. The error message is 'Cells
method of object _Global failed'. Code follows:

option explicit

public appExcel as Excel.Application
public wbExcel as Excel.workbook
public wsExcel as Excel.worksheet
public introw as integer
public intcol as integer

Public Sub Write_Spreadsheet
Set appExcel = New Excel.Application
Set wbExcel = appExcel.Workbooks.Add
appExcel.Visible = True
wbExcel.Worksheets(1).Activate
Set wsExcel = wbExcel.ActiveSheet
With wsExcel
intRow = 1
intCol = 1
Call WS_Titles("Title1", "Title2")
Call WS_Headers_2_Col("Header1", "Header2")
End With
For Each wbExcel In appExcel.Workbooks
wbExcel.Close
Next wbExcel
appExcel.Quit
Set appExcel = Nothing
Exit Sub
End sub

Public Sub WS_Titles(Line1 As String, Line2 As String)
With wsExcel
With .Cells(intRow, intCol)
...
End With
End With
Exit Sub
End Sub

Public Sub WS_Headers_2_Col(ColHead1 As String, ColHead2
As String)
With wsExcel
With .Range(Cells(intRow, intCol), Cells(intRow + 1,
intCol)) [code fails here on 2nd pass]
...
End With
End With
Exit sub
End Sub

 

[Howard Kaikow]

The is not complete enough to vet.
I do note that the With ... End With in the following has no effect.

With wsExcel
intRow = 1
intCol = 1
Call WS_Titles("Title1", "Title2")
Call WS_Headers_2_Col("Header1", "Header2")
End With

--
http://www.standards.com/; See Howard Kaikow's web site.

The objective is to create an Excel file on the fly from
within an Access (2002) application. There is a need to
create multiple files from a single Access session.
The code works perfectly on the first pass but fails on
subsequent passes. It is necessary to close the database
and quit and restart Access for the code to run again.
After two or three re-runs it is necessary to restart the
computer to restore function. The error message is 'Cells
method of object _Global failed'. Code follows:

option explicit

public appExcel as Excel.Application
public wbExcel as Excel.workbook
public wsExcel as Excel.worksheet
public introw as integer
public intcol as integer

Public Sub Write_Spreadsheet
Set appExcel = New Excel.Application
Set wbExcel = appExcel.Workbooks.Add
appExcel.Visible = True
wbExcel.Worksheets(1).Activate
Set wsExcel = wbExcel.ActiveSheet
With wsExcel
intRow = 1
intCol = 1
Call WS_Titles("Title1", "Title2")
Call WS_Headers_2_Col("Header1", "Header2")
End With
For Each wbExcel In appExcel.Workbooks
wbExcel.Close
Next wbExcel
appExcel.Quit
Set appExcel = Nothing
Exit Sub
End sub

Public Sub WS_Titles(Line1 As String, Line2 As String)
With wsExcel
With .Cells(intRow, intCol)
...
End With
End With
Exit Sub
End Sub

Public Sub WS_Headers_2_Col(ColHead1 As String, ColHead2
As String)
With wsExcel
With .Range(Cells(intRow, intCol), Cells(intRow + 1,
intCol)) [code fails here on 2nd pass]
...
End With
End With
Exit sub
End Sub