Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
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 1 day 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 1 day 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 1 day 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 1 day 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 1 day 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 1 day 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 today
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
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…
Background
What I'm presenting in this article is the result of 2 conditions in my work area:
We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…
Suggested Courses
Course of the Month11 days, 7 hours left to enroll
718 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.