Solved

VB6 - Send from MSFlexgrid to Excel issue

Posted on 2013-11-03
5
1,097 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
  • 3
  • 2
5 Comments
 
LVL 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now