Automation Problem

[Gary Cobden]

I have a problem with the following code, which leaves an instance of
Excel visible in Task Manager.

By a process of elimination I have got it down to the fact that
something in the DoCmd.Transfer Spreadsheet line is holding on to an
Excel reference somewhere - can anybody assist (if I comment this line
out, the instance is released, and not visible in Task Manager).

Private Sub btnLoadData_Click()
Dim xlApp2 As Object
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim strFilename as string
Set strfilename = "c:\test.xls"
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
Select Case Me.Data_File_From
Case "West Herts"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="barbados"
Case "Luton"
xlApp2.Workbooks.Open FileName:=strFileName
Case "Stoke Mand"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
Case "South Bucks"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
Case "South Bucks Susp"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
End Select

DoCmd.TransferSpreadsheet , , strImportTableName, strFileName, True

xlApp2.Workbooks.Close
fCloseApp ("XLMain")

Set xlBook = Nothing

xlApp2.Quit
Set xlApp2 = Nothing

End Sub
Function fCloseApp(lpClassName As String) As Boolean

Dim lngRet As Long, hWnd As Long, pID As Long

hWnd = apiFindWindow(lpClassName, vbNullString)
If (hWnd) Then
lngRet = apiPostMessage(hWnd, WM_CLOSE, 0, ByVal 0&)
Call apiGetWindowThreadProcessId(hWnd, pID)
Call apiWaitForSingleObject(pID, INFINITE)
fCloseApp = Not (apiIsWindow(hWnd) = 0)
End If
End Function

Thanks

Gary

[Rich P]

I have been down this EXACT same road many a time and had the EXACT same
problem as you and used the EXACT same api code to try to close that
instance of Excel :). I finally found a solution that seems to work
between 99-100% of the time.

First, I would make the TransferSpreadsheet call and then I would do the
CreateObject thing after TransferSpreadsheet.

Second, instead of using an Object variable for xlApp2, I would make a
reference to your Excel Library (incase you haven't already) and
dimension your vars like this:

Dim xlApp2 As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet

Then you can use CreateObject(...). Note that I did not use the New
keywork in the Dim statements. I have tried New without CreateObject
but had issues. If you just reference the vars as I did and use
CreateObject, then xlApp2.Quit will really quit and no more Excel in
Task Manager. Plus, you get intellisense dropdowns this way.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Eric Schittlipz]

"Gary Cobden" <ga*********@nhs.net> wrote in message
news:4a**************************@posting.google.c om...

I have a problem with the following code, which leaves an instance of
Excel visible in Task Manager.

By a process of elimination I have got it down to the fact that
something in the DoCmd.Transfer Spreadsheet line is holding on to an
Excel reference somewhere - can anybody assist (if I comment this line
out, the instance is released, and not visible in Task Manager).

Private Sub btnLoadData_Click()
Dim xlApp2 As Object
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim strFilename as string
Set strfilename = "c:\test.xls"
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
Select Case Me.Data_File_From
Case "West Herts"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="barbados"
Case "Luton"
xlApp2.Workbooks.Open FileName:=strFileName
Case "Stoke Mand"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
Case "South Bucks"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
Case "South Bucks Susp"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
End Select

DoCmd.TransferSpreadsheet , , strImportTableName, strFileName, True

xlApp2.Workbooks.Close
fCloseApp ("XLMain")

Set xlBook = Nothing

xlApp2.Quit
Set xlApp2 = Nothing

End Sub
Function fCloseApp(lpClassName As String) As Boolean

Dim lngRet As Long, hWnd As Long, pID As Long

hWnd = apiFindWindow(lpClassName, vbNullString)
If (hWnd) Then
lngRet = apiPostMessage(hWnd, WM_CLOSE, 0, ByVal 0&)
Call apiGetWindowThreadProcessId(hWnd, pID)
Call apiWaitForSingleObject(pID, INFINITE)
fCloseApp = Not (apiIsWindow(hWnd) = 0)
End If
End Function

Thanks

Gary

Wthout commenting on the DoCmd.TransferSpreadsheet, your procedures need to
implement error handling. This is pasted from a reply a recently posted on
the same issue:
Here is an example of some error handling which calls xlApp.Quit if an error
occurs (e.g. trying to open a non-existant workbook) but only if the
instance of Excel was one that the code created - ie Excel was not already
running:

Sub OpenWorkbook()

On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlBook As Object
Dim blnError As Boolean
Dim blnNewApp As Boolean

' Switch off normal error handling as we
' attempt to use ruuning instance of Excel
' If Excel is not running, we try to start an instance
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set xlApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
blnError = True
MsgBox "Error starting Excel", vbCritical, "Error"
GoTo Exit_Handler
Exit Sub
Else
blnNewApp = True
End If
End If

' Switch normal error handling back on
On Error GoTo Err_Handler

Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")

xlApp.Visible = True
Exit_Handler:

On Error Resume Next

If Not xlBook Is Nothing Then
Set xlBook = Nothing
End If

If Not xlApp Is Nothing Then

If blnError And blnNewApp Then
xlApp.Quit
End If

Set xlApp = Nothing

End If

Exit Sub

