Consolidate excel file with multiple sheets

[TheLazyPig]

Hi!

I can consolidate a multiple .xls file to 1 .dbf file but what if that xls file has multiple sheets and I only needed that specific sheet? How can I do that?


Thanks for the replies!

 

[Chris Miller]

The simplest way, I guess, is to OLE automate Excel to open the multisheet file and save the sheet you want as a separate xls to import that to dbf.
An oExcel.ActiveWorkbook.Worksheets(N) is a single sheet and has a SaveAs method, so each Sheet can be saved as a single xls(x) file and then be imported.

Chriss

 

[Mike Lewis]

Another option would be like this:

[tt]
USE TheDBF
APPEND FROM TheWorkbook.xls TYPE XL8 [highlight #FCE94F]SHEET SheetName[/highlight][/tt]

You specifically said that the Excel file was an XLS, so the above should work. But you can't use APPEND FROM with an Excel XLSX file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Crikey Mike, I hadn't come across xl8 as an option, but that because it is only applicable to append from not copy to.

I note that xl5 also supports the sheetname option.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

I note that xl5 also supports the sheetname option.

That's right. But unfortunately none of the APPEND FROM options support XLSX files. For that, you need a completely different approach. See, for example, Vilhelm-Ion Praisach's work, described in thread184-1759033.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Yes, it's funny how APPEND FROM offers types XLS, XL5 and XL8, EXPORT and COPY TO only XLS and XL5. Maybe an indicator that they didn't thought it was necessary to create newer excle files. as Excel 8.0 (That's Excel 97 for PCs) was capable of reading in older format files anyway.

I always saw it as a sign of losing interest of Office integration that VFP7,8,9 weren't upgraded in that respect. I can imagine the first input and output routines didn't even come from Microsoft themselves and so they never picked up on it, really.

The OLE automation part is still capable to automate even the latest Office versions, as long as they are installed as the Desktop versions of the office applications. And that has nice options with arrays, you can easily "paste" an array to an Excel range or simply set array = OleExcelRange.Value. With restrictions on data types as VFP/OLE/Excel aren't compatible in that respect..



Chriss

 

[Chris Miller]

See

http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation

Paragraph "Getting Data from Excel", where it's described technically.

Chriss