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

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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.:

Code:
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.
 
Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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:

Code:
ActiveWorkbook.SaveAs FileName:=myPath & myFileName & ".csv", FileFormat:=xlCSV, Local:=True
 
Upvote 0
Can also avoid fiddling with the control panel settings each time. Just do it like this:

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Code:
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

:)
 
Upvote 0
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
VBA Code:
ActiveWorkbook.Close False
when closing your workbooks, that should do the trick.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top