Solved

MSFlexGrid to Excel

Posted on 1998-03-26
6
521 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 100 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

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

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.
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…
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…

821 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