antifashionpimp
Registered User.
- Local time
- Today, 03:54
- Joined
- Jun 24, 2004
- Messages
- 137
Hi there,
I am trying to figure out what is wrong with my automation procedure. I am using Access as an automation client, and Excel as an automation server.
What my program does, is read values that are defined in a user-defined type; each type represents a page of a questionnaire. Then, I create a .csv file to store all these values. I first create headings for each value in the .csv, and then export the values to the .csv file.
My problem is as follows:
When writing data from a recordset to an excel file, it all works fine the FIRST TIME around. However, the function I wrote to close the instance of Excel then doesn’t work – probably due to the fact that the Excel object is declared as a global variable. Therefore, when I run this procedure again (SECOND TIME), to write a recordset to an Excel file, it crashes because there is already an Excel instance in my Task Manager.
So this is the code here:
FIRST ATTEMPT
Then, I tried someone else’s suggestion, and created a local Excel object, not global. This should „Ensure that every property / method / object within the automated application is referenced through -- and only through -- an explicit variable declared in the calling program. That will ensure that there is no behind-the-scene direct reference from the calling program to the automated application. “
When I do it this way though, I get an error, something like „Runtime error 429 – Couldn’t create ActiveX object„. The debugger points to the statement:
Set objXLApp = GetObject(, "Excel.Application")
My procedure WriteToCSV now looked like this:
SECOND ATTEMPT
How do I solve this problem? At the moment when I run the first attempt, I can make the procedure run once, after that I must close down the Access database completely.
Can someone please (a very big please
) have a look through my code and see whether or not I’m making some common mistake? Or make some suggestion?
A huge thanks in advance
Jean
I am trying to figure out what is wrong with my automation procedure. I am using Access as an automation client, and Excel as an automation server.
What my program does, is read values that are defined in a user-defined type; each type represents a page of a questionnaire. Then, I create a .csv file to store all these values. I first create headings for each value in the .csv, and then export the values to the .csv file.
My problem is as follows:
When writing data from a recordset to an excel file, it all works fine the FIRST TIME around. However, the function I wrote to close the instance of Excel then doesn’t work – probably due to the fact that the Excel object is declared as a global variable. Therefore, when I run this procedure again (SECOND TIME), to write a recordset to an Excel file, it crashes because there is already an Excel instance in my Task Manager.
So this is the code here:
FIRST ATTEMPT
Code:
Public gobjExcel As Excel.Application
Public Sub WriteToCSV(typAnf As PopUpAnfang, intIntroFrage As Integer, typAny1 As FragebogenS1, typAny2 As FragebogenS2, _
typAny3 As FragebogenS3, typAny4 As FragebogenS4, typAny5 As FragebogenS5, _
typAny6 As FragebogenS6)
On Error GoTo WriteToCSV_Err
Dim objWS As Excel.Worksheet
Dim rst As ADODB.Recordset
Dim intCount As Integer
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
intCount = 1
If CreateExcelObj() Then
'create Excel object successfully, write to file
rst.Open "Select * from tblÜberschriften" 'contains headings
rst.MoveFirst
With gobjExcel
.Workbooks.Add
Set objWS = gobjExcel.ActiveSheet
'fill cells with headings
Do Until rst.EOF
objWS.Cells(1, intCount) = rst![Namen]
intCount = intCount + 1
rst.MoveNext
Loop
'close recordset
rst.Close
'clear recordset and db objects
Set rst = Nothing
'fill cells with data under headings
With objWS
Cells(2, 1) = typAnf.VPNummer
Cells(2, 2) = typAnf.VPName
Cells(2, 3) = intIntroFrage
Cells(2, 4) = typAny1.Fs1all
Cells(2, 5) = typAny1.Fs1a
Cells(2, 6) = typAny1.Fs1b
Cells(2, 7) = typAny1.Fs2
..................................
Cells(2, 125) = typAny6.Studium
Cells(2, 126) = typAny6.StudiumText
'save
.SaveAs FileName:=APPPATH & "\FragebogenCSV\" & typAnf.VPName & "_" & typAnf.VPNummer & ".csv", _
FileFormat:=xlCSVWindows
End With
End With
End If
WriteToCSV_Exit:
Call CloseExcel
Exit Sub
WriteToCSV_Err:
MsgBox "Fehler # " & Err.Number & ": " & Err.Description
Resume WriteToCSV_Exit
End Sub
Function CreateExcelObj() As Boolean
On Error GoTo CreateExcelObj_Err
CreateExcelObj = False
'Attempt to Launch Excel
Set gobjExcel = New Excel.Application
CreateExcelObj = True
CreateExcelObj_Exit:
Exit Function
CreateExcelObj_Err:
MsgBox "Könnte nicht Microsoft Excel ausführen!!", vbCritical, "Warnung!"
CreateExcelObj = False
Resume CreateExcelObj_Exit
End Function
Public Sub CloseExcel()
On Error GoTo CloseExcel_Err
If Not gobjExcel Is Nothing Then
gobjExcel.DisplayAlerts = False
gobjExcel.Quit
End If
CloseExcel_Exit:
Set gobjExcel = Nothing
Exit Sub
CloseExcel_Err:
MsgBox "Fehler # " & Err.Number & ": " & Err.Description
Resume CloseExcel_Exit
End SubThen, I tried someone else’s suggestion, and created a local Excel object, not global. This should „Ensure that every property / method / object within the automated application is referenced through -- and only through -- an explicit variable declared in the calling program. That will ensure that there is no behind-the-scene direct reference from the calling program to the automated application. “
When I do it this way though, I get an error, something like „Runtime error 429 – Couldn’t create ActiveX object„. The debugger points to the statement:
Set objXLApp = GetObject(, "Excel.Application")
My procedure WriteToCSV now looked like this:
SECOND ATTEMPT
Code:
Public Sub WriteToCSV(typAnf As PopUpAnfang, intIntroFrage As Integer, typAny1 As FragebogenS1, typAny2 As FragebogenS2, _
typAny3 As FragebogenS3, typAny4 As FragebogenS4, typAny5 As FragebogenS5, _
typAny6 As FragebogenS6)
'On Error GoTo WriteToCSV_Err
Dim objXLApp As Excel.Application
Dim objWS As Excel.Workbook
Dim rst As ADODB.Recordset
Dim intCount As Integer
Dim blIStartedXL As Boolean
Dim blResult As Boolean
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
intCount = 1
Set objXLApp = GetObject(, "Excel.Application")
If objXLApp Is Nothing Then
Set objXLApp = CreateObject("Excel.Application")
blIStartedXL = True
End If
rst.Open "Select * from tblÜberschriften" 'contains headings
rst.MoveFirst
Set objWS = objXLApp.Workbooks.Add
'fill cells with headings
Do Until rst.EOF
objXLApp.Range("a" & CStr(intCount)) = rst![Namen]
intCount = intCount + 1
rst.MoveNext
Loop
'close recordset
rst.Close
'clear recordset and db objects
Set rst = Nothing
'fill cells with data under headings
'save
objXLApp.Range("b1") = typAnf.VPNummer
objXLApp.Range("b2") = typAnf.VPName
........................................................
objXLApp.Range("b125") = typAny6.Studium
objXLApp.Range("b126") = typAny6.StudiumText
'save
objXLApp.SaveAs FileName:=APPPATH & "\FragebogenCSV\" & typAnf.VPName & "_" & typAnf.VPNummer & ".csv", _
FileFormat:=xlCSVWindows
WriteToCSV_Exit:
If blIStartedXL Then
objXLApp.Quit
End If
Set objXLApp = Nothing
Set objWS = Nothing
'Call CloseExcel
Exit Sub
WriteToCSV_Err:
MsgBox "Fehler # " & Err.Number & ": " & Err.Description
Resume WriteToCSV_Exit
End SubHow do I solve this problem? At the moment when I run the first attempt, I can make the procedure run once, after that I must close down the Access database completely.
Can someone please (a very big please
) have a look through my code and see whether or not I’m making some common mistake? Or make some suggestion?A huge thanks in advance
Jean
