Save xls file as csv, by using VBA

[blacornelis]

Hi,

Im working on a VBA project and got stuck on the last part… saving files as CSV.
My VBA macro extracts data and puts it in an Excel sheets.
This newly generated Excel sheets need to be saved as CSV Files.
My code works… but when i open my CSV files they differ rather differ a lot from what i expected them to look.
All the values of a row are merged into the same column.
Does anyone know how to solve this problem?

The part of my code where I’m saving my files as CSV is paste below:
(need to mention I added an If function because the name need to be dependend of the value of cell C9, but this isn’t the problem, work fine, and from my point of vieuw shouldn’t affect the file itself)

I gues it has something to do with te pre-set of de delimitor and the sing used to define the decimals being the same? But I’m not sure, and would not know how to fix this.

ActiveWorkbook.Sheets("WegschrijfSheet").Range("C9").Select
    If ((ActiveCell.Value Mod 2) = 0) Then
        ActiveWorkbook.SaveAs FileName:="Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Wegschrijflocatie\4022 631 79951#14#" & Sheet1.Range("B1").Value & ".csv", Fileformat:=xlCSV, CreateBackup:=False
            Else
                ActiveWorkbook.SaveAs FileName:="Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Wegschrijflocatie\4022 631 79951#14#" & Sheet1.Range("B1").Value & "_1" & ".csv", Fileformat:=xlCSV, CreateBackup:=False
    End If

Pardon me for my English grammar, my native language is Dutch.

Thanks in advance,

Greets Cornelis

[michaelengel3777 (M.Engel)]

I had a problem that was similar, everytime i saved the file the values weren’t correct. What helped was a refresh of the data in the sheet.

You could put that infront of your function and see if it helps:

ActiveWorkbook.RefreshAll
Application.CalculateFull
Application.CalculateFullRebuild

[Mike400]

Put a break point on the VBA code (F9 if I remember correctly will insert a break point) at the If Statement. When your code hits this statement flip over to your spreadsheet. Ensure everything is in the correct cells. If it is, use the native Excel Save As menu option to see what Excel itself does.

If Excel works properly, then start a macro recording while you do a Save As to CSV. Stop the macro after you finish the Save As and then open that macro in the VBA editor. Use the syntax in that macro’s code as an example for your code.

[michaelengel3777 (M.Engel)]

How do you open your csv-file?
Have you tried it like that?

Then you can select in which style the CSV was created and you can select the seperator. In your case ‘,’

In the last picture you can select the seperator, in my case ‘;’

[blacornelis]

First of all, both thanks for your replies, I’m going to try them.

@M.Engel
I’m entierly using VBA. (the VB code loops to create about 600 files)
SaveAs FileName.csv FileFormat := xlCSV Create Backup:=False
Like the code I paste above in my question.

My VB macro is not opening files, it is creating new excel files, exports data into it, saves it as CSV file, colses the file and goes on with creating the next file

I’ll past my full code below in case that may clear it up?

Sub Collect()
Dim objFSO As Object
Dim objfolder As Object
Dim objFile As Object
Dim i As Integer
Dim sBron As String
Dim kWegschrijf As String

Application.ScreenUpdating = False
'voorkomt knipperen van scherm

sBron = Sheet1.Range("L4")
sWegschrijf = Sheet1.Range("L5")

Sheets("FileListSheet").Select
Sheets("FileListSheet").Range("A2").Select

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objfolder = objFSO.GetFolder(sBron)
'Get the folder odject

i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objfolder.Files
    'print file name
    Cells(i + 1, 1) = objFile.name
    'print file path
    Cells(i + 1, 2) = objFile.path
    i = i + 1
Next objFile

Dim a As Integer
Dim b As String
Dim c As Integer

c = 0

Do Until c = 1
   ' If b = "stop" Then
    '    c = 1
    ' Else: c = 0

a = 0

Sheet2.Range("A2").Select
Range("Z100") = ""

    
    Do Until a = 1
        If ActiveCell.Value = "Stop" Then
            a = 1
        
        ElseIf IsEmpty(ActiveCell) Then
            a = 0
            ActiveCell.Offset(1, 0).Select
        Else: a = 1
                
        End If
        
    
    Loop
    
    ActiveCell.Select
    b = ActiveCell.Value
    ActiveCell.Value = ""
    
    'MsgBox (b)
    
    Sheet2.Range("Z100") = (b)
        
    
    'MsgBox ("S:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Testbestanden\" & Range("Z100").Value)
    'myFile = "S:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Testbestanden\" & Range("Z100").Value
    'MsgBox ("S:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Testbestanden\" & Range("Z100").Value)

    
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Workbooks.Open FileName:=sBron & "\" & Sheet2.Range("Z100").Value, UpdateLinks:=False
    Sheets("VDL ETG Almelo IMS").Select
    Sheets("VDL ETG Almelo IMS").Copy Before:=Workbooks("Oude Excel bestanden importeren.xlsm").Sheets(3)
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    
    'Het bovenste Excel bestand uit de lijst copiëren uit het meetrapport en plakken het macro excel bestand
       
'_____________________________________________________________________________________
Between this a lot of lines copying values... (which works and don't affect the rest of the code) I cut it out to make some space
    
'_____________________________________________________________________________________

    Sheet1.Select
           
    Dim wb As Workbook
    Set wb = Workbooks.Add
      
    If Sheet2.Range("Z100") = "stop" Then GoTo Einde
    
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("WegschrijfSheet").Copy Before:=wb.Sheets(1)
    'kopieerd de wegschrijfSheet naar een nieuw Workbook
      
    ActiveWorkbook.Sheets("WegschrijfSheet").Range("C9").Select
    If ((ActiveCell.Value Mod 2) = 0) Then
        ActiveWorkbook.SaveAs FileName:="Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Wegschrijflocatie\4022 631 79951#14#" & Sheet1.Range("B1").Value & ".csv", Fileformat:=xlCSV, CreateBackup:=False
            Else
                ActiveWorkbook.SaveAs FileName:="Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Wegschrijflocatie\4022 631 79951#14#" & Sheet1.Range("B1").Value & "_1" & ".csv", Fileformat:=xlCSV, CreateBackup:=False
    End If
        
    'Opslaan van de sheet als nieuw document onder de naam van het product nummer
    ActiveWorkbook.Close
    
    Workbooks("Oude Excel bestanden importeren").Worksheets("VDL ETG Almelo IMS").Activate
    ActiveWorkbook.Sheets("VDL ETG Almelo IMS").Select
    ActiveWindow.SelectedSheets.Delete
    
    Windows(b).Activate
    ActiveWindow.Close
    Sheets("FileListSheet").Select
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    
Loop
    
Einde:
   If Range("Z100") = "stop" Then c = 1

Application.ScreenUpdating = True
'Zet scherm updaten weer aan

End Sub

Greets Cornelis

[blacornelis]

I have one main Excel file to proces other excel files.
The main File opens a excel file (.xls or somthing like that)) and copy-past a sheet into the main File.
The old file is closed
Data from the sheet that has been copyed to the main file is copy-past to “WegschrijfSheet”(also in the main Excel file.)
I do this to put measure values in a layout I need for the software my company uses to analyze the data.
When the values are all set in place "WegschrijfSheet"is being saved as new File (and converted to CSV).
This last step is where it goes wrong and my layout changes.

Greets Cornelis

[michaelengel3777 (M.Engel)]

@blacornelis

When the values are all set in place "WegschrijfSheet"is being saved as new File (and converted to CSV).
This last step is where it goes wrong and my layout changes.

Could it be that the seperator is wrong? Maybe ‘,’ is not write if you want it to look like that:

A1 = “Productieorder”

A2 = “Artikelnummer”

B1 = “228767”

B2 = “4022 631 79951”

http://stackoverflow.com/questions/13496686/how-to-save-semi-colon-delimited-csv-file-using-vba

Specifying a Delimiter when Saving a CSV File in a Macro (Microsoft Excel)

[michaelengel3777 (M.Engel)]

I guess that for excel there is no difference in the values: Productieorder and 228767 since they both are seperated by a ‘,’ comma. It doesn’t know where to seperate the files.

[michaelengel3777 (M.Engel)]

Could you post a picture in the way you want it to be?

[blacornelis]

All the values in Column A and B are important.
(values from C D E ect. are not variable will not be used)
the red collor is not needed but will disapear automaticly when converting to CSV i gues?

[michaelengel3777 (M.Engel)]

@blacornelis

the red collor is not needed but will disapear automaticly when converting to CSV i gues?

Yes, CSV is just plain text. You can open it with a texteditor.

[blacornelis]

Oke … that is fine for me, i don’t care about the color as long as it won’t effect my file’s layout.
Thanks a lot for helping me so far.

Juist tried the sollution you first mentioned in your first post.
It did’nt help… but thank so far though!

(mentioning one thing… I don’t need to open te CSV file as text file, juist CSV in excel, but in the original layout)

Greets Cornelis

[michaelengel3777 (M.Engel)]

@blacornelis

(mentioning one thing… I don’t need to open te CSV file as text file, juist CSV in excel, but in the original layout)

And I think that is the problem.

You want your CSV to look like that:

You have a header an stuff… but a CSV normally looks like that:

The header is defined as well as the values

[michaelengel3777 (M.Engel)]

How should your CSV file know (if possible) what will be the header, when will the data start?

[blacornelis]

The last picture i send is wat my VBA macro creates when I save it as regular MS Excel Worksheet. (this is alwso ho i’d like it to look like.)

When i changed the FileFormat into CVS (like the code above is) my macro creates messy files like this:

[michaelengel3777 (M.Engel)]

And that’s correct.

At least for excel…

It will create a CSV according to your values. Could it be that your sheet has excatly the same amount of columns as you have commas behind your values? 12 or something? The CSV file can’t know anything about your format.
That will also explain lines A:13 - A16, in your original sheet these rows are blank. So Excel will fill them with empty Values. CSV mean Comma Seperated Value. So Row A13 will be , because in your Excel won’t be any data to copy.

[michaelengel3777 (M.Engel)]

In your code you copy cells. I would try to copy only the cells that are needed. The dataValues.
A16 & B16 until the end of the file. Take them and copy that to an CSV.

[Mike400]

From your code - you’re using the German version of Excel? What are the international settings for numbers on your computer. Excel’s CSV reader is sensitive to these settings. I’m taking a guess here that your numeric setting is

#.##0,00

This setting will invalidate the use of a single comma during the CSV read. Instead, you need to instruct Excel to save “,” as your delimiter. I think there is a flag for this in the Save As method.

[michaelengel3777 (M.Engel)]

@mikeober

From your code - you’re using the German version of Excel? What are the international settings for numbers on your computer. Excel’s CSV reader is sensitive to these settings. I’m taking a guess here that your numeric setting is

#.##0,00

This setting will invalidate the use of a single comma during the CSV read. Instead, you need to instruct Excel to save “,” as your delimiter. I think there is a flag for this in the Save As method.

As posted above:

Could it be that the seperator is wrong? Maybe ‘,’ is not write if you want it to look like that:

http://stackoverflow.com/questions/13496686/how-to-save-semi-colon-delimited-csv-file-using-vba

Specifying a Delimiter when Saving a CSV File in a Macro (Microsoft Excel)