How to use VBA to save a pipe delimited CSV file - Works when I manually SaveAs but not when I use code

[Joyner]

Hello, and thank you for the help. I have searched everywhere and cannot find the answer why this is not working.

I want to save Excel files in a pipe delimited CSV format. In my code, I have taken the steps that work when I do the same thing manually, but the code doesn’t work.

Manually saving, I do these steps:

In the Control Panel settings I changed the default Comma List Separator to a Pipe. I open an Excel file, do SaveAs: CSV (Comma delimited) (*csv), I get the standard dialog box warning:

filename.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format?

I answer Yes – I open the saved CSV file in notepad and the Pipe delimiters are there.

If I use code that completes the same steps (I believe), the pipe delimiter is not there, the commas are.

I use this code, that appears to be the same as the recorded steps that do work. Note myPath and myFileName are previously defined and work, the save routine works, but it only saves the comma delimiter.:

Application.DisplayAlerts = False
    
    'Save as a csv file for import

        ActiveWorkbook.SaveAs FileName:=myPath & myFileName & ".csv",  ‘FileFormat:=xlCSV
   
    Application.DisplayAlerts = True

My understanding is when using Application.DisplayAlerts = False, it answers the dialog box as Yes.

So it appears that the code is doing the exact same thing as when the steps are completed manually, but the code doesn’t work the same.

Am I missing something.

Any help would be greatly appreciated.

Thank you.

 

[Joyner]

Okay - Solved.

After many more searches I found the solution. It's interesting that many people seem to have posted a similar question with no answer, or incorrect answers but did find it and it is working.

Need to add the Local parameter to the SaveAs for the code to use the local setting on your computer. So the code that works is:

ActiveWorkbook.SaveAs FileName:=myPath & myFileName & ".csv", FileFormat:=xlCSV, Local:=True

 

[ParamRay]

Can also avoid fiddling with the control panel settings each time. Just do it like this:

Public Sub CreatePipeDelimitedTextFile(ByVal strSavePath As String, _
                                       ByVal strSaveName As String)
  
' Creates a pipe-delimited text file out of the region of
' cells surrounding A1 on the active worksheet. Saves the
' file using the folder and filename specified as arguments.
  
  Const strPROC_NAME = "Create Pipe-Delimited Text File"
  Dim intFileNum As Integer
  Dim intLastCol As Integer
  Dim intColNum As Integer
  Dim lngLastRow As Long
  Dim lngRowNum As Long
  
  On Error GoTo ErrHandler
  
' Check active sheet is a worksheet
  If Not TypeOf ActiveSheet Is Worksheet Then
    MsgBox "You must activate a worksheet first.", _
            vbExclamation, strPROC_NAME
    GoTo TidyUp
  End If
  
' Add path separator if missing
  If Right(strSavePath, 1) <> "\" Then
    strSavePath = strSavePath & "\"
  End If

' Determine region of cells around A1
  intLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  
' Write the cells to the file, one by one
  intFileNum = FreeFile()
  Open (strSavePath & strSaveName) For Output As intFileNum
  For lngRowNum = 1 To lngLastRow
    For intColNum = 1 To intLastCol
      If intColNum <> intLastCol Then
        Print #intFileNum, Cells(lngRowNum, intColNum).Value & "|";
      Else
        Print #intFileNum, Cells(lngRowNum, intColNum).Value & vbCrLf;
      End If
    Next intColNum
  Next lngRowNum
  Close intFileNum

' Notify user it is finished
  MsgBox "File created as:" & vbCrLf & strSavePath & _
          strSaveName, vbInformation, strPROC_NAME
TidyUp:
  On Error Resume Next
  Close intFileNum
  Exit Sub
  
ErrHandler:
  MsgBox "Error " & Err.Number & ":" & vbCrLf & _
          Err.Description, vbCritical, strPROC_NAME
  Resume TidyUp
End Sub

 

[ParamRay]

Actually, probably better to print the cells' .Text property rather than .Value

 

[Joyner]

Thanks, I'll give it a try.

I did try some similar code I found and it seems to take a long time on the large files I have and then it got hung up, but I'll give it a test.

Thaks

 

[Joyner]

Okay - Solved.


Need to add the Local parameter to the SaveAs for the code to use the local setting on your computer. So the code that works is:

Still, why it don't work (use the local setting by default) seems very strange since it works properly when you go through the steps manually. So when done manually it uses the local setting, but in code it doesn't.

 

[DonChino]

Okay - Solved.
After many more searches I found the solution. It's interesting that many people seem to have posted a similar question with no answer, or incorrect answers but did find it and it is working.
Need to add the Local parameter to the SaveAs for the code to use the local setting on your computer. So the code that works is:

ActiveWorkbook.SaveAs FileName:=myPath & myFileName & ".csv", FileFormat:=xlCSV, Local:=True

You The Man, Joyner...

Thanks, you saved me from another FULL DAY of wasted work and pulling out my hair; because EVERYONE online talks about setting REGIONAL SETTINGS under CONTROL PANEL to change the LIST DELIMITER, but VBA code does NOT respect this. It works through the UI; but any code will ignore it, so you have to manually set this argument and now my VBA code works perfect.

My complete comment here on SuperUser:
https://superuser.com/questions/1189980/vba-outlook-save-attachments-in-csv-format/1273590#1273590

 

[LeCanalla]

Still, why it don't work (use the local setting by default) seems very strange since it works properly when you go through the steps manually. So when done manually it uses the local setting, but in code it doesn't.

I know this is an old post but I found myself in the same situation, the solution is pretty simple. The error consists that some of us (probably not the best practice) save the workbook again when closing, this somehow overwrites the "Local" instruction. All you need to do is use

ActiveWorkbook.Close False

when closing your workbooks, that should do the trick.

Hope this helps.