Save excel in CSV format

[rachit.goyal]

I am doing excel automation using C++ without MFC. I just want to open
Workbook->Worksheet and want to save that in CSV format using SaveAs
function. I got how to load excel application and open workbook and
worksheet, but I am not able to save it in CSc format. I am struggling
with VARIANT array values.

I am following one of the word automation example available in MSDN.
But MS word VARIANT array is not working in case of excel. Please
guide me.

This is code, I am using -


//Set up the DISPPARAMS for the SaveAs method (11 arguments)
VARIANT vArgsSaveAs[11];
DISPPARAMS dpSaveAs;
dpSaveAs.cArgs = 11;
dpSaveAs.cNamedArgs = 0;
dpSaveAs.rgvarg = vArgsSaveAs;

BSTR bstrEmptyString;
bstrEmptyString = ::SysAllocString(OLESTR(""));

VARIANT vFalse;
vFalse.vt = VT_BOOL;
vFalse.boolVal = FALSE;

BSTR bstrTemp = ::SysAllocString(OLESTR("c:\\Excelexp.csv"));
vArgsSaveAs[10].vt = VT_BSTR;
vArgsSaveAs[10].bstrVal = bstrTemp; //Filename
vArgsSaveAs[9].vt = VT_I4;
vArgsSaveAs[9].lVal = 6; //CSV FileFormat
vArgsSaveAs[8] = vFalse; //LockComments
vArgsSaveAs[7].vt = VT_BSTR;
vArgsSaveAs[7].bstrVal = bstrEmptyString; //Password
vArgsSaveAs[6].vt = VT_BOOL;
vArgsSaveAs[6].boolVal = TRUE; //AddToRecentFiles
vArgsSaveAs[5].vt = VT_BSTR;
vArgsSaveAs[5].bstrVal = bstrEmptyString; //WritePassword
vArgsSaveAs[4] = vFalse; //ReadOnlyRecommended
vArgsSaveAs[3] = vFalse; //EmbedTrueTypeFonts
vArgsSaveAs[2] = vFalse; //SaveNativePictureFormat
vArgsSaveAs[1] = vFalse; //SaveFormsData
vArgsSaveAs[0] = vFalse; //SaveAsOCELetter


//Invoke the SaveAs method
OLECHAR FAR* szFunction;
DISPID dispid_SaveAs;
szFunction = OLESTR("SaveAs");
hr = pWorkbook->GetIDsOfNames(IID_NULL, &szFunction, 1,
LOCALE_USER_DEFAULT,
&dispid_SaveAs);
hr = pWorkbook->Invoke(dispid_SaveAs, IID_NULL,
LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpSaveAs, NULL, NULL, NULL);

 

[dtate22]

Enclosed is the VBA Code... Please translate to the C++ code and you should
be good to go!

Sub ConvertFile()
Dim sFile As String
Dim sFileCSV As String

sFile = "File1.xlsx"
sFileCSV = "File1.csv"

'3) Open new file and Save As "CSV"
OpenSaveAsCSV sFile, sFileCSV

End Sub

' Below is the real MAGIC!
'==============================================================================
' DATE: 2009-01-28
' TIME: 09:37 AM
' PURPOSE: Open a given file and save as another file format and clos
'==============================================================================
Sub OpenSaveAsCSV(sFile As String, sFileCSV As String)
Set ExportBook = Workbooks.Open(sFile)

With ExportBook
.SaveAs Filename:=sFileCSV, FileFormat:=xlCSV
.Close SaveChanges:=True
End With

End Sub

I am doing excel automation using C++ without MFC. I just want to open
Workbook->Worksheet and want to save that in CSV format using SaveAs
function. I got how to load excel application and open workbook and
worksheet, but I am not able to save it in CSc format. I am struggling
with VARIANT array values.

I am following one of the word automation example available in MSDN.
But MS word VARIANT array is not working in case of excel. Please
guide me.

This is code, I am using -


