VBA Macro to export a data range to a semi colon separated CSV file.

[exPERten]

Hello guys.
I am new here and have tried to search for an answer that suits my needs, but to no avail.
I have managed to do almost all I need it to do by copying functions from others suggestions here.

Private Sub CommandButton1_Click()
Dim content As String
Dim rng As Range
Set rng = Range("A12:AS30")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB As Workbook, _
sWS As Worksheet

Dim dWB As Workbook, _
dWS As Worksheet

Path = "\\PATH\"
'Path = "PATH"
FileName1 = Range("A16")
FileName2 = Range("B16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("A12:AS30").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS
dWB.Close False
End Sub


The data set that I want to export is built by a number of formulas, some resulting in an empty cell (only containing the formula, no data).

CONTRACT	MCH-CODE	MCH-NAME	MCH-LOC	MCH-POS	MCH-DOC	PURCH-PRICE
Object Site	Object ID	Description	Room	Position	Documents	Acquisition Cost
VARCHAR2	VARCHAR2	VARCHAR2	VARCHAR2	VARCHAR2	VARCHAR2	NUMBER
5	100	200	10	15	20	20
KOP	12345-U01	1	T1	A1
KOP	12345-S001	1-1	T1	A1
KOP	12345-C0001	Kula, till: F, Modell: M, Tillv: T, Tillvnr: 6	T1	A1
KOP	12345-U02	2	T1	A1
KOP	12345-S002	2-2	T1	A1
KOP	12345-C0002	Boll, till: F, Modell: M, Tillv: T, Tillvnr: 8	T1	A1
	-U
	-U
	-U

I want all cells copied to the CSV file, but the ones without data just as an empty field.
The thing I need help with is that my crated CSV file is not separated by anything other than tabs it looks like.

What I get now is:
CONTRACT MCH-CODE MCH-NAME MCH-LOC MCH-POS MCH-DOC PURCH-PRICE PURCH-DATE WARR-EXP NOTE INFO DATA COMPANY PRODUCTION-DATE EQUIPMENT-MAIN-POSITION GROUP-ID MCH-TYPE COST-CENTER OBJECT-NO CATEGORY-ID SUP-MCH-CODE SUP-CONTRACT OBJ-LEVEL MANUFACTURER-NO VENDOR-NO SERIAL-NO TYPE PART-NO CRITICALITY PLANT-DESIGN-ID PLANT-DESIGN-PROJPHASE PLANT-DESIGN-COTPROJ-PROJID IS-CATEGORY-OBJECT IS-GEOGRAPHIC-OBJECT OPERATIONAL-STATUS OPERATIONAL-STATUS-DB MANUFACTURED-DATE CF$_MACHINE_CLASSIFICATION_DB CF$_MACHINE_CLASSIFICATION CF$_SAP_ASSET_NO CF$_MODEL CF$_COMP_CLASS_DB CF$_COMP_CLASS CF$_CLASS CF$_DRAWING_POS
Object Site Object ID Description Room Position Documents Acquisition Cost Purchase Date Warranty Expires Note Info Data Company Production Date Main Position Group ID Object Type Cost Center Asset CategoryID Belongs to ObjectID Belongs to Site Object Level Manufacturer Supplier Serial No Type Designation Part No Criticality Plant Design Id Plant Design Projphase Plant Design Cotproj Projid Is a Category Object Is a Geographic Object Operational Status Operational Status Manufactured Date Machine Classification Machine Classification Sap Asset No Model Component Classification Component Classification Class Drawing Position
VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2
5 100 200 10 15 20 20 21 21 2000 2000 2000 4000 21 4000 10 20 10 10 10 100 5 30 20 20 50 30 25 10 20 20 10 5 5 4000 20 21 20 4000 100 100 20 20 25 100
KOP 12345-U01 1 T1 A1 UNIT 7111 4S 12345 KOP 090-UNIT IN_OPERATION AA AA
KOP 12345-S001 1-1 T1 A1 SUB-UNIT 7111 4S 12345-U01 KOP 100-SUBUNIT IN_OPERATION AA AA
KOP 12345-C0001 "Kula, till: F, Modell: M, Tillv: T, Tillvnr: 6" T1 A1 COMPONENT 7111 4S 12345-S001 KOP 110-COMPONENT S IN_OPERATION AA AA M A1 A1
KOP 12345-U02 2 T1 A1 UNIT 7111 4S 12345 KOP 090-UNIT IN_OPERATION AA AA
KOP 12345-S002 2-2 T1 A1 SUB-UNIT 7111 4S 12345-U02 KOP 100-SUBUNIT IN_OPERATION AA AA
KOP 12345-C0002 "Boll, till: F, Modell: M, Tillv: T, Tillvnr: 8" T1 A1 COMPONENT 7111 4S 12345-S002 KOP 110-COMPONENT S IN_OPERATION AA AA M B1 B1
-U UNIT 0 090-UNIT
-U UNIT 0 090-UNIT

I would like it to be:

CONTRACT;MCH-CODE;MCH-NAME;MCH-LOC;MCH-POS;MCH-DOC;PURCH-PRICE;PURCH-DATE;WARR-EXP;NOTE;INFO;DATA;COMPANY;PRODUCTION-DATE;EQUIPMENT-MAIN-POSITION;GROUP-ID;MCH-TYPE;COST-CENTER;OBJECT-NO;CATEGORY-ID;SUP-MCH-CODE;SUP-CONTRACT;OBJ-LEVEL;MANUFACTURER-NO;VENDOR-NO;SERIAL-NO;TYPE;PART-NO;CRITICALITY;PLANT-DESIGN-ID;PLANT-DESIGN-PROJPHASE;PLANT-DESIGN-COTPROJ-PROJID;IS-CATEGORY-OBJECT;IS-GEOGRAPHIC-OBJECT;OPERATIONAL-STATUS;OPERATIONAL-STATUS-DB;MANUFACTURED-DATE;CF$_MACHINE_CLASSIFICATION_DB;CF$_MACHINE_CLASSIFICATION;CF$_SAP_ASSET_NO;CF$_MODEL;CF$_COMP_CLASS_DB;CF$_COMP_CLASS;CF$_CLASS;CF$_DRAWING_POS
Object Site;Object ID;Description;Room;Position;Documents;Acquisition Cost;Purchase Date;Warranty Expires;Note;Info;Data;Company;Production Date;Main Position;Group ID;Object Type;Cost Center;Asset;CategoryID;Belongs to ObjectID;Belongs to Site;Object Level;Manufacturer;Supplier;Serial No;Type Designation;Part No;Criticality;Plant Design Id;Plant Design Projphase;Plant Design Cotproj Projid;Is a Category Object;Is a Geographic Object;Operational Status;Operational Status;Manufactured Date;Machine Classification;Machine Classification;Sap Asset No;Model;Component Classification;Component Classification;Class;Drawing Position
VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;NUMBER;DATE;DATE;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;DATE;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;NUMBER;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;DATE;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2;VARCHAR2
5;100;200;10;15;20;20;21;21;2000;2000;2000;4000;21;4000;10;20;10;10;10;100;5;30;20;20;50;30;25;10;20;20;10;5;5;4000;20;21;20;4000;100;100;20;20;25;100
KOP;12345-U01;1;T1;A1;;;;;;;;;;;;UNIT;7111;;4S;12345;KOP;090-UNIT;;;;;;;;;;;;;IN_OPERATION;;AA;AA;;;;;;
KOP;12345-S001;1-1;T1;A1;;;;;;;;;;;;SUB-UNIT;7111;;4S;12345-U01;KOP;100-SUBUNIT;;;;;;;;;;;;;IN_OPERATION;;AA;AA;;;;;;
KOP;12345-C0001;"Kula, till: F, Modell: M, Tillv: T, Tillvnr: 6";T1;A1;;;;;;;;;;;;COMPONENT;7111;;4S;12345-S001;KOP;110-COMPONENT;;;S;;;;;;;;;;IN_OPERATION;;AA;AA;;M;A1;A1;;
KOP;12345-U02;2;T1;A1;;;;;;;;;;;;UNIT;7111;;4S;12345;KOP;090-UNIT;;;;;;;;;;;;;IN_OPERATION;;AA;AA;;;;;;
KOP;12345-S002;2-2;T1;A1;;;;;;;;;;;;SUB-UNIT;7111;;4S;12345-U02;KOP;100-SUBUNIT;;;;;;;;;;;;;IN_OPERATION;;AA;AA;;;;;;
KOP;12345-C0002;"Boll, till: F, Modell: M, Tillv: T, Tillvnr: 8";T1;A1;;;;;;;;;;;;COMPONENT;7111;;4S;12345-S002;KOP;110-COMPONENT;;;S;;;;;;;;;;IN_OPERATION;;AA;AA;;M;B1;B1;;
;-U;;;;;;;;;;;;;;;UNIT;;;;0;;090-UNIT;;;;;;;;;;;;;;;;;;;;;;
;-U;;;;;;;;;;;;;;;UNIT;;;;0;;090-UNIT;;;;;;;;;;;;

 

[Joe4]

Welcome to the Board!

First, if you want to save as a CSV, you need to use the CSV file format in your save line.
So this:
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS
should look like this:
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV

Second, if you want the CSV to use a semi-colon instead of a comma as the field separator, you need to update your Regional Settings and change the separator.

 

[exPERten]

Welcome to the Board!

First, if you want to save as a CSV, you need to use the CSV file format in your save line.
So this:
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS
should look like this:
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV

Second, if you want the CSV to use a semi-colon instead of a comma as the field separator, you need to update your Regional Settings and change the separator.

Thanks for a quick response.
The first one worked like a charm!
The other I dont get. It looks like I have ; as list separator, or do I need to change somewhere else?

 

[Joe4]

OK, if you already have that set, try it out and see if it works like the way you want.

 

[exPERten]

OK, if you already have that set, try it out and see if it works like the way you want.

It was already like that. Doesnt make a difference if I change it back to comma either.

 

[Joe4]

If that is not working, then you might need to creaate VBA code to save it the way you want.
People have already done this, so you could just "borrow" their code.
Here is one example that was found pretty quickly with a simple Google search: VBA code: Save an excel file as semicolon separated values

 

[exPERten]

If that is not working, then you might need to creaate VBA code to save it the way you want.
People have already done this, so you could just "borrow" their code.
Here is one example that was found pretty quickly with a simple Google search: VBA code: Save an excel file as semicolon separated values

Thanks for bearing with me. I am totally new to VBA.
The last link did not work unfortunately, there seems to be some links in it: (Seems to be a dead link to this forum.)

Open File For Binary Access Write As #1
Put #1 , , Bytes
Close #1

I get this:
VBA error Run-time error '75
Path/File access error,

 

[Joe4]

Thanks for bearing with me. I am totally new to VBA.
The last link did not work unfortunately, there seems to be some links in it: (Seems to be a dead link to this forum.)

Open File For Binary Access Write As #1
Put #1 , , Bytes
Close #1

I get this:
VBA error Run-time error '75
Path/File access error,

No, those really aren't links. The forum thinks that they are links (anything preceded with a "#"), but they really aren't. that is literally the signs the code needs to have.

As far as your error, you need to update the file path and name to whatever you want (you probably don't have the directory listed in that example).

Sub MacroTest()

    Call CreateUnicodeFile("C:\Users\Public\Desktop\SemicolonSeparatedFile.csv", ActiveSheet.UsedRange, ";")
    
End Sub

 

[Joe4]

So I think you do not need the "smaller" macro from that link.
You would just make the substitution in your original code.

Try replacing this line:

dWB.SaveAs Filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS

with this:

Call CreateUnicodeFile(Path & FileName1 & "_" & FileName2 & ".csv", ActiveSheet.UsedRange, ";")

and see if that works.

 

[exPERten]

So I think you do not need the "smaller" macro from that link.
You would just make the substitution in your original code.

Try replacing this line:

dWB.SaveAs Filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS

with this:

Call CreateUnicodeFile(Path & FileName1 & "_" & FileName2 & ".csv", ActiveSheet.UsedRange, ";")

and see if that works.

I managed to find an easy way to solve it.
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV ---- Just added: , Local:=True in the end of this row.