MSFlexGrid to Excel

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 ) ?
razpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

razpAuthor Commented:
Edited text of question
0
SteveB032698Commented:
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
SteveB032698Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

razpAuthor Commented:
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
MirkwoodCommented:
Bought This Question.
0
torrid333Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.