Excel Sheet Disappears

[dsperry101]

I am trying to pass data from an array in VB5 to an excel spread sheet
This code works but the spread sheet closes back up. I want the user to be able
access the spread sheet and then return to my program. The Variables are arrays
that are variable length.

Private Sub Command1_Click()

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

ExcelSheet.Application.Visible = True
With ExcelSheet.Application
Dim myarray As Variant
.Selection.NumberFormat = "0.00"
.Range("a1:l" & LTrim(Str(Dataptr(1, 0)))).Select
.Range("a1:a" & LTrim(Str(Dataptr(1, 0)))).Value = .Transpose(Shottime)
.Range("b1:b" & LTrim(Str(Dataptr(1, 0)))).Value = .Transpose(Shotpos)
.Range("c1:c" & LTrim(Str(Dataptr(1, 1)))).Value = .Transpose(Shearpos)
.Range("d1" & LTrim(Str(Dataptr(1, 2)))).Value = .Transpose(Clamppos)
.Range("e1:e" & LTrim(Str(Dataptr(1, 3)))).Value = .Transpose(Nitropress)
.Range("f1:f" & LTrim(Str(Dataptr(1, 4)))).Value = .Transpose(Clamppress)
.Range("g1:g" & LTrim(Str(Dataptr(1, 5)))).Value = .Transpose(Shotpres)

Rangestr = "A1:L" & LTrim(Str(Dataptr(1, 0)))
.Range(Rangestr).Select
End With
ExcelSheet.Application.Visible = True



End Sub

help !!!

Dan S

[NatThoelecke]

Your ExcelSheet object is local to the Click event. So when End Sub is called ExcelSheet is destroyed causing your worksheet to close. Defining ExcelSheet as a Global object should keep it open until you're ready to have it closed through your program or by command from the user. Here's how it would look.

Option Explicit
Dim ExcelSheet As Object

Private Sub Command1_Click()

  Set ExcelSheet = CreateObject("Excel.Sheet") 

  ExcelSheet.Application.Visible = True 
  With ExcelSheet.Application 
    Dim myarray As Variant 
    .Selection.NumberFormat = "0.00" 
    .Range("a1:l" & LTrim(Str(Dataptr(1, 0)))).Select 
    .Range("a1:a" & LTrim(Str(Dataptr(1, 0)))).Value = .Transpose(Shottime) 
    .Range("b1:b" & LTrim(Str(Dataptr(1, 0)))).Value = .Transpose(Shotpos) 
    .Range("c1:c" & LTrim(Str(Dataptr(1, 1)))).Value = .Transpose(Shearpos) 
    .Range("d1:d" & LTrim(Str(Dataptr(1, 2)))).Value = .Transpose(Clamppos) 
    .Range("e1:e" & LTrim(Str(Dataptr(1, 3)))).Value = .Transpose(Nitropress) 
    .Range("f1:f" & LTrim(Str(Dataptr(1, 4)))).Value = .Transpose(Clamppress) 
    .Range("g1:g" & LTrim(Str(Dataptr(1, 5)))).Value = .Transpose(Shotpres) 

    Rangestr = "A1:L" & LTrim(Str(Dataptr(1, 0))) 
    .Range(Rangestr).Select 
  End With
 
  'This next line is redundant you could take it out and things would still work
  ExcelSheet.Application.Visible = True      

End Sub

[dsperry101]

Thanks for the feedback. Your solution worked.

Thanks again

Dan S

[NatThoelecke]

Your welcome. Glad I could help.