mbsoftwaresolutions@mbsoftwaresolutions.com wrote on 2016-08-18:
Thanks, Joe, Stephen, and Kevin for these ideas. Yep...I've used Christof's ExcelXML class years ago here so that would work for sure. I'm going to try Joe's CSV approach first though, as there's no pretty formatting to be done with this.
On 2016-08-18 12:58, Kevin J Cully wrote: Thinking outside of the box...
Don't use the Excel automation. Convert the flat file into an XML string, and spit that out into a file named "MyFile.xls" and then have Excel open that. It'll be wicked fast. Excel will load the XML as if it was an Excel spreadsheet.
I think Christof had a posting a couple of years ago with a VFP class that would allow for some formatting (Bolds, colors, etc.) of the XML so it looks "nicer" when opened. I think you can even put formulas in the XML and have Excel honor it.
I've used this in VFP and Xojo over the years. It's a great way to do "Excel" without having Excel installed and without worrying about M$ borking up the automation version to version.
-Kevin
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Thursday, August 18, 2016 12:29 PM To: profox@leafe.com Subject: Flat file to Excel....but it's got more than 255 columns!
VFP9SP2/Win7Pro/SDF fixed width file
I've got a flat file that needs to be translated to an Excel file. Traditional program put it into a cursor and then via automation did copy/paste into Excel. Problem is now that there are more than 255 columns (410 to be exact) so using a VFP cursor/table won't work. I was just doing direct string manipulations and insertting right to the Excel cells, but it's a SLOW process. It works, but I'm always looking for a better way.
Suggestions for approach?
tia!
Mike,
Since you have an automation routine, try it with ExcelObject.Application.ScreenUpdating = .F.
https://msdn.microsoft.com/en-us/library/office/ff193498.aspx
It might speed it up.
Tracy Pearson PowerChurch Software