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.
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
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.
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 = DataForRangeEnd Sub
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
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 WithDim 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 measurementDim 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
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.
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.