vbscript and excel - opening and saving files 2

[kregh99]

Does anyone know any vbscript code that will open a bunch of excel spreadsheet (.xls), save them as character-separated value text files (.csv), and then close them?

I'm not very good at this language (I usually program in Perl) and I just need something to perform this seemingly simple task.

Thanks in advance for the help.

 

[SarkMan]

Try this

set xlApp = CreateObject("excel.application&quot
xlApp.Visible = false
xlApp.DisplayAlerts = False
newFileName="C:\test.csv"
xlApp.Workbooks.Open "C:\Book1.xls"
xlApp.ActiveWorkbook.SaveAs newFileName, 6, false
xlApp.ActiveWorkbook.Close(0)
xlApp.Quit
Set xlApp = Nothing

 

[kregh99]

Thanks much. Worked like a charm on the test file I had set up.

If I had 20 files in a directory, could I loop it and use variables to have it run through all 20 files, and name the .csv file the same as the .xls file it opened?

If that's possible, how could you read the contents of the directory?

EX: Spreasheet -> BSmith_TimeSheet_08252003.xls
Text Files -> BSmith_TimeSheet_08252003.csv

 

[SarkMan]

Set fso = CreateObject("Scripting.FileSystemObject&quot
set objFolder=fso.GetFolder("C:\YourDirectory\&quot
set objFile=objFolder.files
For each file in objFile

do your csv stuff
msgbox(file.name)
next
set objFile=nothing
set objFolder=nothing

 

[kregh99]

So how would I handle this section:

newFileName="C:\test.csv"
xlApp.Workbooks.Open "C:\Book1.xls"
xlApp.ActiveWorkbook.SaveAs newFileName, 6, false

Would newFileName be set equal to objFile concatenated with .csv? (What is the concatenation opertor?)
And when you do the xlApp.Workboos.Open, that's objFile, right?

(Just trying to get my syntax straight.)

 

[PHV]

You can try something like this:

Set X=CreateObject("excel.application")
X.Visible=False
X.DisplayAlerts=False
Set fso=CreateObject("Scripting.FileSystemObject")
Set F=fso.GetFolder("\Path\to\directory\")
set FF=F.Files
For Each W In FF
  If Right(Ucase(W.Path),3)="XLS" Then
    newPath=Replace(W.Path,".xls",".csv",1,1,1)
    WScript.Echo W.Path & " -> " & newPath
'                       ^ concatenation operator
    X.Workbooks.Open W.Path
    X.ActiveWorkbook.SaveAs newPath,6,False
    X.ActiveWorkbook.Close(0)
  End If
Next
X.Quit

Hope This Help
PH.

 

[kregh99]

Everybody's responses have been enormously helpful and have saved me a ton of time and aggravation.

Thanks.

If you guys want a chuckle, go to

http://caffeinedout.com/ui

(not an ad - just some not-for-profit techie humor).