//Set up the DISPPARAMS for the SaveAs method (11 arguments)
VARIANT vArgsSaveAs[11];
DISPPARAMS dpSaveAs;
dpSaveAs.cArgs = 11;
dpSaveAs.cNamedArgs = 0;
dpSaveAs.rgvarg = vArgsSaveAs;

BSTR bstrEmptyString;
bstrEmptyString = ::SysAllocString(OLESTR(""));

VARIANT vFalse;
vFalse.vt = VT_BOOL;
vFalse.boolVal = FALSE;

BSTR bstrTemp = ::SysAllocString(OLESTR("c:\\Excelexp.csv"));
vArgsSaveAs[10].vt = VT_BSTR;
vArgsSaveAs[10].bstrVal = bstrTemp; //Filename
vArgsSaveAs[9].vt = VT_I4;
vArgsSaveAs[9].lVal = 6; //CSV FileFormat
vArgsSaveAs[8] = vFalse; //LockComments
vArgsSaveAs[7].vt = VT_BSTR;
vArgsSaveAs[7].bstrVal = bstrEmptyString; //Password
vArgsSaveAs[6].vt = VT_BOOL;
vArgsSaveAs[6].boolVal = TRUE; //AddToRecentFiles
vArgsSaveAs[5].vt = VT_BSTR;
vArgsSaveAs[5].bstrVal = bstrEmptyString; //WritePassword
vArgsSaveAs[4] = vFalse; //ReadOnlyRecommended
vArgsSaveAs[3] = vFalse; //EmbedTrueTypeFonts
vArgsSaveAs[2] = vFalse; //SaveNativePictureFormat
vArgsSaveAs[1] = vFalse; //SaveFormsData
vArgsSaveAs[0] = vFalse; //SaveAsOCELetter


//Invoke the SaveAs method
OLECHAR FAR* szFunction;
DISPID dispid_SaveAs;
szFunction = OLESTR("SaveAs");
hr = pWorkbook->GetIDsOfNames(IID_NULL, &szFunction, 1,
LOCALE_USER_DEFAULT,
&dispid_SaveAs);
hr = pWorkbook->Invoke(dispid_SaveAs, IID_NULL,
LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpSaveAs, NULL, NULL, NULL);

 

[rachit.goyal]

Hi dtate22,
In above VBA code, you are opening only workbook not worksheets.
If open workbook have multiple worksheets, will it work ?
How can I open all present worksheets and save each in CSV format.
Can I get available worksheets count and name dynamicay ?

Regards,
Rachit

 

[dtate22]

Yes, All worksheets are in a list.
- I suggest that you look at the intellisense given and you should get a
general idea of how it works, but in the mean time, let me detail the members
that you will need to save each worksheet as an individual CSV.

Article: http://www.mvps.org/dmcritchie/excel/saveas.htm

Variables
==========
Dim CurWkbook As Workbook
Dim wkSheet As Worksheet


Example code
==========
For Each wkSheet In CurWkbook.Worksheets
shtcnt(1) = shtcnt(1) + 1
Application.StatusBar = shtcnt(1) & "/" & shtcnt(2) & _
" " & wkSheet.Name
wkSheetName = Trim(wkSheet.Name)
If wkSheetName = Left(Application.ActiveWorkbook.Name, _
Len(Application.ActiveWorkbook.Name) - 4) Then _
wkSheetName = wkSheetName & "_D" & dtimestamp
Workbooks.Add
ActiveWorkbook.SaveAs _
filename:=xpathname & wkSheetName & ".xls", _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", CreateBackup:=False, _
ReadOnlyRecommended:=False
Set newWkbook = ActiveWorkbook

Application.DisplayAlerts = False
newWkbook.Worksheets("sheet1").Delete
On Error Resume Next
newWkbook.Worksheets(wkSheet.Name).Delete
On Error GoTo 0
Application.DisplayAlerts = True

CurWkbook.Worksheets(wkSheet.Name).Copy Before:=newWkbook.Sheets(1)
'no duplicate sheet1 because they begin with "a"
ActiveWorkbook.Save
ActiveWorkbook.Close
Next wkSheet