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

exPERten

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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).

CONTRACTMCH-CODEMCH-NAMEMCH-LOCMCH-POSMCH-DOCPURCH-PRICE
Object SiteObject IDDescriptionRoomPositionDocumentsAcquisition Cost
VARCHAR2VARCHAR2VARCHAR2VARCHAR2VARCHAR2VARCHAR2NUMBER
510020010152020
KOP12345-U011T1A1
KOP12345-S0011-1T1A1
KOP12345-C0001Kula, till: F, Modell: M, Tillv: T, Tillvnr: 6T1A1
KOP12345-U022T1A1
KOP12345-S0022-2T1A1
KOP12345-C0002Boll, till: F, Modell: M, Tillv: T, Tillvnr: 8T1A1
-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;;;;;;;;;;;;
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,521
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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?
1592912650676.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,521
Office Version
  1. 365
Platform
  1. Windows
OK, if you already have that set, try it out and see if it works like the way you want.
 

exPERten

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,521
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,521
Office Version
  1. 365
Platform
  1. Windows
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).
VBA Code:
Sub MacroTest()

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,521
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
dWB.SaveAs Filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS
with this:
VBA Code:
Call CreateUnicodeFile(Path & FileName1 & "_" & FileName2 & ".csv", ActiveSheet.UsedRange, ";")
and see if that works.
 

exPERten

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
dWB.SaveAs Filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlTextMSDOS
with this:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,223
Messages
5,576,828
Members
412,748
Latest member
MikeyP14
Top