I've got what should be an easy automation problem, but the solution
simply isn't coming to me.
I've got several public variables set up for automation as follows:
Public gappExcel As Excel.Applicati on 'ADO Object for Excel
Automation
Public gstrExcelDir As String 'Source or Destination Directory
Public gstrExcelFile As String 'File Name
Public gwkbExcelBook As Excel.workbook 'Workbook object
Public grstExcelBook As ADODB.Recordset
I initiate automation as follows:
Sub Start_Automatio n()
' This routine sets up objects and variables for excel automation
Set gappExcel = New Excel.Applicati on
If InDevelopment Then gappExcel.Visib le = True
Set grstExcelBook = New ADODB.Recordset
End Sub
I run through the routine which opens, reformats, imports the excel
data and closes excel:
Sub importCoreStyle s
...........
gwkbExcelBook.C lose savechanges:=Fa lse
Call Close_Recordset (grstExcelBook)
gappExcel.Quit
Call Kill_Automation
MsgBox "Core Styles have been imported."
Exit Sub
End sub
The routine Kill_Automation works as follows:
Sub Kill_Automation ()
' Used to clear automation objects
Set gappExcel = Nothing
Set gwkbExcelBook = Nothing
Set grstExcelBook = Nothing
End Sub
Everything works fine until I run a second routine which basically
does the same thing. Only this time, I run into a "Range method of
Object '_Global' failed" run-time error. I doesn't seem to want to
recognize either the Excel application or the workbook object. If I
manually close excel, the second routine runs fine. Is there something
that manually closing the application does that I'm missing by using
..quit?
It seems to me that I could solve this problem by simply initiating
new objects but why should I when I have a set of perfectly good
objects ready to use?
It also seems that the automation is not releasing all resources
because after testing the routines 6 or more times I start to
encounter windows errors.
Any ideas would be helpful.
Thanks.
simply isn't coming to me.
I've got several public variables set up for automation as follows:
Public gappExcel As Excel.Applicati on 'ADO Object for Excel
Automation
Public gstrExcelDir As String 'Source or Destination Directory
Public gstrExcelFile As String 'File Name
Public gwkbExcelBook As Excel.workbook 'Workbook object
Public grstExcelBook As ADODB.Recordset
I initiate automation as follows:
Sub Start_Automatio n()
' This routine sets up objects and variables for excel automation
Set gappExcel = New Excel.Applicati on
If InDevelopment Then gappExcel.Visib le = True
Set grstExcelBook = New ADODB.Recordset
End Sub
I run through the routine which opens, reformats, imports the excel
data and closes excel:
Sub importCoreStyle s
...........
gwkbExcelBook.C lose savechanges:=Fa lse
Call Close_Recordset (grstExcelBook)
gappExcel.Quit
Call Kill_Automation
MsgBox "Core Styles have been imported."
Exit Sub
End sub
The routine Kill_Automation works as follows:
Sub Kill_Automation ()
' Used to clear automation objects
Set gappExcel = Nothing
Set gwkbExcelBook = Nothing
Set grstExcelBook = Nothing
End Sub
Everything works fine until I run a second routine which basically
does the same thing. Only this time, I run into a "Range method of
Object '_Global' failed" run-time error. I doesn't seem to want to
recognize either the Excel application or the workbook object. If I
manually close excel, the second routine runs fine. Is there something
that manually closing the application does that I'm missing by using
..quit?
It seems to me that I could solve this problem by simply initiating
new objects but why should I when I have a set of perfectly good
objects ready to use?
It also seems that the automation is not releasing all resources
because after testing the routines 6 or more times I start to
encounter windows errors.
Any ideas would be helpful.
Thanks.
Comment