VB6 - Send from MSFlexgrid to Excel issue

Hello all

I have this application where i want to export data from my MSHFlexgrid 1 to Excel.

I want to export the row ZERO with the heaters followed by the rows between row numbers from my 2 text box.

Ex:
If Val(from_row)  = 100
If Val(to_row)  = 102
 
It will  only send to excel the heather row + row 100 to 102.

But with the macro bellow, it always do row ZERO to 102 even if i have the value 100 in my from_row textbox.

How can i fix that?

Thanks again for your help

 Dim xlObject    As Excel.Application
    Dim xlWB        As Excel.Workbook
    Dim xlWs        As Excel.Worksheet
    Dim R As Long
    
    Set xlObject = New Excel.Application

    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add
    Set xlWs = xlWB.Sheets(1)
    
    With MSHFlexGrid2
        For R = Val(from_row) To Val(to_row)
            
             If UCase(Trim(MSHFlexGrid2.TextMatrix(R, 0))) = "LOCATION_NOT_MATCHING" Then
                Clipboard.Clear         'Clear the Clipboard
                '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 = R             'Select relevant rows
                Clipboard.SetText .Clip 'Send to Clipboard
           
                With xlWs
                    '~~> Bold all the relevant cells in 1 Go!
                    .Range("A1,A3,B3,C5,D5").Font.Bold = True
                    .Range("A6:BF6").Interior.Color = RGB(205, 197, 191)
                    
                    .Range("A:BF").NumberFormat = "@"
                    .Range("a1") = "Isotrak and TMS location extract from Oracle"
                    .Range("a1").Font.Size = 17
                    .Range("a3") = "Date of report:"
                    .Range("C3") = Format(Date, "mmm dd, yyyy")
                    .Columns("A:BF").HorizontalAlignment = xlLef
                    .Columns("A:A").ColumnWidth = 28
                    .Columns("B:B").ColumnWidth = 0
                    
                    xlObject.ActiveWorkbook.ActiveSheet.Range("A6").Select 'Select Cell A1 (will paste from here, to different cells)
                    xlObject.ActiveWorkbook.ActiveSheet.Paste   'Paste clipboard content

                    .Range("C7").Select

                    xlObject.ActiveWindow.FreezePanes = True

                    xlObject.ActiveWorkbook.ActiveSheet.Range("A6").Select
                End With
            End If
        Next
    End With
     xlObject.ActiveWorkbook.ActiveSheet.Name = "Location_not_matching"
     xlObject.ActiveWorkbook.ActiveSheet.Tab.ColorIndex = 4

xlObject.ActiveWorkbook.ActiveSheet.Columns("C:BF").AutoFit

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Change line 19 to

.Row = R


And put back .Text since it's better code.
0
 
Martin LissOlder than dirtCommented:
Put a breakpoint on line 13. What do you see when you put the cursor on from_row?

If from_row an ActiveX textbox?

You should use from_row.Text for self-documenting purposes but it shouldn't make a difference in the result.
0
 
Wilder1626Author Commented:
When i put the break, i see 100 and 102.

Before i used For R = Val(from_row) To Val(to_row), i tried like this also:
For R = from_row.text To to_row.text

But i was still having the same result.
0
 
Wilder1626Author Commented:
Thanks, i will tweak the code to also add the headers and then use your option.

If i just change to .Row. R, i don't keep the headers in the excel report.

Thanks again for your help
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
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.