title: Using VBA to create a CSV from an Excel sheet, ignoring semicolons
body: <p>I found code somewhere, and tuned to suit my needs.</p>
  <p>Some fields have semicolons, and when I open a freshly generated CSV file in Excel, anything after a semicolon is automatically placed in a new column. That's even before I transform the data. I tried wrapping the semicolons in double quotes, single quotes, quadruple quotes.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub CSV_contact()
  
      Dim myCSVFileName As String
      Dim myWB As Workbook
      Dim rngToSave As Range
      Dim fNum As Integer
      Dim csvVal As String
      Dim lastrow As Long
  
      With Sheets(&quot;Contactpersonen&quot;).UsedRange
          lastrow = .Rows(.Rows.Count).Row
      End With
  
      Set myWB = ThisWorkbook
      myCSVFileName = Sheets(&quot;Instellingen&quot;).Range(&quot;B4&quot;).Value &amp; &quot;\&quot; &amp; &quot;CSV_contactpersonen&quot; &amp; VBA.Format(VBA.Now, &quot;dd-MMM-yyyy hh-mm&quot;) &amp; &quot;.csv&quot;
      csvVal = &quot;&quot;
      fNum = FreeFile
      Set rngToSave = Range(&quot;A10:O&quot; &amp; lastrow)
  
      Open myCSVFileName For Output As #fNum
  
      For i = 1 To rngToSave.Rows.Count
          For j = 1 To rngToSave.Columns.Count
              csvVal = csvVal &amp; Chr(34) &amp; Replace(rngToSave(i, j).Value, &quot;;&quot;, Chr(34) &amp; Chr(34) &amp; &quot;;&quot; &amp; Chr(34) &amp; Chr(34)) &amp; Chr(34) &amp; &quot;,&quot;
              Debug.Print csvVal
          Next
          Print #fNum, Left(csvVal, Len(csvVal) - 2)
          csvVal = &quot;&quot;
      Next
  
      Close #fNum
  End Sub
  </code></pre>
  <p>When I use debug print, this is what a typical line looks like:</p>
  <pre><code>&quot;normal string 1&quot;,&quot;normal string 2&quot;,&quot;string containing&quot;&quot;;&quot;&quot; somewhere&quot;&quot;;&quot;&quot; in the middle&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;another normal string after some empty columns&quot;,
  </code></pre>
  <p>Mind that this is one of the things I tried, the first thing I tried created lines where the semicolon strings looked like this:</p>
  <pre><code>&quot;string containing; somewhere; in the middle&quot;
  </code></pre>
  <p>Is there an escape character, like / or *, I can put in front of it?</p>
  <p>I read about having to write my own CSV parser, but that's beyond my skill level.</p>
  <p>My client uses European Excel, so the comma is the standard decimal delimiter. Maybe the solution would be to (temporarily) set the delimiter to a point? I prefer it to be temporary as my client is an accountant and values correct layout.</p>