excel obj - save as csv?

[Chuck]

From within a Word macro I'm trying to save an Excel object as a csv file,
but can't seem to find any documentation that explains how to do it. Any
ideas? Code below -- I've tried objExcel.dialogs(xlDialogSaveAs).show but
get an error saying that xlDialogSaveAs is an undefined variable, and when I
try to specify xlCSV as a file format I get the same undefined variable error:

sub SaveAsCSV()

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv"
'The above line saves as an .xls even if the suffix is ".csv"

objExcel.Application.Quit

Set objExcel = Nothing

end sub

 

[Peter]

Because you are using what's called "late binding" to reference Excel, Word doesn't recognize Excel properties, constants or methods.
Basically, you Dimmed your Excel object as a generic Object, then told Word to create an instance of that object as an Excel Application object. So Word creates that instance, but it has no knowledge of the methods/properties/constants/etc... of that object.
when you then access a method/property/etc... of that object, Word just passes along your request to the object, and if there's no match, passes back an error.

In your code you referenced the Excel constants xlCVS and xlDialogSaveAs by name. Word has no idea what those constants are, so it raises the "undefined variable" error.

Using late binding has the advantage of not being tied to one version of an object, but it has the disadvantage of not being able to refer to constants by name. Instead, you have to know and use the value of the constant.

To find out the value of a constant, open Excel, go to the VBA IDE (Alt+F11) and create a sub and a statement that uses the constant in which you're interested. Then step into the sub (F8, or use the Debug toolbar) and hover your mouse on the constant name. A tooltip containing the value of the constant should pop up.

For your specific case, in order to save an excel document as a CVS file, you need to either define xlCVS as a constant and pass to the SaveAs method the FileFormat, or just use the value of xlCVS. I prefer to define the constant because it improves readability:

Sub SaveAsCSV()

Const xlCVS As Integer = 6

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs Filename:="c:\myfile.csv", FileFormat:=xlCVS

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

Set objExcel = Nothing

End Sub

hth,

-Peter

 

[Helmut Weber]

Hi Chuck,
I think, there are two issues here.

1) Save as might require the type of file, too, like
ActiveWorkbook.SaveAs Filename:="C:\Test\Test3.csv", FileFormat:=xlCSV

2) Learn about early binding vs. late binding.
You are using late binding, I presume.
Early binding requires a reference to the other application's library
Extras, references, ...
After that, the application will recognise constants like xlCSV.
Otherwise you have to pass the value of the constant,
here:
xlCSV would be 6
xlDialogSaveAs would be 5

And make sure, that the workbook does not contain
more than 1 worksheet. Otherwise there would be an alert from Excel,
difficult to get rid of, may not posssible at all.
;-)

HTH

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Chuck]

Thanks for that explanation, it's really helpful. Lots of room for
exploration!

Because you are using what's called "late binding" to reference Excel, Word doesn't recognize Excel properties, constants or methods.
Basically, you Dimmed your Excel object as a generic Object, then told Word to create an instance of that object as an Excel Application object. So Word creates that instance, but it has no knowledge of the methods/properties/constants/etc... of that object.
when you then access a method/property/etc... of that object, Word just passes along your request to the object, and if there's no match, passes back an error.

In your code you referenced the Excel constants xlCVS and xlDialogSaveAs by name. Word has no idea what those constants are, so it raises the "undefined variable" error.

Using late binding has the advantage of not being tied to one version of an object, but it has the disadvantage of not being able to refer to constants by name. Instead, you have to know and use the value of the constant.

To find out the value of a constant, open Excel, go to the VBA IDE (Alt+F11) and create a sub and a statement that uses the constant in which you're interested. Then step into the sub (F8, or use the Debug toolbar) and hover your mouse on the constant name. A tooltip containing the value of the constant should pop up.

For your specific case, in order to save an excel document as a CVS file, you need to either define xlCVS as a constant and pass to the SaveAs method the FileFormat, or just use the value of xlCVS. I prefer to define the constant because it improves readability:

Sub SaveAsCSV()

Const xlCVS As Integer = 6

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs Filename:="c:\myfile.csv", FileFormat:=xlCVS

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

Set objExcel = Nothing

End Sub

hth,

-Peter

From within a Word macro I'm trying to save an Excel object as a csv file,
but can't seem to find any documentation that explains how to do it. Any
ideas? Code below -- I've tried objExcel.dialogs(xlDialogSaveAs).show but
get an error saying that xlDialogSaveAs is an undefined variable, and when I
try to specify xlCSV as a file format I get the same undefined variable error:

sub SaveAsCSV()

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv"
'The above line saves as an .xls even if the suffix is ".csv"

objExcel.Application.Quit

Set objExcel = Nothing

end sub

 

[Chuck]

Thanks for that explanation, it's really helpful. Lots of room for
exploration!

Because you are using what's called "late binding" to reference Excel, Word doesn't recognize Excel properties, constants or methods.
Basically, you Dimmed your Excel object as a generic Object, then told Word to create an instance of that object as an Excel Application object. So Word creates that instance, but it has no knowledge of the methods/properties/constants/etc... of that object.
when you then access a method/property/etc... of that object, Word just passes along your request to the object, and if there's no match, passes back an error.

In your code you referenced the Excel constants xlCVS and xlDialogSaveAs by name. Word has no idea what those constants are, so it raises the "undefined variable" error.

Using late binding has the advantage of not being tied to one version of an object, but it has the disadvantage of not being able to refer to constants by name. Instead, you have to know and use the value of the constant.

To find out the value of a constant, open Excel, go to the VBA IDE (Alt+F11) and create a sub and a statement that uses the constant in which you're interested. Then step into the sub (F8, or use the Debug toolbar) and hover your mouse on the constant name. A tooltip containing the value of the constant should pop up.

For your specific case, in order to save an excel document as a CVS file, you need to either define xlCVS as a constant and pass to the SaveAs method the FileFormat, or just use the value of xlCVS. I prefer to define the constant because it improves readability:

Sub SaveAsCSV()

Const xlCVS As Integer = 6

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs Filename:="c:\myfile.csv", FileFormat:=xlCVS

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

Set objExcel = Nothing

End Sub

hth,

-Peter

From within a Word macro I'm trying to save an Excel object as a csv file,
but can't seem to find any documentation that explains how to do it. Any
ideas? Code below -- I've tried objExcel.dialogs(xlDialogSaveAs).show but
get an error saying that xlDialogSaveAs is an undefined variable, and when I
try to specify xlCSV as a file format I get the same undefined variable error:

sub SaveAsCSV()

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv"
'The above line saves as an .xls even if the suffix is ".csv"

objExcel.Application.Quit

Set objExcel = Nothing

end sub

 

[Chuck]

Thanks Helmut, looks like this is a good project to break me in for
early/late binding!