Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1312
  • Last Modified:

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

0
Wilder1626
Asked:
Wilder1626
  • 3
  • 2
1 Solution
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
Change line 19 to

.Row = R


And put back .Text since it's better code.
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 LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now