VB 6 -Excel application problem - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

VB 6 -Excel application problem

kel1981bkel1981b Posts: 852Member
First of all this is not VBA code. It's VB 6. Personaly I hate Excel and do not have that much experience coding VB 6 with Excell. But my boss like it. So ... 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
[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"

rsMonthly.MoveNext
Loop
'it's jaust part of the code

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
[/code]

Hate to say that but need help ASAP

Any help, advises, opinion will be highly appreciated


Comments

  • PavlinIIPavlinII Posts: 404Member
    : First of all this is not VBA code. It's VB 6. Personaly I hate Excel and do not have that much experience coding VB 6 with Excell. But my boss like it. So ... 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
    : [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"
    :
    : rsMonthly.MoveNext
    : Loop
    : 'it's jaust part of the code
    :
    : 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
    : [/code]
    :
    : Hate to say that but need help ASAP
    :
    : Any help, advises, opinion will be highly appreciated
    :
    :
    :
    Hi, I understand that needing help isn't pleasant ;-) But even the best sometimes don't know...
    I had this problem several weeks ago...
    [gray]Solution is unexpected and irrational but works[/gray]
    [green]Do not Quit objExcel[/green]
    [code] objWorkbook.Close
    objExcel.DisplayAlerts = False
    [green]' objExcel.Quit - Do not do this[/green]

    Set objSheet = Nothing
    Set objWorkbook = Nothing
    Set objWorkbooks = Nothing
    Set objExcel = Nothing [green]'But if you do this, it will work[/green][/code]
    Hope thiw is enough

    PavlinII
Sign In or Register to comment.