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

Brick Transport

Brick Transport

New Member
Joined
Nov 17, 2017
Messages
22
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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:

[TABLE="class: grid, width: 500, align: center"]
<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>[TR]
[TD="width: 14"][/TD]
[TD="width: 37"]A[/TD]
[TD="width: 52"]B[/TD]
[TD="width: 60"]C[/TD]
[TD="width: 62"]D[/TD]
[TD="width: 36"]E[/TD]
[TD="width: 77"]F[/TD]
[TD="width: 65"]G[/TD]
[TD="width: 83"]H
[/TD]
[TD="width: 63"]I[/TD]
[TD="width: 81"]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type[/TD]
[TD]A/c Ref[/TD]
[TD]Nominal[/TD]
[TD]<BLANK>[/TD]
[TD]Date[/TD]
[TD]Invoice No.[/TD]
[TD]Narrative[/TD]
[TD]Net Amount[/TD]
[TD]Tax Code[/TD]
[TD]Tax Amount
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500, align: center"]
<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>[TR]
[TD="width: 56"][/TD]
[TD="width: 52"]A
[/TD]
[TD="width: 75"]B
[/TD]
[TD="width: 49"]C
[/TD]
[TD="width: 95"]D
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A/c Ref[/TD]
[TD]Invoice No[/TD]
[TD]Date[/TD]
[TD]Gross Amount
[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
icon_warning.gif
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
Code: 
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

[TABLE="width: 705"]
<tbody>[TR]
[TD]Type[/TD]
[TD]A/c Ref[/TD]
[TD]Nominal[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Invoice No.[/TD]
[TD]Narrative[/TD]
[TD]Net Amount[/TD]
[TD]Tax Code[/TD]
[TD]Tax Amount[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]123[/TD]
[TD]q[/TD]
[TD][/TD]
[TD="align: right"]18/10/2018[/TD]
[TD="align: right"]1[/TD]
[TD]d[/TD]
[TD="align: right"]100[/TD]
[TD]m[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]456[/TD]
[TD]w[/TD]
[TD][/TD]
[TD="align: right"]17/10/2018[/TD]
[TD="align: right"]2[/TD]
[TD]c[/TD]
[TD="align: right"]200[/TD]
[TD]n[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]789[/TD]
[TD]e[/TD]
[TD][/TD]
[TD="align: right"]16/10/2018[/TD]
[TD="align: right"]3[/TD]
[TD]v[/TD]
[TD="align: right"]300[/TD]
[TD]b[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

and generated

[TABLE="width: 369"]
<tbody>[TR]
[TD]A/c Ref[/TD]
[TD]Invoice No.[/TD]
[TD]Date[/TD]
[TD]Gross Amount[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18/10/2018[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]17/10/2018[/TD]
[TD="align: right"]220[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16/10/2018[/TD]
[TD="align: right"]330[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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:


<tbody>
[TD="align: right"] [TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="width: 42, align: center"][/TD]
[TD="width: 42, align: center"]A[/TD]
[TD="width: 49, align: center"]B[/TD]
[TD="width: 75, align: center"]C[/TD]
[TD="width: 95, align: center"]D[/TD]
[TD="width: 61, align: center"]E[/TD]

[TD="width: 42, align: center"]1[/TD]
[TD="width: 42, align: right"]60205[/TD]
[TD="width: 49, align: right"]500000[/TD]
[TD="width: 75, align: right"]19/10/2018[/TD]
[TD="width: 95"]Gross Amount[/TD]
[TD="width: 61"]manager[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]

</tbody>
[/TD]
[TD="width: 49, align: right"][/TD]
[TD="class: xl63, width: 75, align: right"][/TD]
[TD="width: 95"][/TD]
[TD="width: 61"][/TD]
[/TR]
</tbody>[/TABLE]

the source data is this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]SI[/TD]
[TD="align: center"]60205[/TD]
[TD="align: center"]4000[/TD]
[TD][/TD]
[TD]19/10/2018[/TD]
[TD]500000[/TD]
[TD]Invoice[/TD]
[TD]220[/TD]
[TD]1[/TD]
[TD]44[/TD]
[TD][/TD]
[TD]manager[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


so I should be getting this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 42, align: center"][/TD]
[TD="width: 42, align: center"]A[/TD]
[TD="width: 49, align: center"]B[/TD]
[TD="width: 75, align: center"]C[/TD]
[TD="width: 95, align: center"]D[/TD]
[/TR]
[TR]
[TD="width: 42, align: center"]1[/TD]
[TD="width: 42, align: center"]60205[/TD]
[TD="width: 49, align: center"]500000[/TD]
[TD="width: 75, align: center"]19/10/2018[/TD]
[TD="width: 95, align: center"]264[/TD]
[/TR]
</tbody>[/TABLE]


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

Any suggestions?

Thanks
 
Last edited:
Upvote 0
Will update the code later today when back at PC
 
Upvote 0
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

Code: 
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:


[TABLE="class: cms_table"]
<tbody>[TR]
[TD]A/c Ref[/TD]
[TD]Invoice No.[/TD]
[TD]Date[/TD]
[TD]Gross Amount[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18/10/2018[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]17/10/2018[/TD]
[TD="align: right"]220[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16/10/2018[/TD]
[TD="align: right"]330
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table"]
<tbody>[TR]

[/TR]
[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
The total would be £660, but have it displayed in the workbook that the macro is stored in and run from?
[TABLE="class: cms_table"]
<tbody>[TR]

[/TR]
[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
pulling data from one chart to another
Replies
1
Views
814
jb00976
J
T
  • Question Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit
O
  • Question Question
Survey data to capture full month
Replies
5
Views
883
DanteAmor
DanteAmor
F
  • Question Question
Time Percentages
Replies
1
Views
909
James006
James006
TheRogue
  • Question Question
Dynamic List from Table
2
Replies
14
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top