VB 6 - Excel problem. Hate to say that but need help ASAP

[kel1981b]

Personaly I hate Excel. But my boss like it. So B-(... Here it is the problem. For some reason, I cannot open .xls file generated by my application till application running. Let me explain. I run the Application, it generates .xls file. So far so good. Now I want to open .xls file from Explorer without stopping VB application. I minimize application form, open Explorer and double click on .xls file. Something strange happened. File is opened just partially, all other part of screen is still Explorer window. If I stop VB application completely, I can open .xls file from Explorer without any problem. Opening file from Excel don't cause any problem either even if, VB application is not stoped. Seems to me something stuck in the memory. I thing I destroy all Excel objects generated by VB application but looks like I missed something or did something wrong .
Here is piece of my code

Dim objWorkbook As excel.Workbook
Dim objExcel As excel.Application
Dim objSheet As excel.Worksheet

Set objExcel = New excel.Application
Set objWorkbook = excel.Workbooks.Add

For G = 1 To 2
   objExcel.DisplayAlerts = False
   objWorkbook.Sheets(G).Delete
Next

With objWorkbook
         
 .Sheets(1).Name = "Monthly"
 .Sheets(1).Range("A1:K1").Font.Bold = True
 .Sheets(1).Range("A1:K1").Columns(1).ColumnWidth = 20
 .Sheets(1).Range("A1:K1").Columns(2).ColumnWidth = 60
 .Sheets(1).Range("A1:K1").Columns(3).ColumnWidth = 10
 .Sheets(1).Range("A1:K1").Columns(4).ColumnWidth = 30

 .Sheets(1).Cells(1, 1) = "Customer Code"
 .Sheets(1).Cells(1, 2) = "Customer Name"
 .Sheets(1).Cells(1, 3) = "Request ID"
 .Sheets(1).Cells(1, 4) = "Report/Copy Type"
 .Sheets(1).Cells(1, 5) = "Primary Name"
 
  Do Until rsMonthly.EOF
            
    .Sheets(1).Cells(1, 1) = "Customer Code"
    .Sheets(1).Cells(1, 2) = "Customer Name"
    .Sheets(1).Cells(1, 3) = "Request ID"
    .Sheets(1).Cells(1, 4) = "Report/Copy Type"
    ' so on
    rsMonthly.MoveNext
  Loop
End With
       
strFileName = "C:\TestReportMontly.xls"

objWorkbook.SaveAs strFileName

objWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Quit

Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWorkbooks = Nothing
Set objExcel = Nothing

Any help, advises, opinion will be highly appreciated

 

[paulbent]

I had something like this when automating Lotus 1-2-3 and bizarrely it was caused by not making the app visible before opening a workbook. I doubt if Excel has the same bug but it may be worth trying:

Set objExcel = New excel.Application
objExcel.Visible = True
Set objWorkbook = excel.Workbooks.Add

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[kel1981b]

Thanks for your reply paulbent. Unfortunately, it did not fix problem.