Excel 'SaveAs' via Word

[Ed]

I need (via Word) to open an Excel spreadsheet to read a .csv file that has
'Enters' within the scope of the data (as opposed to at the end of the line
which indicates 'end of record'), and in two very unusual situations, I need
to save the Excel file (.csv) to a 'normal' Excel '.xls' file.

Here is code so far:

Set oXL = CreateObject("Excel.Application")

'('file2convert' is a variable pointing to .csv file)
Set oWB = oXL.Workbooks.Open(file2convert)

'next line simply changes extension of file I want to save from .csv to .xls
file2save = Left(file2convert, InStr(file2convert, ".csv") - 1) & ".xls"

'next line tracks the actual Excel macro code, but the
"FileFormat:=xlNormal" generates error
'if I comment out that part of the line, it saves,
'but only as a csv file with an '.xls' extension.
'iI.e., there was no 'saveas' with a real conversion to xls
oWB.SaveAs FileName:=file2save , fileformat:=xlnormal

Any thoughts to make this work?

Ed (in Virginia)

 

[Doug Robbins - Word MVP]

All that you need is:

Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.Workbooks.Open(file2convert)
oWB.SaveAs FileFormat:=xlNormal
oWB.Close
Set oWB = Nothing
Set oXL = Nothing


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[Ed]

Thanks. That pretty much did it. Because of the late binding, "xlNormal"
must be a real value. I went into Excel to see what xlNormal represents. It
is an unusual number: -4143, but when I used it, it worked. I will now need
to program this for 2007, which I understand has a new set of numbers.
Thanks. Just getting anything to work was driving me crazy.

 

[Doug Robbins - Word MVP]

I was using 2007 when I created that and I believe that the -4143 also
applies to that version for xlNormal

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com