Err_Handler:
blnError = True
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Gary Cobden]

Hi Rich

Thanks for your suggestion – which I hoped was going to work

Unfortunately, if I use the CreateObject statement after the Transfer
Spreadsheet as suggested, I get error 3161 – Could not decrypt file

Any further suggestions?

Thanks

Gary


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

Hi Gary,

Yes, I do have another suggestion. I steer away from
TransferSpreadsheet because of the very issue you describe. I use ADO
when I pass data to Excel. This technique is a little more
sophisticated than TransferSpreadSheet but offers a lot more control and
reliability. I will give you a sample code below. First, you need to
make sure you have Mdac2.5 and Mdac2.6 loaded on the computer you will
run this code. Mdac2.5 is the last version of Mdac that contains Jet
for interfacing with Access. Mdac2.6 is an upgrade for 2.5 which really
makes a difference in the ADO code useability. I think if you load 2.6
without having 2.5, the ADO may not work. You should be able to find
Mdac at MSDN or Google. Anyway, you make a reference in Tools/References
to Microsoft ActiveX Data Objects 2.6. Then here is the sample code:

-----------------------------------------------------------
Sub DataToExcelADO()
Dim DB As Database, RS1 As Recordset, strSql As String
Dim j As Integer, t As Integer, RetVal as Variant
Dim cn As New ADODB.Connection, RS As New ADODB.Recordset

Set DB = CurrentDb

strSourcePath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
strSourcePath = strSourcePath & "testExcel.xls;"
RS.CursorLocation = adUseClient
cn.Mode = adModeReadWrite
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

'---clear old data first
DoEvents
For j = 2 To 50 'clear old data from Excel Sheet
strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For k = 0 To RS.Fields.Count - 1: RS(k) = Null: Next
RS.Update
RS.Close
Next

'---populate Sheet1
Set RS1 = DB.OpenRecordset("tblTestData")
f = RS1.Fields.Count
j = 2
DoEvents
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For k = 0 To RS1.Fields.Count - 1
RS(k) = RS1(k)
Next
RS.Update
RS.Close
j = j + 1
RS1.MoveNext
t = t + 1
RetVal = SysCmd(acSysCmdSetStatus, t)
Loop
RS1.Close
cn.Close
End Sub
---------------------------------------------------------

Note that this technique does not use automation. You are referencing a
specific Excel workbook to write to. You can't do that with
TransferSpreadsheet. There are a few Gotcha's though. First, you have
to pre-populate the Excel sheet you are going to write to, save, then
delete the fake data. This preps the sheet for ADO. Then usually, you
can leave the cells in General format, but sometimes ADO complains if
you write data from a Date/Time field in an Access Table. So format
those columns in Excel as Date or Time.

The next Gotcha is that ADO will only write one row at a time to Excel.
If you try Select * from your table of 500 rows, it will write the first
row 500 times to Excel. So you have to close the ADO recordset var RS
for each row. But it is lightening fast. You can write 500 rows in
about 5 seconds, if that. Also note, that for the Excel file that I
reference, you have to end it with a semi colon ";"

strSourcePath = strSourcePath & "testExcel.xls;"

Then, the sql statement

strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"

references the sheet you will write to. You can name the sheet anything
you want in Excel, but you have to use the same name in this sql
statement. I am writing here to columns A through U. j is the row
counter. Note in the example code, I start j at Row 2 then I increment
j. I also use

RetVal = SysCmd(acSysCmdSetStatus, t)

to monitor the progress of the Do Loop. SysCmd write to the status bar
at the bottom of Access. But back to the sql statement, you can write
your data to any column, row you want by referencing it in the sql
statement

strSql = "SELECT * FROM [Sheet1$H" & j & ":AD" & j & "]"

This write to columns H through AD. Just make sure you have the same
number of columns in your table. Just a word of FYI, ADO is very
touchy. It does a lot of complaining about data types. So start out
with just text fields, like say a table with 5 columns of text. So your
sql statement would be

strSql = "SELECT * FROM [Sheet1$A" & j & ":E" & j & "]"

The Excel workbook can be open or close. Works much faster if the
workbook is closed. Once you are done passing your data, you can then
use automation to do other stuff. I do this all the time.

HTH
Rich


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

>>
You are referencing a specific Excel workbook to write to. You can't do
that with TransferSpreadsheet.
<<

Correction: You can reference a specific workbook with
TransferSpreadsheet, but you can't re-use that workbook unless you
delete the TransferSheet first because TransferSpreadsheet doesn't
overwrite. You don't have this problem with the ADO method. ADO will
write to any sheet you reference, and/or overwrite. Word of Caution, if
you pass 500 rows of data with ADO and then pass 300 rows another day,
you still have 200 old rows left over. So you need to clear out the old
data first. I usually, prep a worksheet for 10,000 rows with fake data
-- ";lkj;lkj;" --, save the workbook, then delete the fake data and save
again. The workbook will server as a template workbook (but not really
a template, not .xlt, just xls - once you pass your data use automation
to save the workbook as something else). Note: Prep Date/Time columns
with date/time data 1/1/1900, 00:05 and drag that down for 10,000 rows,
then delete.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!