Programmatically save .xls as .csv using ASP

  • Thread starter Thread starter kayaker411
  • Start date Start date
K

kayaker411

Greetz!

I receive .xls spreadsheets daily to a directory on my local machin
which is running IIS. I want to process them as .csv files. Currently
am opening each spreadhseet by hand and using "Save As" to convert to
CSV file. Not bad with 1 or 2, but I project a rapid increase in th
number of files I'll be getting.

Is there a way use vbscript to do this? I would prefer to not have t
use WSH to open Excel first but if I have to...

I -had- been opening each .xls and defining a range on each workshee
(only one thankfully) but this too seemed idiotic and definitely no
scalable. By using a CSV I am able to keep my code fairly simple and
hopefully, easier to maintain.

Thanks!
 
How about VBA instead?

Create a new workbook that's used just to run this macro.

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim myFileNames As Variant
Dim nextWkbk As Workbook
Dim wks As Worksheet
Dim fCtr As Long

myFileNames = Application.GetOpenFilename _
(FileFilter:="Excel files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) Then
Application.ScreenUpdating = False
For fCtr = LBound(myFileNames) To UBound(myFileNames)

Set nextWkbk = Nothing
On Error Resume Next
Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr), _
ReadOnly:=True, UpdateLinks:=0)
On Error GoTo 0
If nextWkbk Is Nothing Then
MsgBox "Error with: " & myFileNames(fCtr)
Else
For Each wks In nextWkbk.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:\temp\" & wks.Name _
& Format(Time, "_hhmmss"), FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks
nextWkbk.Close savechanges:=False
End If
Next fCtr
Else
MsgBox "try again later!"
End If

Application.ScreenUpdating = True

End Sub

When prompted, you can click on the first and ctrl-click on subsequent (or even
shift-click to get the files in between).

I didn't know where to put the .csv files, so I stuck them in c:\temp with this
line:

"C:\temp\" & wks.Name _
& Format(Time, "_hhmmss"), FileFormat:=xlCSV

I also included the time at the end--just in case there would have been files
that would have had the same name.

If your pc is faster than mine (and you get prompts asking to overwrite the
file), add this line (right after the line shown):

For fCtr = LBound(myFileNames) To UBound(myFileNames)
Application.Wait Now + TimeSerial(0, 0, 1)

The wait line will pause for a second and continue.

And all the names will be unique.
 
Thanks for the reply and the code. I don't use VBA much. I only use i
for stripping the excel attachments off emails in Outlook and am not
fast enough learner to implement that solution. it is purty cod
though!

I'm pretty sure I can do what I need to in script.

I can use Server.CreateObject("Excel.Application" ) to get at Excel'
methods and properties.

I tried using

-CSVFileName = strippedname & ".csv"
ExcelWorkbook.SaveAs CSVFileName -

but this doesn't save it as a csv I can open in Notepad. It's not
true text file since there's Microsoft formatting junk whenI open it.

I then tried:

- ExcelWorkbook.FileFormat=xlCSV
ExcelWorkbook.SaveAs CSVFileName -

but that yielded the same results.

I know when I 'save as' in human interaction mode :) I have to selec
csv as the file type value. I think if I could get that done utilizin
my current method I'd have it done.

Any ideas folks? Any links to listings of Excel's scripting DOM?

Cheers and Thanks: Ric
 
Here is the solution to my probelm:

xlCSV = 6

ExcelBook.SaveAs CSVName,xlCSV

I was passing a constant, xlCSV, that was meaningless. I was also no
passing it to the SaveAs method. I was just loading the paramete
"FileFormat" and then not feeding that to the SaveAs Method.

Now I open a real csv.

Oh happy day!

:
 
Back
Top