Saving Excel as CSV

[MikeMV]

Hello,

I have an Excel spreadsheet that I need to save as a CSV file, I wonder if this is doable from within VFP?

I output the spreadsheet from within VPF and then run a routine to change the column headers and width, that all works fine, but I can't figure out how to tell VPF to save as a CSV, here is the code I have:

oExcel = CREATEOBJECT("Excel.Application")
oWorkBook=oExcel.Workbooks.Open("P:\InventoryFeed\inventory.xls")
oExcel.Cells(1,1).Value = "StockCode"
oExcel.Cells(1,2).Value = "In-Stock"
oExcel.Cells(1,3).Value = "Kit"
oExcel.Columns.AutoFit()
oWorkbook.Save()
oWorkbook.SaveAs("P:\InventoryFeed\inventory.csv","xlText")
oWorkbook.Close()
RELEASE oWorkbook
RELEASE oExcel

I have tried many different ways to format the SaveAs command, but none work.

I will greatly appreciate your help.

Mike.

 

[Nigel Gomm]

Mike,

#DEFINE xlCSV 6

oWorkbook.SaveAs("P:\InventoryFeed\inventory.csv",xlCSV)


if you open excel.exe with VFP's object browser (tools menu)
you can see xlFileFormat under enums.... with all the supported fileformats and their xlFileFormat value.

hth

Nigel

 

[Nigel Gomm]

p.s.

you will also want

oExcel.quit

after the oWorkbook.close() to prevent being left with a lingering excel process.

n

 

[Olaf Doschke]

oExcel.Columns.AutoFit() is merely a cosmetical change of the visual width of the excel columns and will not change, how wide the Cells will be in characters/bytes in the final CSV. You can skip that.

Bye, Olaf.

 

[JRB-Bldr]

A general note....

If you want to know how to use VFP Automation to make something occur in Excel, then do the work manually in Excel while recording the actions as a Macro.

Then open the Macro and look at how Excel did the operation.

Finally convert the Macro code into VFP Automation code.

Good Luck,
JRB-Bldr

 

[MikeMV]

>> to prevent being left with a lingering excel process <<

Thanks, yesterday doing testing I ended up with about 17 instance of Excel running.

I got everything working great, except for one pesky problem, after saving the file as a CSV I get the common Excel prompt of "Do you want to save the changes you made to inventory.csv?"

Do you know if there is a way to avoid this?

The code now looks like:

DELETE FILE P:\InventoryFeed\inventory.csv
*
oExcel = CREATEOBJECT("Excel.Application")
oWorkBook=oExcel.Workbooks.Open("P:\InventoryFeed\inventory.xls")
oExcel.Cells(1,1).Value = "StockCode"
oExcel.Cells(1,2).Value = "In-Stock"
oExcel.Cells(1,3).Value = "Kit"
oExcel.Columns.AutoFit()
oWorkbook.Save()
#DEFINE xlCSV 6
oWorkbook.SaveAs("P:\InventoryFeed\inventory.csv",xlCSV)
oWorkbook.Close()
oExcel.Quit()

RELEASE oWorkbook
RELEASE oExcel
RETURN

Thanks.

 

[Nigel Gomm]

hmmmm.... try

oexcel.ActiveWorkbook.Saved = .t.

after the saveas

n

 

[Mike Lewis]

I got everything working great, except for one pesky problem, after saving the file as a CSV I get the common Excel prompt of "Do you want to save the changes you made to inventory.csv?"

Do you know if there is a way to avoid this?

Instead of: oWorkbook.Close()

Do either: oWorkbook.Close(.T.) to save the changes, or oWorkbook.Close(.F.) to discard the changes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

One thing I always add somewhere before my SaveAs is

   oExcel.DisplayAlerts = .F.

Good Luck,
JRB-Bldr