Macro to open a CSV, edit data and create 2nd csv

[Brick Transport]

Hi,

Apologies if this has been asked before as I am sure it has (for the life of me I can't find a thread for it). I have a csv file called "import.csv" in the format:

	A	B	C	D	E	F	G	H	I	J
1	Type	A/c Ref	Nominal	<BLANK>	Date	Invoice No.	Narrative	Net Amount	Tax Code	Tax Amount

<colgroup><col style="mso-width-source:userset;mso-width-alt:512;width:11pt" width="14"> <col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:1901;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:1316;width:27pt" width="36"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody>
</tbody>

And I would like to create a macro to open this csv, manipulate the information and create a secondary csv, "4153/SCHEDULE.csv" in the format:

	A	B	C	D
1	A/c Ref	Invoice No	Date	Gross Amount

<colgroup><col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1901;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:1792;width:37pt" width="49"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> </colgroup><tbody>
</tbody>

both csv files are located in the same folder: C:\Users\User\xxxxxxxx\documents\Work


Being new to this I would like to know:
1. Where do you create the macro in the first place - a new workbook or within one of the original two?
2. What would be the code I'd need?
3. Could it be created so that all that's required would be to press a button and off it goes to do it's stuff - you know like a dialog box or something?

Thanks in advance!!

Bruce

 

[Yongle]

Test on a COPY of your csv

1. Where do you create the macro in the first place? a new workbook
2. What would be the code I'd need? see below
3. Could it be created so that all that's required would be to press a button and off it goes to do it's stuff - you know like a dialog box or something?
AFTER pasting the code, create a simple button like this:
- Insert a shape (eg rectangle) \ right-click on it \ Assign Macro \ Click on CreateCSV \ OK

NOTES
- amend fPath (should be the ONLY thing you need to change)
- cannot use "/" in file name (illegal character) hence "4153 SCHEDULE.csv"
- the first save is not strictly required but is precautionary (effectively renames file immediately)
- I assume that GROSS = NET + TAX AMOUNT

place in a standard module {Alt}{F11} takes you to VBA \ {ALT} I M (insertt Module) \ paste code \ {ALT}{F11} to go back to Excel

Sub CreateCSV()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const fPath = "[COLOR=#000080]C:\Users\User\xxxxxxxx\documents\Work[/COLOR]"
Const import = "import.csv"
Const fName = "4153 SCHEDULE.csv"
Dim wb As Workbook, ws As Worksheet, cel As Range, rng As Range
Set wb = Workbooks.Open(fPath & "\" & import)
Set ws = wb.Sheets(1)
Application.DisplayAlerts = False
wb.SaveAs Filename:=fPath & "\" & fName, FileFormat:=xlCSV

With ws
    Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Offset(, 10)
    For Each cel In rng
        cel = cel.Offset(, -3) + cel.Offset(, -1)
    Next cel
    .Range("K1") = "Gross Amount"
    .Columns("F").Copy ws.Range("D1")
    .Columns("F:J").Delete
    .Columns("C").Delete
    .Columns("A").Delete
End With
With wb
    .SaveAs Filename:=fPath & "\" & fName, FileFormat:=xlCSV
    .Close False
End With
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub

I started with

Type	A/c Ref	Nominal		Date	Invoice No.	Narrative	Net Amount	Tax Code	Tax Amount
a	123	q		18/10/2018	1	d	100	m	10
b	456	w		17/10/2018	2	c	200	n	20
c	789	e		16/10/2018	3	v	300	b	30

<tbody>
</tbody>

and generated

A/c Ref	Invoice No.	Date	Gross Amount
123	1	18/10/2018	110
456	2	17/10/2018	220
789	3	16/10/2018	330

<tbody>
</tbody>

 

[Brick Transport]

Thanks Yongle - thanks very much for your help, but it doesn't quite work for me somehow...

I've followed your instructions but it generates this:

A B C D E 1 60205 500000 19/10/2018 Gross Amount manager 2 0 <tbody> </tbody>

<tbody>
</tbody>

the source data is this:

A

B

C

D

E

F

G

H

I

J

K

L

1

SI

60205

4000

19/10/2018

500000

Invoice

220

1

44

manager

<tbody>
</tbody>

so I should be getting this:

	A	B	C	D
1	60205	500000	19/10/2018	264

<tbody>
</tbody>

There are no column or row headers by the way, I don't know if that makes a difference?

Any suggestions?

Thanks

 

[Yongle]

Will update the code later today when back at PC

 

[Yongle]

It was the lack of headers that was causing the problem - original code assumed row 1 contained headers

Delete original code and replace with code below
- assumes there are no headers and that data starts in row 1
- the 2 red lines can be removed if you do not want headers inserting in the new csv

Sub CreateCSV()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const fPath = "[COLOR=#000080]C:\Users\User\xxxxxxxx\documents\Work[/COLOR]"
Const import = "import.csv"
Const fName = "4153 SCHEDULE.csv"
Dim wb As Workbook, ws As Worksheet, cel As Range, rng As Range
Set wb = Workbooks.Open(fPath & "\" & import)
Set ws = wb.Sheets(1)

Application.DisplayAlerts = False
wb.SaveAs Filename:=fPath & "\" & fName, FileFormat:=xlCSV

With ws
    Set rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Offset(, 10)
    For Each cel In rng
        cel = cel.Offset(, -3) + cel.Offset(, -1)
    Next cel
    .Columns("F").Copy ws.Range("D1")
    .Columns("F:J").Delete
    .Columns("C").Delete
    .Columns("A").Delete
[COLOR=#ff0000]    .Rows(1).Insert Shift:=xlDown
    .Range("A1:D1") = Array("A/c Ref", "Invoice No", "Date", "Gross Amount")[/COLOR]
End With
    
With wb
    .SaveAs Filename:=fPath & "\" & fName, FileFormat:=xlCSV
    .Close False
End With
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Brick Transport]

Thank you so much for this - it works a treat!! Fantastic, really appreciate your help with this, its going to save me so much time.

 

[Brick Transport]

Hi Yongle,

Just one more question - I have set the macro up and created a button to run it in a new workbook and as I say it's working well, but is there a way to show the total of the "Gross Amount" column underneath the button in the new workbook (not in the 4153 SCHEDULE csv) ?

So using your example earlier:

A/c Ref	Invoice No.	Date	Gross Amount
123	1	18/10/2018	110
456	2	17/10/2018	220
789	3	16/10/2018	330

<tbody>
</tbody>

<tbody>
</tbody>

The total would be £660, but have it displayed in the workbook that the macro is stored in and run from?

<tbody>
</tbody>