Excel VBA save to CVS file

Question

I have an excel file which has a macro which saves the file as a .CSV file. I have a program that calls on the CSV file but the program only recognizes the .CSV file if I open it manually and save it. Then after doing so, the program recognizes the file and data.

I've used Google Sheets for years and just download to CSV and this has worked. I wanted to automate this process using Excel but seem to have found some weird issue.

The macro runs and saves the Excel Sheet to the CSV file and when opening it everything in the file is correct. Again the program calling the file will only recognize it if I manually open it and physically save it.

I can't figure out the issue. Does any have any input it would be greatly appreciated. Below is the macro I'm using.

Public Sub ExportWorksheetAndSaveAsCSV()

    Dim wbkExport As Workbook
    Dim shtToExport As Worksheet

    Set shtToExport = ThisWorkbook.Worksheets("nqdev")     'Sheet to export as CSV
    Set wbkExport = Application.Workbooks.Add
    shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
    Application.DisplayAlerts = False                       'Possibly overwrite without asking
    wbkExport.SaveAs FileName:="H:\My Drive\KML\TOTEliteMembers\NQDev.csv", FileFormat:=xlCSV
    Application.DisplayAlerts = True
    wbkExport.Close SaveChanges:=True

End Sub

Answer 1

I always use this code when saving a file, works wonders and you can change the delimiter to whatever you desire to use.

Importante note: you need to change the Range to the one you're using, then change the "delimiter = |" just use ; for csv, and the Path in the Open.. line

Dim r As Range: Set r = Range("A1:P15") 
Dim buffer As String, delimiter As String, c As Range
Dim i As Long

For Each c In r
    If (i Mod r.Columns.Count) = 0 Then
        If (Len(buffer)) Then delimiter = vbCrLf
    Else
        delimiter = |
    End If
    buffer = buffer & delimiter & """" & CStr(c.Text) & """" '// c.value or c.text to preserve formatting
    i = (i + 1)
Next

Open "D:\YourFolder\Yourname.csv" For Output As #1
Print #1, buffer
Close #1