Export VB6 MSHflexgri to Excel issue

Hello all

I'm trying to export my grid from column 0 but it only export from column 1 to the end.

Do you know why?

Thanks again for your help.



Private Sub perdc_Click()
Dim i As Long, j As Long, Rw As Long, Cl As Long
    Dim oXlObj As Excel.Application
    Dim oXlWbObj As Excel.Workbook
    Dim MyArray() As String
    Dim found As Boolean
              
    Rw = 0
    
    With MSHFlexGrid2
        ReDim Preserve MyArray(.Rows - 1, 10)
        
        '~~> Get Headers
        For i = 1 To .Cols - 1
        On Error Resume Next
            MyArray(0, i) = MSHFlexGrid2.TextMatrix(0, i)
        Next i
        
        '~~> Loop through the rows
        For i = 1 To .Rows - 1
            
            If .TextMatrix(i, 3) <> "" Then
                found = True
                Rw = Rw + 1
                Cl = 0
                For j = 1 To .Cols - 1
                    MyArray(Rw, j) = MSHFlexGrid2.TextMatrix(i, j)
                    Cl = Cl + 1
                Next j
            End If
        Next i
    End With
    
    '~~> If instance found then get Excel ready for export
    If found = True Then
        Set oXlObj = New Excel.Application
        '~~> Add a new woorkbook
        Set oXlWbObj = oXlObj.Workbooks.Add
        
        For i = 0 To UBound(MyArray)
            For j = 1 To 15
                oXlWbObj.Sheets(1).Cells(i + 1, j).Value = MyArray(i, j)
            Next j
        Next i
        
        
         oXlObj.ActiveWorkbook.ActiveSheet.Range("A1:J1").Interior.Color = RGB(205, 197, 191)
         
         
       oXlObj.ActiveWorkbook.ActiveSheet.Columns("A:A").ColumnWidth = 8
        oXlObj.ActiveWorkbook.ActiveSheet.Columns("A:A").HorizontalAlignment = xlLeft
         oXlObj.ActiveWorkbook.ActiveSheet.Columns("A:A").Text = Format(oXlObj.ActiveWorkbook.ActiveSheet.Columns("A:A"), "00")
          oXlObj.ActiveWorkbook.ActiveSheet.Columns("B:B").ColumnWidth = 12
           oXlObj.ActiveWorkbook.ActiveSheet.Columns("B:B").HorizontalAlignment = xlLeft
            oXlObj.ActiveWorkbook.ActiveSheet.Columns("C:C").ColumnWidth = 17
             oXlObj.ActiveWorkbook.ActiveSheet.Columns("C:C").HorizontalAlignment = xlLeft
              oXlObj.ActiveWorkbook.ActiveSheet.Columns("D:D").ColumnWidth = 11
               oXlObj.ActiveWorkbook.ActiveSheet.Columns("D:D").HorizontalAlignment = xlLeft
                oXlObj.ActiveWorkbook.ActiveSheet.Columns("E:E").ColumnWidth = 8
                 oXlObj.ActiveWorkbook.ActiveSheet.Columns("E:E").HorizontalAlignment = xlLeft
                oXlObj.ActiveWorkbook.ActiveSheet.Columns("F:F").ColumnWidth = 11
               oXlObj.ActiveWorkbook.ActiveSheet.Columns("F:F").HorizontalAlignment = xlLeft
              oXlObj.ActiveWorkbook.ActiveSheet.Columns("G:G").ColumnWidth = 13
             oXlObj.ActiveWorkbook.ActiveSheet.Columns("G:G").HorizontalAlignment = xlLeft
            oXlObj.ActiveWorkbook.ActiveSheet.Columns("H:H").ColumnWidth = 13
           oXlObj.ActiveWorkbook.ActiveSheet.Columns("H:H").HorizontalAlignment = xlLeft
          oXlObj.ActiveWorkbook.ActiveSheet.Columns("I:I").ColumnWidth = 22
         oXlObj.ActiveWorkbook.ActiveSheet.Columns("I:I").HorizontalAlignment = xlLeft
        oXlObj.ActiveWorkbook.ActiveSheet.Columns("J:J").ColumnWidth = 22
       oXlObj.ActiveWorkbook.ActiveSheet.Columns("J:J").HorizontalAlignment = xlLeft
        
    oXlObj.ActiveWorkbook.ActiveSheet.Range("B2").Select


oXlObj.ActiveWindow.FreezePanes = True



        oXlObj.Visible = True
    End If
    'End If
End Sub

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?
 
karthika_ctsConnect With a Mentor Commented:
You try this one:

Private Sub FlexToExcel()
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
       
    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 MSFlexGrid1
        '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
        .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste              'Paste clipboard contents
    End With
   
    ' This makes Excel visible
    xlObject.Visible = True
End Sub

http://www.vbforums.com/showthread.php?t=393082
0
 
Wilder1626Author Commented:
Perfect

Thanks, i will take that one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.