Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re opening Excel 2000 lost work book
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/default...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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re opening Excel 2000 lost work book
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. -- Regards, Tom Ogilvy jim moose wrote in message ... 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/default...microsoft.com: 80/support/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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re opening Excel 2000 lost work book
"Tom Ogilvy" wrote in message
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re opening Excel 2000 lost work book
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...b;en-us;178510 PRB: Excel Automation Fails Second Time Code Runs http://support.microsoft.com/default...b;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. -- Regards, Tom Ogilvy jimmymoose wrote in message om... "Tom Ogilvy" wrote in message 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace Book 1 When Opening File in Excel | Excel Discussion (Misc queries) | |||
excel crash when opening work book | Excel Discussion (Misc queries) | |||
I want user form to display when opening a work book | Excel Worksheet Functions | |||
Q - Excel 2000: How to refer to worksheet in the same book? | Excel Worksheet Functions | |||
recovering a lost book in excel that was not saved... | Excel Discussion (Misc queries) |