Excel object - save as csv from Word?

[Guest]

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

 

[Tom Ogilvy]

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv", Fileformat:=6

 

[Guest]

Hi Tom

Thanks for that -- it works. However, it seems that when I use that syntax,
if I save in any format other than an Excel format, I get a series of Excel
save as dialogs (warnings that the file exists, the Excel dialog itself, then
a warning about saving in a non Excel format) in addition to the Word dialog
pointing out that a file of the same name already exists (I want to
overwrite). Is there any way to avoid having all those Excel dialogs pop up?

Chuck

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv", Fileformat:=6

--
Regards,
Tom Ogilvy

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

 

[Tom Ogilvy]

On error resume next
Kill "C:\Myfile.csv
On Error goto 0
objExcel..DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv",
Fileformat:=6"Chuck"
objExcel.ActiveWorkbook.Close SaveChanges:=False
objExcel.DisplayAlerts = True

--
Regards,
Tom Ogilvy

Hi Tom

Thanks for that -- it works. However, it seems that when I use that syntax,
if I save in any format other than an Excel format, I get a series of Excel
save as dialogs (warnings that the file exists, the Excel dialog itself, then
a warning about saving in a non Excel format) in addition to the Word dialog
pointing out that a file of the same name already exists (I want to
overwrite). Is there any way to avoid having all those Excel dialogs pop up?

Chuck

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv", Fileformat:=6

--
Regards,
Tom Ogilvy

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

 

[Guest]

That's excellent, you're a star! It's common sense really.

On error resume next
Kill "C:\Myfile.csv
On Error goto 0
objExcel..DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv",
Fileformat:=6"Chuck"
objExcel.ActiveWorkbook.Close SaveChanges:=False
objExcel.DisplayAlerts = True

--
Regards,
Tom Ogilvy

Hi Tom

Thanks for that -- it works. However, it seems that when I use that syntax,
if I save in any format other than an Excel format, I get a series of Excel
save as dialogs (warnings that the file exists, the Excel dialog itself, then
a warning about saving in a non Excel format) in addition to the Word dialog
pointing out that a file of the same name already exists (I want to
overwrite). Is there any way to avoid having all those Excel dialogs pop up?

Chuck

objExcel.ActiveWorkbook.SaveAs FileName:="c:\myfile.csv", Fileformat:=6

--
Regards,
Tom Ogilvy

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