VB6- Copy rows of MSHFlexgrid to excel cell issue

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
LVL 11
Wilder1626Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
aikimarkConnect With a Mentor Commented:
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
 
aikimarkCommented:
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
 
Wilder1626Author Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
 
aikimarkCommented:
The code was written to read a text file and push the data into Excel.  You are using a grid as the source.
0
 
Wilder1626Author Commented:
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
 
aikimarkCommented:
looks like the statement in error needs to follow the ReDim statement.
0
 
Wilder1626Author Commented:
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
 
aikimarkCommented:
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
 
Wilder1626Author Commented:
ok, let me try this and i will let you know the result.
0
 
Wilder1626Author Commented:
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
 
aikimarkCommented:
what parameters are you passing to the routine?
0
 
Wilder1626Author Commented:
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
 
aikimarkCommented:
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
 
Wilder1626Author Commented:
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
 
Wilder1626Author Commented:
Sorry for the delay, this one is more complicated for me.

i will try to work on it.
0
 
Martin LissOlder than dirtCommented:
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
 
Wilder1626Author Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.