Save file as CSV from XLS after VBA applied

Hi, David,

maybe you can save a lot of time when you just open the template once and clear teh contents isnstead of closing the saved file and opening the template again.

Maybe give this code a try:

Sub SaveCSVFiles()
'
'
'


Dim wb As Workbook
Dim rng As Range
Dim sTemplate As String
Dim sFilename As String
Dim NextRow As Long

sTemplate = "C:\Documents and Settings\user\My Documents\MPEDIOrders\CFXEDIExport\Temp\Template.xls"
If Dir(sTemplate) = "" Then
   MsgBox "Template does not exist!"
   Exit Sub
End If

Application.WindowState = xlMinimized
Cells.EntireColumn.AutoFit

Set wb = Workbooks.Open(sTemplate)
Set rng = ThisWorkbook.ActiveSheet.Range("B2")

Do Until rng = ""
   
  'Multi-Item loop
  With wb.Worksheets(1)
    .Range("A1").Offset(NextRow, 0).Resize(1, 18).Value = rng.Offset(, -1).Resize(1, 18).Value    ' SHIP_TO_CODE
    Set rng = rng.Offset(1, 0)
    Do Until rng.Offset(-1, 0) <> rng.Value
      NextRow = NextRow + 1
      .Range("A1").Offset(NextRow, 0).Resize(1, 18).Value = rng.Offset(, -1).Resize(1, 18).Value
      Set rng = rng.Offset(1, 0)
    Loop
  End With
  NextRow = 0
  'Save the filename as order number
  cName = Range("A1")
  sFilename = "C:\Documents and Settings\user\My Documents\MPEDIOrders\CFXEDIExport\Ready\" & cName & "-" & rng.Offset(-1).Value & ".csv"
  wb.SaveAs Filename:=sFilename, FileFormat:=xlCSV
  wb.ActiveSheet.UsedRange.ClearContents
Loop

wb.Close False

'Reset variables
Set rng = Nothing
Set wb = Nothing

Application.WindowState = xlMaximized
   
End Sub

Iīm afraid I donīt understand

It just the formatting that I need to save as.

No formatting is transferred when you copy values directly. Or do you mean the suffix?

Ciao,
Holger