Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSFlexGrid to Excel

Posted on 1998-03-26
6
Medium Priority
?
529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Suggested Courses

715 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