Solved

VB6- Copy rows of MSHFlexgrid to excel cell issue

Posted on 2013-05-31
18
545 Views
Last Modified: 2013-06-30
Hello

Im using this macro below to copy all row from my grid and paste it in an excel workbook:

    With MSHFlexGrid2
                '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 = .Rows - 1    'Select all rows
                Clipboard.SetText .Clip    'Send to Clipboard



                With xlObject.ActiveWorkbook.ActiveSheet

                    xlObject.ActiveWorkbook.ActiveSheet.Columns("A:V").HorizontalAlignment = xlLeft

                    xlObject.ActiveWorkbook.ActiveSheet.Columns("A:V").NumberFormat = "@"

                    xlObject.ActiveWorkbook.ActiveSheet.Range("a3") = "Date of report:"
                    xlObject.ActiveWorkbook.ActiveSheet.Range("a3").Font.Bold = True
                    xlObject.ActiveWorkbook.ActiveSheet.Range("B3") = Format(Date, "mmm dd, yyyy")
                    xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Font.Bold = True
                    xlObject.ActiveWorkbook.ActiveSheet.Range("D5").NumberFormat = "0"


                    .Range("A6").Select    'Select Cell A1 (will paste from here, to different cells)
                    .Paste   'Paste clipboard content


                    xlObject.ActiveWorkbook.ActiveSheet.Range("C7").Select

                    xlObject.ActiveWindow.FreezePanes = True
                    xlObject.ActiveWorkbook.ActiveSheet.Range("A6:W6").AutoFilter
                    xlObject.ActiveWorkbook.ActiveSheet.Columns("A:W").AutoFit
                    xlObject.ActiveWorkbook.ActiveSheet.Name = "EQUIPMENT CLASSES RSSULTS"



                End With

Open in new window


What i would like to modify on that macro is that if in my TEXT1, i have the value 19, it will copy from row 0 plus the 19 rows after.

If i put 50 in the TEXT1, it will copy the row 0 plus the 50 rows after.

How can i update it?

Thanks again
0
Comment
Question by:Wilder1626
  • 9
  • 7
18 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I wouldn't use the clipboard, if it can be helped.

Populate a 2D array with the data and make a value assignment to the destination cells.  I describe this as well as an ADO alternative in my Fast Data Push to Excel article.
http://www.experts-exchange.com/A_2253.html
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Hi aikimark

Thanks for that topic. I was trying to replicate the project but i'm facing an out of range run time error on the import data command.

run time error
what i did is to put data into an excel file and try to upload it.

do you know what it could be?
Test.zip
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The code was written to read a text file and push the data into Excel.  You are using a grid as the source.
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Ok...

I just tried to upload from a txt file and i still have the same error.

i also used that text inside:

4501
1	19.7585
1.666	24.8874
2.332	27.468
2.998	25.9469

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
looks like the statement in error needs to follow the ReDim statement.
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Perfect

Now next issue i have is on
Input #intFN, sngData(2, lngCount)

Open in new window


again out of range error

 ReDim Preserve sngData(1 To 2, 1 To (lngStart + lngLines))
      lngStart = UBound(sngData, 2)
      If lngStart = 1 Then lngStart = 0
      
      
      For lngCount = (lngStart + 1) To (lngStart + lngLines)
        Input #intFN, sngData(2, lngCount)

Open in new window


But if i remove that part of the macro in error, it give me a result:
Finish Data Import. 4501 rows in memory.

it looks wrong, does it?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
You don't need to do any of the file I/O.  Your program has direct access the the TextMatrix array associated with the grid.  Just copy the data you want transferred from the part of the TextMatrix array into an array that is the size necessary to hold the transferred data.
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
ok, let me try this and i will let you know the result.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Ok, after the test, now i have a new run time error out of range on the BulkLoadFast.

On this part of the code:
lngChunkSize = UBound(parmData, 2)

Open in new window



