Re opening Excel 2000 lost work book

[jim moose]

Hiya All

Ive got an vb application that opens MS Excel 200, to produce some
tables retreived from a database record set. All works fine the first time
that Excel is opened, and whilst it remains open susbsquent interaction with
Excel is fine as new tables are produced on sheet one.

If the Excel application is closed by the user then reopened however, Excel
re opens but no Worksheet is visible. Im aware that because my
Excel.Application object is global within my vb application that Excel is
visible form the task manager, i check on entering my Excel open method
whether an Excel object is avaiable through the Err code and also using is
nothing. Code below is available to guff at,

My one idea is from
http://support.microsoft.com/defaul...port/kb/articles/q178/5/10.asp&NoWebContent=1

which is concerned with referencing,
rather new to excel and vb, however if anyone has had similar experinces or
idea will be apprec

<<<<<CODE>>>>>>
jim

Public Sub openExcel(QueryName As String)
Const rowstart = 2

Dim myWorkbook As Excel.Workbook
Dim mySheet As Worksheet
Dim myRS As DAO.Recordset

Dim I As Integer
'ignore errors
On Error Resume Next
Err.Clear
If myExcel Is Nothing Then

'look for a running copy of Wor
Set myExcel = GetObject(, "Excel.Application")

'If Word is not running then
If Err.Number = 429 Then
Set myExcel = CreateObject("Excel.Application") 'run it

' Clear error
Err.Clear
End If
End If

If myExcel.ActiveWorkbook Is Nothing Then
' Add a work book
Set myWorkbook = myExcel.Workbooks.Add

Else
Set myWorkbook = myExcel.ActiveWorkbook
End If

' Get a reference to the first sheet
Set mySheet = myWorkbook.ActiveSheet

If mySheet Is Nothing Then
Set mySheet = myWorkbook.Sheets.Add(1)
End If
myWorkbook.RefreshAll
mySheet.Visible = xlSheetVisible
' Open record set
Set myRS = dbsApex.OpenRecordset(QueryName)

'This loop will collect the field names and place them in the first
'row starting at "A1"
For I = rowstart To (myRS.Fields.Count + rowstart) - 1
mySheet.Cells(rowstart, I - rowstart + 1).Value = myRS.Fields(I -
rowstart).Name
Next I

'The next line simply formats the headers to bold font
With mySheet.Range(mySheet.Cells(rowstart, 1), mySheet.Cells(rowstart,
myRS.Fields.Count))
.Font.Bold = True
.Interior.Color = RGB(215, 215, 215)
End With

' Paste to worksheet
mySheet.Range("A" & CStr(rowstart + 2)).CopyFromRecordset myRS


'This next code set will just select the data region and
'auto-fit the columns
With myExcel
' .Sheets("Sheet1").Select
.ActiveSheet.Select
.Range("A" & CStr(rowstart + 2)).Select
.Selection.CurrentRegion.Select
.Selection.Columns.AutoFit
.Range("A" & CStr(rowstart + 2)).Select
.Visible = True
End With

' Ensure column width fits the headers
For I = rowstart To (myRS.Fields.Count + rowstart) - 1
If mySheet.Cells(rowstart, I - rowstart + 1).ColumnWidth <
Len(myRS.Fields(I - rowstart).Name) Then
mySheet.Cells(rowstart, I - rowstart + 1).ColumnWidth =
Len(myRS.Fields(I - rowstart).Name) + 5
End If
Next I

mySheet.PageSetup.Orientation = xlLandscape

' Clean up afterwords
Set myRS = Nothing
Set mySheet = Nothing
Set myWorkbook = Nothing



End Sub

 

[Tom Ogilvy]

Set myRS = Nothing
Set mySheet = Nothing
MyWorkbook.close SaveChanges:=False
Set myWorkbook = Nothing
MyExcel.Quit
set myExcel = Nothing


End Sub


Also, there is an article in the KB that says that using With End With in
an automation situation can create non releasable references. So instead,
fully qualify your objects.

 

[jimmymoose]

Set myRS = Nothing
Set mySheet = Nothing
MyWorkbook.close SaveChanges:=False
Set myWorkbook = Nothing
MyExcel.Quit
set myExcel = Nothing

Cheer for the swift reply,im replying from google cause ntlworld is

very up and down, with more down then up, i ve got other questions but
my big question is if you could answer: when a do a getobject the
first time the is no excel so it goes into the create object, and
creates and references a excel object, which adds one to the reference
counter. Does Excel also add one so that there are two references,
cause whilst trying to solve the problem i removed everything apart
from the get and create. So i kill the Excel reference by pressing
close, but the should still be the reference i created in my appl,
like with COM. Why can i not use that orginal reference if count is
greater then or equal to one? As much as anything i wish to understand
the processes that are occcuring otherwise i may as well be a daft
fish? i come from a C++ background but i enjoy VB, but sometimes it
results in headaches

in the mean time i used

If myExcel Is Nothing Then
Set myExcel = New Excel.Application
End If

which worked a treat, but also reading through newgroups a conflicting
opinion
occurs, with one side saying that Excel.Application is more efficent
then CreateObject, whilst another saying CreateObject is more generic
able cater to different possible versions of Excel if i read right, to
add to my confusion and limited ole exp is that im also calling word
within my app and using bookmark etc, with MS access

regardless

thanks for the reply

jim

 

[Tom Ogilvy]

If the reference is a ghost reference, your application is never going to
find myexcel as anthing but nothing. If there is a reference to excel, it
is not through any of your variables, because you have set to nothing. A
reference that gets created by referring to a component in the Excel object
model that is not fully qualified creates such a reference.

I suspect if using the new keyword seems to avoid the problem, then I would
guess you will find you have one or more instances of excel running in the
task manager that have not been released. You have not solved your problem,
you have avoided the problem of connecting to one of these broke instances
of Excel - would be my assessment. The reference will be released with
your application stops running I believe.

http://support.microsoft.com/default.aspx?scid=kb;en-us;178510
PRB: Excel Automation Fails Second Time Code Runs

http://support.microsoft.com/default.aspx?scid=kb;EN-US;189618
PRB: Automation Error Calling Unqualified Method or Property

Anyway, I wouldn't dare to call myself an expert on COM or anything close to
it. These just represent my opinions.