Excel Sheet Disappears

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: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
ExcelSheet.Application.Visible = True



End Sub

help !!!

Dan S

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.

Code:

---

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

---

Thanks for the feedback. Your solution worked.

Thanks again

Dan S

Your welcome. Glad I could help.