Wilder1626
asked on
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
If i just change to .Row. R, i don't keep the headers in the excel report.
Thanks again for your help
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013
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.