PDA

View Full Version : click save csv files



mikeo1313
08-23-2010, 12:20 PM
I'm using this code to batch run a macro to a folder of files.

I have no problem with saving xlsx files, but when I run this macro on a folder of csv files I have to click save every time. Is there a way to suppress the save messages or just some vba to batch convert csv's to xlsx's?

Sub runrepl()

Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "C:\ex\*.*"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop
Set r = Range("A1")
While r.Value <> ""
Workbooks.Open Filename:="C:\ex\" & r.Value
Call formheader
ActiveWorkbook.Save
ActiveWorkbook.Close
Set r = r.Offset(1, 0)
Wend
End Sub

Ken Puls
08-23-2010, 10:16 PM
Hi there,

I haven't tested this at all, but you could try just disabling the alerts:

Application.DisplayAlerts = False

Just remember to turn it on afterwards.

If that doesn't solve it, then you may want to test the file extension and do a SaveAs command on it to save it either to CSV or XLSX (whatever your preference)

ActiveWorkbook.SaveAs Filename:="C:\ex\" & r.Value, FileFormat:=xlCSV
or
ActiveWorkbook.SaveAs Filename:="C:\ex\" & r.Value, FileFormat:=xlOpenXMLWorkbook