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]

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.

Ah, yes. That would make sense. I didn't realize that you have to tell it to use your local settings. I thought that updated the Regional Settings would be enough, but apparently you need to do that too.

Glad you got it all figured out!

 

[exPERten]

Solved!!!!!!!! Thanks Joe4 for all the help, I appriciate it alot!

Private Sub Create_CSV()
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\"
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:=xlCSV, Local:=True
dWB.Close False
End Sub