Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
10 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
VB6- Copy rows of MSHFlexgrid to excel cell issue
Posted on 2013-05-31
Hello
Im using this macro below to copy all row from my grid and paste it in an excel workbook:
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
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
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
[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
-
9
7
18 Comments
Active today
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
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
Active today
The code was written to read a text file and push the data into Excel. You are using a grid as the source.
Active 4 days ago
Ok...
I just tried to upload from a txt file and i still have the same error.
i also used that text inside:
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
Active 4 days ago
Perfect
Now next issue i have is on
again out of range error
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?
Now next issue i have is on
Input #intFN, sngData(2, lngCount)
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)
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?
Active today
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.
Active 4 days ago
Ok, after the test, now i have a new run time error out of range on the BulkLoadFast.
On this part of the code:
Full code:
On this part of the code:
lngChunkSize = UBound(parmData, 2)
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
Active 4 days ago
I have used this for coping the MSHFlexgrid
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
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
Active today
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.
I am NOT using the clipboard object to do this.
Active 4 days ago
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?
Not sure i understand.
Is it possible to copy from an array in an MSHFlexgrid so that the extract command work?
Active today
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.
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.
Active 4 days ago
Sorry for the delay, this one is more complicated for me.
i will try to work on it.
i will try to work on it.
Active 1 day ago
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.
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.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 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…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 15 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.