Can VB6 work with multiple Excel sessions?

[crpjaviman]

Hello all,

It's been a while since my last thread.

I am having a slight problem with my VB6 project. The following code works great on the first run. The form has checkboxes to choose different reports in any combination.

The first time it runs, it runs great. Then, if I want to have a second report, the info ends up on the first report. I have been able to isolate the issue to the code is opening a second session but reverting to the first session and putting the info there.

I tried closing the first session and running a second, but the problem remains. Of course, if I close the VB project and run it again, it runs find for the second report.

Is there something that I am missing?

Here is part of the code:

Dim cnt As Integer
Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet
Dim intRow, xrow, x As Integer ' counter
Dim intCol As Integer ' counter
Dim TheRows, TheCols As Integer
Dim startdate, enddate As Variant
Dim startdate2, enddate2 As Variant
Dim WorkSheetName, SheetNameB As String
Dim flxgrid As Variant
Dim found As Variant
Dim flxgrid1 As MSHFlexGrid
Dim WorkSheetName2 As Form

On Error Resume Next

startdate = Nothing
enddate = Nothing
newname = Nothing
startdate2 = Nothing
enddate2 = Nothing
Set wbXL = Nothing
Set wsXL = Nothing
Set objXL = Nothing

If ckRpt = 0 And ckRpt1 = 0 And ckRpt2 = 0 Then
    MsgBox "Please choose one or more reports to view.", vbCritical, "Report Message"
    Exit Sub
End If

' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet

objXL.Dialogs(xlDialogSaveAs).Show
newname = wbXL.Name

Excel.Application.ScreenUpdating = True
StartForm.SetFocus

startdate = Format(txtStartDate.Text, "00000")
enddate = Format(txtEndDate.Text, "00000")


Windows(newname).Activate
'Sheets(1).Name = "Test One" [COLOR=red]'tested to check if sheet name was able to be changed. This is where my problem starts, the sheet name is not changed going thru by steps[/color]

enddate2 = enddate
startdate2 = startdate

If Weekday(startdate, vbMonday) <> 1 Then startdate = Val(startdate) + (1 - Weekday(startdate, vbMonday))
If Weekday(enddate, vbMonday) <> 1 Then enddate = Val(enddate) + (1 - Weekday(enddate, vbMonday))
startdate = Format(startdate, &quot;00000&quot;)
enddate = Format(enddate, &quot;00000&quot;)


If ckRpt Then Call Sales_ServiceFlexGrid_To_Excel(startdate, enddate, newname, startdate2, enddate2)

The code from here works if I can get the project to acknowledge the second session.

Can some give me some ideas?

Thanks,
Javier

Thanks,
crpjaviman

 

[crpjaviman]

I found out what I was doing wrong. It is working now. Thanks for all of those that looked at this posting.



Thanks,
crpjaviman