Aughhh! Opening then Closing Excel in App - Excel remains in memory 7

[Nicholas]

On my form I have a routine Private Sub SendToExcel() in which I have this code:<br>
<br>
Dim objExcel as Excel.Application<br>
Dim objWorkbook As Excel.Workbook<br>
Dim objWorkSheet As Excel.Worksheet<br>
Dim objRange As Excel.Range<br>
<br>
I then open Excel as:<br>
<br>
Set objExcel = GetObject(&quot;&quot;, &quot;Excel.Application&quot<br>
objExcel.Visible = False<br>
Set objWorkbook = objExcel.Workbooks.Add<br>
objExcel.DisplayAlerts = False<br>
<br>
The recorset is sent to Excel and formatted. I end with:<br>
<br>
Set objExcel = Nothing<br>
Set objWorkSheet = Nothing<br>
Set objWorkbook = Nothing<br>
Set objRange = Nothing<br>
<br>
I noticed during testing, that Excel was opening with a blank sheet but no errors occurred. I opened Task Manager and saw &quot;Excel&quot; listed 23 times, once for each time I ran the routine! <br>
<br>
The only time Excel was unloaded was if I ended my entire programme. My questions are:<br>
<br>
1. How to run only one Excel instance<br>
2. Unload Excel completely once this routine is finished.<br>
<br>
I certainly do not want 23 instances of this app.<br>
<br>
Thanks all - thank you, thank you!<br>
<br>
Nick<br>
<p>Nicholas, Bank of America NetO<br><a href=mailto: > </a><br><a href= > </a><br>

 

[chiph]

You have to copy the data from the grid to the excel spreadsheet cell by cell. It's slow & ugly, but it works.

BTW, use the &quot;Range&quot; property in the excel worksheet with the cell name used twice: MyWorksheet.Range(&quot;A1..A1&quot = MyGrid.CellContents(iRow, iColumn)

Chip H.

 

[dvannoy]

The King,

ColID and Row ID are they actual fields in your grid?? I am trying to test this with a filed called Color....
rs.Fields(&quot;Color&quot..

sorry to bug

 

[TheKing]

it is not a bug dvannoy
ColID is the Id for the excel column (the letters)
RowID is the Id for the excel rows (the numbers)

TheKing

 

[dvannoy]

OK, so whats this part then RS_REP.Fields(ColIdx - 1)? I am a little confused on how you reference your datagrid.

based on the code in this thread, Excel will open fine. I have a datagrid with one field called Color.. this is just to allow me to test it.

I set my datagrid = rs

now the data gets into the grid fine. I put your code behind a command button and thats where I get errors saying not found in collection. and it highlites

objExcel.Range(ColId & RowID).Value = RS_REP.Fields(ColIdx - 1)

objExcel - did you dim that as excel.application, range or what?

Thanks

 

[TheKing]

sorry I am new to posting code, here is my click event:

Private Sub cmdExcel_Out_Click()
' this is where we output to Excel spreadsheet
' this is in General Declarations
' Dim objExcel As Excel.Application

On Error Resume Next
Set objExcel = GetObject(, &quot;Excel.Application&quot
If Err.Number <> 0 Then
Set objExcel = CreateObject(&quot;Excel.Application&quot
End If
Err.Clear

objExcel.Visible = True
objExcel.WindowState = xlMaximized
objExcel.Workbooks.Add

RS_REP.MoveFirst ' my recordset that I used to populate my data grid
s = 0
r = RS_REP.Fields.Count
RowNum = 0
Do While (RS_REP.EOF = False)
s = s + 1
RowID = Trim(s)
For ColIdx = 1 To r
ColSet = Int((ColIdx - 1) / 26)
If (ColSet = 0) Then
ColId = Chr(ColIdx + 64)
Else
ColId = Chr(ColSet + 64) & Chr((ColIdx - (ColSet * 26)) + 64)
End If
objExcel.Range(ColId & RowID).Value = RS_REP.Fields(ColIdx - 1)
Next ColIdx
RS_REP.MoveNext
Loop
Set objExcel = Nothing

On Error GoTo 0

End Sub

ok maybe I didn't explain myself well the first time
I tried to do this off of a data grid but didn't get it to work so I use the recordset (RS_REP) that I used to poputate my data grid. so I don't use the data grid I use the recordset that I used to fill the data grid.

Hope this helps.
Tell me if it does.

TheKing

 

[dvannoy]

Thanks worked perfect..tryed to give you a star but I guess your not a member.

Thanks again

 

[TheKing]

Wow really?

I always thought I was a member.

What else do I need to be a member I wonder.

TheKing

 

[dvannoy]

I guess it went through this time..


Thanks again

 

[TheKing]

Glad I could help

TheKing

 

[eblattner]

Had the same problem. The user was the one to close Excel, but an instance stayed in memory. This fixed my problem...

'In the declarations:
Dim WithEvents xlBook As Excel.Workbook

'the calling procedure:

Dim ExpName As String

ExpName = xlsfile ' the name of the excel file I am opening

ADb.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, UseQueryName, ExpName, True 'Save the spreadsheet

On Error GoTo excelerror

Set xlBook = GetObject(ExpName)
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True

excelerror:

MsgBox (&quot;Error &quot; + Str$(Err.Number) + Chr$(13) + Err.Description + Chr$(13) + Chr$(13) + &quot;Could not open Excel spreadsheet&quot

exit sub

'in the form code:
Private Sub xlBook_BeforeClose(Cancel As Boolean)

xlBook.Application.Quit

Set xlBook = Nothing

End Sub

Let me know if this helps - this is my first replay, I hope I can be some help, I sure have gotten a lot of help from here!

Eddie

 

[dvannoy]

The King,

did you ever figure out a way to make the label names for the fields being exported to excel follow?? I have alot of data on my forms and it gets a little hard to tell what goes with what since the column names in excel are not named after the export...


Thanks

 

[vb5prgrmr]

You can also enumerate through the active processed looking for excel.exe in memory.