Solved

MSFlexGrid to Excel

Posted on 1998-03-26
6
519 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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 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…
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…

773 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