CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    98

    VB6 - Extract 3 grid in single excel file

    Hello all

    I have 3 grids that i would like to extract in 1 single excel sheet but each MSHflexgrid will be paste in different sheets.

    I would also like to name the sheet by the MSHFlexgrid name.

    How can i do that?

    Thanks again

    Name of grids:
    MSHFlexgrid1
    MSHFlexgrid2
    MSHFlexgrid3

    Thanks again

    This is the code i have for 1 single grid
    Code:
     
    Form8.Show (False)
        Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 2)
        'Start report
        Dim xlObject As Excel.Application
        Dim xlWB As Excel.Workbook
    
        With MSHFlexGrid2
            ' .Col = 13
            ' .Row = 1               'From first Row (header)
            ' .ColSel = .Cols - 1    'Select all columns
            ' .RowSel = .Rows - 1   'Select all rows
            ' .Sort = flexSortStringAscending
        End With
    
    
        'Delect concatanation first
        ' Dim k As Long
        ' For k = 1 To MSHFlexgrid2.Rows - 1
    
        '    If MSHFlexgrid2.TextMatrix(k, 1) <> "" Then
        '        MSHFlexgrid2.TextMatrix(k, 13) = ""
        '    End If
    
        ' Next k
    
    
        Set xlObject = New Excel.Application
    
        'This Adds a new woorkbook, you could open the workbook from file also
        Set xlWB = xlObject.Workbooks.Add
    
        Clipboard.Clear    'Clear the Clipboard
        With MSHFlexGrid2
            'Select Full Contents (You could also select partial content)
            .Col = 0               'From first column
            .Row = 0               'From first Row (header)
            .ColSel = .Cols - 1    'Select all columns
            .RowSel = .Rows - 1   'Select all rows
            Clipboard.SetText .Clip    'Send to Clipboard
    
        End With
    
        With xlObject.ActiveWorkbook.ActiveSheet
    
            xlObject.ActiveWorkbook.ActiveSheet.Range("a1") = "Rates not used in TMS"
            xlObject.ActiveWorkbook.ActiveSheet.Range("a1").Font.Bold = True
            xlObject.ActiveWorkbook.ActiveSheet.Range("a1").Font.Size = 17
    
            '205-197-191 = gris
            xlObject.ActiveWorkbook.ActiveSheet.Range("A6:I6").Interior.Color = RGB(205, 197, 191)
    
            xlObject.ActiveWorkbook.ActiveSheet.Range("B5") = "Door:"
            xlObject.ActiveWorkbook.ActiveSheet.Range("C5").HorizontalAlignment = xlRight
            xlObject.ActiveWorkbook.ActiveSheet.Range("C5").Font.Bold = True
    
            xlObject.ActiveWorkbook.ActiveSheet.Columns("A:A").ColumnWidth = 20
            xlObject.ActiveWorkbook.ActiveSheet.Columns("A:A").HorizontalAlignment = xlLeft
            xlObject.ActiveWorkbook.ActiveSheet.Columns("A:A").NumberFormat = "@"
            xlObject.ActiveWorkbook.ActiveSheet.Columns("B:B").ColumnWidth = 23
            xlObject.ActiveWorkbook.ActiveSheet.Columns("B:B").HorizontalAlignment = xlLeft
            xlObject.ActiveWorkbook.ActiveSheet.Columns("B:B").NumberFormat = "@"
            xlObject.ActiveWorkbook.ActiveSheet.Columns("C:C").ColumnWidth = 22    '44
    
    
            xlObject.ActiveWorkbook.ActiveSheet.Range("a3") = "Date of report:"
            xlObject.ActiveWorkbook.ActiveSheet.Range("a3").Font.Bold = True
            xlObject.ActiveWorkbook.ActiveSheet.Range("B3") = Format(Date, "mmm dd, yyyy")
            xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Font.Bold = True
    
            .Range("A6").Select    'Select Cell A1 (will paste from here, to different cells)
            .Paste   'Paste clipboard content
            xlObject.ActiveWorkbook.ActiveSheet.Range("C7").Select
            xlObject.ActiveWindow.FreezePanes = True
            'xlObject.ActiveWorkbook.ActiveSheet.Name = xlObject.ActiveWorkbook.ActiveSheet.Range("A1").Text & " " & xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Text
            'AddTotal1 xlObject.ActiveWorkbook.ActiveSheet
            xlObject.ActiveWorkbook.ActiveSheet.Range("A6").Select
            xlObject.ActiveWorkbook.ActiveSheet.Range("c5").Formula = lblTotalrecord2.Caption
            xlObject.ActiveWorkbook.ActiveSheet.Range("C5").Font.Bold = True
    
        End With
        xlObject.Visible = True
    
        Unload Form8

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB6 - Extract 3 grid in single excel file

    Save each grid as it's own spreadsheet, then call Excel to open it again...
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Tags for this Thread

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured