CODEWITHSUNDEEP
excelvba
JudgeCx
JudgeCx

Reputation: 15

Characters in CSV Save File

I am writing this due to the fact that I am unable to get my Excel data transposed onto a separate CSV file without it formatting to multiple special characters. This macro was intended to pull everything required for an upload and save the file as a CSV so the user wouldn't need to bother.

Please see trouble code below. Portions of code are missing from post, however this is the troubled bit.

'''

Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.NumberFormat = "mm/dd/yy;@"
Columns("H:H").Select
Selection.NumberFormat = "0"
Columns.AutoFit
Range("A1").Select

MsgBox "Save as CSV to Client Upload File | Example : HBSFULDT", vbInformation, "Fuel Transfer Complete"
cFile = Application.GetSaveAsFilename(InitialFileName:="XXXFULDT", _
fileFilter:="CSV Files (*.csv), *.csv", _
Title:="Save as FT Client Upload File")

If cFile <> False Then
uploadSheet.SaveAs Filename:=cFile
Else
MsgBox "Please Save Fuel Upload to Client XXXFULDT File", vbCritical 'If Cancel is pushed on SaveAs Screen
Exit Sub
End If
ActiveWindow.Close

'''

Example of Characters shown in notepad - E÷HüCä-Jܲ@5í‚ÇQ>Àēƪc[žiiÿž‰ûB¡j7±ÏÜ{2ñÍh²nm¶‚ˆÆ»R‹ÈÀU^7/ÅÇì%¿’rZYï @1__f› ˜q·ÃR4DáAJ¬h>€ãÚÇVßƹªZ¨9ÈÛÁàNVÞ8Ê©ÓãÑÔji){^óã-I‹"{Üv^¥P!XS)bR¹rú—K¾s(¸3Õ`cÞ0†½ÝÎß»¾7M4²©ŠôªZÆk+¿|\|z¿(Ž‹ôPúº6h_-[ž@!‚ÒØ Pk‹´­2nÏ}Ä?£LËð Ýû%áÄßdºždN"m,à¥ÇžDO97‚~§Èɸ8ÀOíc|n¦Ñä

Upvotes: 0

Views: 87

Answers (1)

tgolisch
tgolisch

Reputation: 6744

The line of code that says uploadSheet.SaveAs, doesn't specify a FileType. The default will be xls or xlsx, regardless of the filename.

eg. if you save an excel file as MyDemo.csv, it will still be a xls. Excel will figure it out when it opens the file, without complaining about the name mismatch

This code should fix it

'replace this line
uploadSheet.SaveAs Filename:=cFile

'with this line
uploadSheet.SaveAs Filename:=cFile, FileFormat:=xlCSV 'or 6, which means CSV format

For info, and other settings, check out the SaveAs docs: https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

Upvotes: 1

Related Questions