dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Excel Sheet Disappears

  1. #1
    Join Date
    Sep 2005
    Posts
    24

    Question 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" & 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

  2. #2
    Join Date
    Jan 2004
    Location
    San Diego
    Posts
    148

    Re: Excel Sheet Disappears

    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
    Death is life's special way of telling you you're fired.

    For I do not seek to understand in order to believe, but I believe in order to understand. For I believe this: unless I believe, I will not understand. - Anselm of Canterbury (10331109)

  3. #3
    Join Date
    Sep 2005
    Posts
    24

    Smile Re: Excel Sheet Disappears

    Thanks for the feedback. Your solution worked.

    Thanks again

    Dan S

  4. #4
    Join Date
    Jan 2004
    Location
    San Diego
    Posts
    148

    Re: Excel Sheet Disappears

    Your welcome. Glad I could help.
    Death is life's special way of telling you you're fired.

    For I do not seek to understand in order to believe, but I believe in order to understand. For I believe this: unless I believe, I will not understand. - Anselm of Canterbury (10331109)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)