Save file as CSV from XLS after VBA applied

Hi,

I have a set of VBA code that opens my template.xls file and moves specific data over from the file I open which is also an XLS file.

When I run the below script it saves each file as a CSV which is what I want but it is not a true CSV file as when I open it in notepad the format is that of an excel file.

The test.xls contains the below VBA and I have also attached the Template.xls

Here is my current VBA below:

Sub SaveCSVFiles()
'
'
'

    Cells.Select
    Cells.EntireColumn.AutoFit
   
   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
   
   Set rng = ActiveSheet.Range("B2")
   
   Do Until rng = ""
      On Error Resume Next
      Set wb = Workbooks.Open(sTemplate)
      
      With wb.Worksheets(1)
         .Range("A1").Offset(NextRow).Value = rng.Offset(, -1).Value    ' SHIP_TO_CODE
         .Range("B1").Offset(NextRow).Value = rng.Offset(, 0).Value     ' CUSTOMERPO
         .Range("C1").Offset(NextRow).Value = rng.Offset(, 1).Value     ' NUMBOXES
         .Range("D1").Offset(NextRow).Value = rng.Offset(, 2).Value     ' WEIGHT
         .Range("E1").Offset(NextRow).Value = rng.Offset(, 3).Value     ' USER_DEFINED1 - SCAC CODE
         .Range("F1").Offset(NextRow).Value = rng.Offset(, 4).Value     ' SHIPPING_METHOD
         .Range("G1").Offset(NextRow).Value = rng.Offset(, 5).Value     ' DATE_SHIPPED
         .Range("H1").Offset(NextRow).Value = rng.Offset(, 6).Value     ' SHIP_TO_NAME
         .Range("I1").Offset(NextRow).Value = rng.Offset(, 7).Value     ' SHIP_TO_ADDR1
         .Range("J1").Offset(NextRow).Value = rng.Offset(, 8).Value     ' SHIP_TO_ADDR2
         .Range("K1").Offset(NextRow).Value = rng.Offset(, 9).Value     ' SHIP_TO_CITY
         .Range("L1").Offset(NextRow).Value = rng.Offset(, 10).Value    ' SHIP_TO_STATE
         .Range("M1").Offset(NextRow).Value = rng.Offset(, 11).Value    ' SHIP_TO_ZIP
         .Range("N1").Offset(NextRow).Value = rng.Offset(, 11).Value    ' SHIP_TO_COUNTRY
         .Range("O1").Offset(NextRow).Value = rng.Offset(, 13).Value    ' ORDER_DATE
         .Range("P1").Offset(NextRow).Value = rng.Offset(, 14).Value    ' TRACKINGNO
         .Range("Q1").Offset(NextRow).Value = rng.Offset(, 15).Value    ' PRODUCTID
         .Range("R1").Offset(NextRow).Value = rng.Offset(, 16).Value    ' ENDCUSTOMERPRODUCTID
      End With
      
        Set rng = rng.Offset(1, 0)
      
        'Multi-Item loop
      With wb.Worksheets(1)
        NextRow = 1
        Do Until rng.Offset(-1, 0) <> rng.Value
         .Range("A1").Offset(NextRow).Value = rng.Offset(, -1).Value    ' SHIP_TO_CODE
         .Range("B1").Offset(NextRow).Value = rng.Offset(, 0).Value     ' CUSTOMERPO
         .Range("C1").Offset(NextRow).Value = rng.Offset(, 1).Value     ' NUMBOXES
         .Range("D1").Offset(NextRow).Value = rng.Offset(, 2).Value     ' WEIGHT
         .Range("E1").Offset(NextRow).Value = rng.Offset(, 3).Value     ' USER_DEFINED1 - SCAC CODE
         .Range("F1").Offset(NextRow).Value = rng.Offset(, 4).Value     ' SHIPPING_METHOD
         .Range("G1").Offset(NextRow).Value = rng.Offset(, 5).Value     ' DATE_SHIPPED
         .Range("H1").Offset(NextRow).Value = rng.Offset(, 6).Value     ' SHIP_TO_NAME
         .Range("I1").Offset(NextRow).Value = rng.Offset(, 7).Value     ' SHIP_TO_ADDR1
         .Range("J1").Offset(NextRow).Value = rng.Offset(, 8).Value     ' SHIP_TO_ADDR2
         .Range("K1").Offset(NextRow).Value = rng.Offset(, 9).Value     ' SHIP_TO_CITY
         .Range("L1").Offset(NextRow).Value = rng.Offset(, 10).Value    ' SHIP_TO_STATE
         .Range("M1").Offset(NextRow).Value = rng.Offset(, 11).Value    ' SHIP_TO_ZIP
         .Range("N1").Offset(NextRow).Value = rng.Offset(, 11).Value    ' SHIP_TO_COUNTRY
         .Range("O1").Offset(NextRow).Value = rng.Offset(, 13).Value    ' ORDER_DATE
         .Range("P1").Offset(NextRow).Value = rng.Offset(, 14).Value    ' TRACKINGNO
         .Range("Q1").Offset(NextRow).Value = rng.Offset(, 15).Value    ' PRODUCTID
         .Range("R1").Offset(NextRow).Value = rng.Offset(, 16).Value    ' ENDCUSTOMERPRODUCTID
              Set rng = rng.Offset(1, 0)
            NextRow = NextRow + 1
        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.Close Savechanges:=True, Filename:=sFilename
   
   Loop
      
   'Reset variables
   Set rng = Nothing
   Set wb = Nothing
      
End Sub

Thanks for any help,

David

to save as csv you have to use saveas, not close Savechanges

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