.CSV format issue with VBA Macro - Auto-Saves without Columns kept

[JonTTrier]

Hey All Thanks for your help in advance,

My Issue:

This code works fine but my main question/issue is the difference to when you manually save a .CSV (MS-DOS) File you are prompted with the following message " .csv may contain features that are not compatible with CSV (MS-DOS). Do you want to keep the workbook in this Format?" When you click Yes the .csv file is saved and all of the columns are kept. When you save automatically with the macro below, it saves all to Column A with the Comma demilited style. I'm wondering how to keep all of the information in the columns in VBA, as it saves when you Manually save it and go through the Prompt Message. I have been stumped on this for weeks and would GREATLY appreciate anyone's advice.


Code:
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\jonathan.trier\Desktop\Destination Folder Adobe\" & "Adobe_CLC 5.0_North America_USD_" & Format(Date1, "YYYY_MM") & " CSV" & ".csv", FileFormat:= _
xlCSVMSDOS, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False


Thanks all,

Jon

 

[chucklesl066]

Hey All Thanks for your help in advance,

My Issue:

This code works fine but my main question/issue is the difference to when you manually save a .CSV (MS-DOS) File you are prompted with the following message " .csv may contain features that are not compatible with CSV (MS-DOS). Do you want to keep the workbook in this Format?" When you click Yes the .csv file is saved and all of the columns are kept. When you save automatically with the macro below, it saves all to Column A with the Comma demilited style. I'm wondering how to keep all of the information in the columns in VBA, as it saves when you Manually save it and go through the Prompt Message. I have been stumped on this for weeks and would GREATLY appreciate anyone's advice.


Code:
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\jonathan.trier\Desktop\Destination Folder Adobe\" & "Adobe_CLC 5.0_North America_USD_" & Format(Date1, "YYYY_MM") & " CSV" & ".csv", FileFormat:= _
xlCSVMSDOS, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False


Thanks all,

Jon

 
Sub SaveToUsersDesktopAsCSVandAutoCloseFileWithNoWarnings()
Dim DTAddress As String
Dim FileName As String
Dim FullyQualifiedFileName As String
'
'get the path to the User's Desktop
'
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
'
'build the Workbook name to save as CSV format
'
FileName = ActiveWorkbook.Name
FileName = Left(FileName, InStr(FileName, ".")) & "csv"
'
'build the fully-qualified workbook "save" path
'
FullyQualifiedFileName = DTAddress & FileName
'
'switch off all application alerts before saving (in case the file already exists etc)
'
Application.DisplayAlerts = False
'
'save the current active sheet as a CSV file to the user's Desktop
'
ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlCSV
'
'pop-up an info dialogue box to confirm where the file has been saved to
'
Answer = MsgBox(FileName & Chr(13) & Chr(13) & "Click OK To Close This Workbook", _
vbOKOnly + vbInformation, "This File Has Been Saved to Your Desktop")
'
'close the original workbook without saving changes
'
ThisWorkbook.Close savechanges:=False
'
'switch all application alerts back on before exiting
'
Application.DisplayAlerts = True
End Sub

 

[JonTTrier]

Sub SaveToUsersDesktopAsCSVandAutoCloseFileWithNoWarnings()
Dim DTAddress As String
Dim FileName As String
Dim FullyQualifiedFileName As String
'
'get the path to the User's Desktop
'
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
'
'build the Workbook name to save as CSV format
'
FileName = ActiveWorkbook.Name
FileName = Left(FileName, InStr(FileName, ".")) & "csv"
'
'build the fully-qualified workbook "save" path
'
FullyQualifiedFileName = DTAddress & FileName
'
'switch off all application alerts before saving (in case the file already exists etc)
'
Application.DisplayAlerts = False
'
'save the current active sheet as a CSV file to the user's Desktop
'
ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlCSV
'
'pop-up an info dialogue box to confirm where the file has been saved to
'
Answer = MsgBox(FileName & Chr(13) & Chr(13) & "Click OK To Close This Workbook", _
vbOKOnly + vbInformation, "This File Has Been Saved to Your Desktop")
'
'close the original workbook without saving changes
'
ThisWorkbook.Close savechanges:=False
'
'switch all application alerts back on before exiting
'
Application.DisplayAlerts = True
End Sub

Thank you for your Quick Response:


However the file, once saved as a .CSV does not retain the columns. They are all saved into column A with Comma Delimeters. I'm looking to Auto save as a .csv file once a button it clicked with all of the data still in the appropiate columns. We import .CSV files into our ERP System using a Template which reads the specific columns in the excel .CSV file, so each column has to in the exact location. When you save manually as a .CSV (MS-DOS) file and go through the prompted messages clicking "Yes" It saves with all the data still in thier respected columns. When Saved Automatically with my Macro code above, it does not have to go through the prompt and all of the data is saved in Column A with the comma delimeters.

 

[Joe4]

When you save automatically with the macro below, it saves all to Column A with the Comma demilited style.

The above statement makes no sense to me.

When you save a file as a CSV file, it is no longer an Excel file, it is a CSV. A CSV is a text file. "Column A" is an Excel term, not a text file term. A text file really has no columns at all, it has delimiters that when imported into another program can be used to split the record into multiple fields.

So if you were to re-open the CSV file in Excel, it should split the data among multiple columns (assuming you had multiple columns to begin with and you open it correctly in Excel). To see this, close the file after you save it, browse to it from Windows Explorer, and select "Open With Excel".

 

[Joe4]

Lest I assume too much about your data, I should mention the following.

I am assuming that your original data in Excel (before saving as a CSV) is starting out in multiple columns. If you start out with everything crammed into column A, with commas in between each thing you want separated, Excel is going to read that as one long text string, and put text qualifers around the whole thing to treat it as a single field.

So you will only end up with multiple columns on the back-end if you originally had your data in multiple columns (before trying to save as CSV). Otherwise, (if everything is starting out in column A), saving as a Space Delimited (prn) file may be what you want (and just give it a CSV extension), as that should export it exactly as you see it and not add any text qualifiers.