Solved

VB6 - Send from MSFlexgrid to Excel issue

Posted on 2013-11-03
5
1,168 Views
Last Modified: 2013-11-03
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
Comment
Question by:Wilder1626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39619826
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 39619836
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
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39619843
Change line 19 to

.Row = R


And put back .Text since it's better code.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39619847
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39619849
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question