Full code:
Private Sub BulkLoadFast(parmRange As Object, parmData() As Single)
  Dim DataForRange() As Single
  Dim lngLoop As Long
  Dim lngChunk As Long
  Dim rngChunk As Object
  Dim lngCol As Long
  Dim lngChunkSize As Long
  
  lngChunkSize = UBound(parmData, 2)
  ReDim DataForRange(1 To UBound(parmData, 2), LBound(parmData, 1) To UBound(parmData, 1))
  For lngLoop = 1 To lngChunkSize
    For lngCol = LBound(parmData, 1) To UBound(parmData, 1)
      DataForRange(lngLoop, lngCol) = sngData(lngCol, lngLoop)
    Next
  Next
  Set rngChunk = parmRange.Range(parmRange.Offset(0, 0), parmRange.Offset(lngChunkSize - 1, 1))
  rngChunk.Value = DataForRange
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
what parameters are you passing to the routine?
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
I have used this for coping the MSHFlexgrid

             
    Clipboard.Clear 'Clear the Clipboard
    With MSHFlexGrid1
        '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 = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard

    End With

Open in new window



Full:
              
    Clipboard.Clear 'Clear the Clipboard
    With MSHFlexGrid1
        '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 = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard

    End With
    
Dim xlObj As Object    'New Excel.Application -- only used with Excel reference
  Dim wkbOut As Object   'Excel.Workbook
  Dim wksOut As Object   'Excel.Worksheet
  Dim rngOut As Object   'Excel.Range
  Dim sngStart As Single    'forperformance measurement
Dim sngData() As Single

  'output to Excel workbook
  lblStatus.Caption = "Begin Excel Data Export"
  Set xlObj = CreateObject("Excel.Application")
  Set wkbOut = xlObj.Workbooks.Add
  Set wksOut = wkbOut.Worksheets("Feuil1")  'can skip this step
  Set rngOut = wksOut.Range("A1")           'by replacing with wkbOut.Worksheets("Sheet1").Range("A1")
  
  Me.MousePointer = vbHourglass
  Me.Enabled = False
  
  xlObj.ScreenUpdating = False
  xlObj.Calculation = -4135     '=xlCalculationManual

  sngStart = Timer
  BulkLoadFast rngOut, sngData
  
  lblStatus.Caption = "Finished Excel Data Export. (" & Timer - sngStart & " seconds)"

  xlObj.Calculation = -4105     '=xlCalculationAutomatic
  xlObj.ScreenUpdating = True
  xlObj.Visible = True
  
  Set rngOut = Nothing
  Set wksOut = Nothing
  Set wkbOut = Nothing
  Set xlObj = Nothing

  Me.MousePointer = vbDefault
  Me.Enabled = True

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The article describes a method of transferring data from an array directly into a workbook, in an assignment statement that has the .VALUE property on the left side of the equal sign.  You could also transfer data using a .CopyFromRecordset method, but your data isn't in a recordset.  The array is the correct intermediate data structure.

I am NOT using the clipboard object to do this.
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Now i,m lost.

Not sure i understand.
Is it possible to copy from an array in an MSHFlexgrid so that the extract command work?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
This isn't exact, but should give you an idea.
1. Get the area borders in your grid from the user.
2. ReDim an array to the same size
3. iterate over each row/col to populate the array
4. determine where you want to put the data in a worksheet
5. assign the (same shape and size) range.value = your array

=====
Notes:
* In VB6, you can ReDim and array with dimensions that start with any value, positive or negative.  This can closely/exactly match the rows and columns chosen by your user if they are specifying a contiguous block of grid cells.
* You will use a nested For...Next loop for the row/col iteration
* I've grown to appreciate the wks.range(wks.cells(r,c), wks.cells(rr,cc)) range referencing when doing the data push.
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Sorry for the delay, this one is more complicated for me.

i will try to work on it.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 500 points for aikimark's comment #a39221861

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
Comment Utility
Thanks for your help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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

11 Experts available now in Live!

Get 1:1 Help Now