General information
Category:
COM/DCOM and OLE Automation
Environment versions
Network:
Windows 2003 Server
Jeana,
To save with CSV format you need ...saveas(..., xlCSV), where xlCSV=6. It will save the active sheet, and without any user dialog if you set from the very beginning:
ObjExcel.DisplayAlerts = .f.
There could be still some problems (first row missing when appending from csv, for example). Also as a rule the formatting in Excel does not work if applied to entire column. Another problem could be that the user applied different formats to cells in the very same column. So be prepared ...
Try also IMPORT command. Perhaps it will help in your case.
>Yuri,
>
>I do not have the control over the excel file to change the format so first I tried the automation to change the format as follows. This is a excel workbook and I need the data from Sheet(3) - "Sheetname"
>
>*------------------
>
>loWorkbook = GETOBJECT("C:\MyData.xls")
>loWorkbook.Sheets("SheetName").Select && Activate the specific sheet
>
>*--- Change the Date format in Excel sheet
>
>WITH loWorkbook.ActiveSheet.Range("I3:I65536")
> lcDateFormat = .NumberFormat
> .NumberFormat = "mm/dd/yy"
>ENDWITH
>
>*--- Clos Excel File without saving
>
>loWorkBook.Close(.F.)
>
>*--- Copy excel file to a temp dbf file..
>
>SELECT Mydbf && Temp dbf to append data
>APPEND FROM ("C:\MyData.xls")TYPE XL5 SHEET SheetName
>
>*-- Save and get rid of the object
>
>RELEASE loWorkbook
>
>*---------------------------------------------
>
>Yuri.., all the above works but it doesn't give the correct date from excel. I still get an empty date in VFP.
>
>My questions :
>
> 1) Where do I go wrong in above code ??
> 2) As you said, I prefer to Copy my Excel to Csv and go from there. but how can I specify the sheet name in csv file?
>
> APPEND FROM ("C:\MyData.csv")TYPE csv SHEET SheetName
>
> gives an error message.
>
>Please Help !!!
>
>TIA
>
>- Jeana -
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only