?
Solved

MSFlexGrid to Excel

Posted on 1998-03-26
6
Medium Priority
?
550 Views
Last Modified: 2012-08-13
I just made an application that calculate reports from a lot of data. In order to show the report in
columns and rows I used a FlexGrid. Now I want to export and save that data in an Excel Sheet
(myfile.xls). I decided that the simple way I could do it was to transfer all the flexgrid to an excel
sheet using this simple code ( below), but when I have a lot of rows in the flexgrid (more than 3000 for example)
it takes more than 10 minutes to transfer all the data (P200Mhz,32Mb RAM).Is there any other way I could transfer my data in the Flexgrid to an Excel sheet in a faster way?.


Set Hoja_Excel_diario = GetObject(, "Excel.Application")
Set Hoja_Excel_diario = GetObject("myfile.xls", "excel.sheet.8")
Hoja_Excel_diario.windows.Item(1).Visible = True

'Transfer data from FlexGrid1 (N Rows, 8 Col) to Excel Sheet

For i = 0 To FlexGrid1.Rows - 1
FlexGrid1.Row = i
For j = 0 To 7
FlexGrid1.Col = j
Hoja_Excel_diario.worksheets.Item(1).cells(i + 1, j +1).Value = Val(flexGrid1.Text)
Next
Next

Is there any way I can copy all the data in the FlexGrid ( in the clipboard,for example ) and then paste it into an Excel sheet with the same format ( N rows x 8 Col ) ?
0
Comment
Question by:razp
6 Comments
 

Author Comment

by:razp
ID: 1459870
Edited text of question
0
 

Expert Comment

by:SteveB032698
ID: 1459871
The flex grid contains the .clip property.  
This property contains a string which contains the selected cells.

To copy to Excel do the following

Dim strTemp as string

MSFlexGrid1.Row=1
MSFlexGrid1.Col=1                 '(or the starting row)
MSFLexgrid1.ColSel= 6         ' (or the number of columns)
MsFlexGrid1.RowSel=6       '(or the number of the last row)
strTemp=MSFlexGrid1.clip

clipboard.clear
clipboard.settext strTemp

0
 

Accepted Solution

by:
SteveB032698 earned 300 total points
ID: 1459872
The flex grid contains the .clip property.  
This property contains a string which contains the selected cells.

To copy to Excel do the following

Dim strTemp as string

MSFlexGrid1.Row=1
MSFlexGrid1.Col=1                 '(or the starting row)
MSFLexgrid1.ColSel= 6         ' (or the number of columns)
MsFlexGrid1.RowSel=6       '(or the number of the last row)
strTemp=MSFlexGrid1.clip

clipboard.clear
clipboard.settext strTemp
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:razp
ID: 1459873
Your answer was very useful, but there is only one problem.

Since all the data in the FlexGrid is "Text", when I use the following command (after I copied the data to the clipboard ):

Hoja_Excel_exportar.worksheets.Item(1).cells(3, 1).Select
Hoja_Excel_exportar.worksheets.Item(1).Paste

All the data is transfered correctly, but is left justified and is transfered as Text, which is not useful to me.

But ( funny thing ) if I go directly to the excel sheet and execute the command Paste in the sheet, the data that I get is right justified and with a format as Number ( since I can SUM all the data in each column ).

I really need to get the data as Number and not Text.

Please help me with this last thing.(I'll give more points if you want )
0
 
LVL 13

Expert Comment

by:Mirkwood
ID: 1459874
Bought This Question.
0
 

Expert Comment

by:torrid333
ID: 10281148
If you use this .clip method to copy the entire flexgrid including the "header row"... it does indeed paste into excel except that the header rows are not in their columns... it takes the new lines and uses them to create a new row....

Any ideas how to correct that???  Perhaps a routine that removes the newline characters?
0

Featured Post

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.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

599 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