Inserting an xls file into another with Fox

[SmartMart]

Hi,

Im trying to insert a "glossary" page xls file onto the end of an .xls report that I am generating for various jobs.

I can currently get it to open the "glossary" file, cut and paste into the new report, but I lose the formatting (column widths etc).

Is there an easier way to do this? Can I "insert" the "Glossary" page somehow as opposed to cutting and pasting? Or what is the best way to cut and paste and keep the formatting, column widths, heights etc.

Any help will be GREATLY appreciated!

Thanks

 

[JRB-Bldr]

In general, whatever you can do in Excel itself, you can also do with automation from within a VFP application.

The best way to determine how to accomplish something via automation is do first do it within Excel itself (stand-alone).

Set Excel to record a Macro
Do the task
Then Stop recording the Macro
Examine the Macro via Edit

The Macro will be recorded as VBA code, but it can typically be converted relatively easily to VFP code.

Since you can already get the Glossary page pasted into your document, you are most of the way there.

If it is a simple Column Auto-Fit that you need, you could use:

* --- Auto-Fit Entire Worksheet ---
mnLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT
mcLastCol = ConvColumn(mnLastCol,.T.)
xlSheet.COLUMNS("A:" + mcLastCol).EntireColumn.AutoFit

For more complex formatting issues, such as Font type & size selection, underline, etc, You could use the VBA macro method to see what you need to do to get the formatting correct.

A last method (probably a last preference) would be to BEGIN by opening your glossary sheet, already formatted. Then add new worksheets and their associated data to it via VFP automation or cut & paste.
Then do a SAVEAS to save the end results to a new Excel file. In that way, you don't touch the glossary formatting.

Good Luck,
JRB-Bldr

 

[finglem]

I've managed something similar using the move command:

oExcel.Workbooks("X").Sheets("glossary").Move (oExcel.Workbooks("Y").Activesheet)

X = the name of the workbook that the glossary worksheet belongs to

Y = the name of the workbook you are adding the glossary sheet to.