Solved

VB6- Copy rows of MSHFlexgrid to excel cell issue

Posted on 2013-05-31
18
553 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
ID: 39212050
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
ID: 39212782
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
ID: 39212920
The code was written to read a text file and push the data into Excel.  You are using a grid as the source.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 11

Author Comment

by:Wilder1626
ID: 39213339
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
ID: 39213340
looks like the statement in error needs to follow the ReDim statement.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39213390
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
ID: 39216974
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
ID: 39218717
ok, let me try this and i will let you know the result.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39218789
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
ID: 39218815
what parameters are you passing to the routine?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39218868
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
ID: 39220110
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
ID: 39220558
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
ID: 39221861
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
ID: 39228618
Sorry for the delay, this one is more complicated for me.

i will try to work on it.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39288732
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
ID: 39288733
Thanks for your help
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

829 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