Create CSV file from data in worksheet

[Dan5977]

Hi all,

I'd be really grateful for your help on this one. The number in the left hand column is a payroll reference and the other columns show the hours each employee worked at each rate. There will be a lot more rows but this is enough to work with.

	Hours At Each Rate
	1	2	3	4
81	37.5	2.5	0	0
167	37.5	0	0	0
210	37.5	0	0	0
89	37.5	2.5	0	0

I need to create a CSV file with a rather specific format so that it can be imported into our accounting software. Hopefully the below table showing the required format of the CSV file makes sense.

Employee Reference​	Payment Reference​	Hours​
81​	1​	37.5​
81​	2​	2.5​
81​	3​	0​
81​	4​	0​
167​	1​	37.5​
167​	2​	0​
167​	3​	0​
167​	4​	0​
210​	1​	37.5​
210​	2​	0​
210​	3​	0​
210​	4​	0​
89​	1​	37.5​
89​	2​	2.5​
89​	3​	0​
89​	4​	0​

What I need to do is create a line for each payment reference (1-4) for employee and place the correct hours alongside. Ideally I would put a button on the front sheet of the spreadsheet to export the CSV file. I can manage that part but coming up with the right VBA code is beyond my current capabilities unfortunately.

I'm not against adding columns or sheets to rearrange the source data if necessary.

 

[rpaulson]

try this,

Sub Creat_Text_File()

myfile = "c:\temp\test.csv"
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists

Data = "Employee Reference,Payment Reference,Hours" & vbNewLine
For r = 3 To Cells(Rows.Count, "A").End(xlUp).Row
    For c = 2 To 5 'column numbers
    
        Data = Data & Cells(r, "A") & "," & c - 1 & "," & Cells(r, c) & vbNewLine
    Next c
    
  Next r
Open myfile For Append As #1
Print #1, Data
Close #1


MsgBox myfile & "  has been Created"

End Sub

 

[Dan5977]

try this,

Sub Creat_Text_File()

myfile = "c:\temp\test.csv"
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists

Data = "Employee Reference,Payment Reference,Hours" & vbNewLine
For r = 3 To Cells(Rows.Count, "A").End(xlUp).Row
    For c = 2 To 5 'column numbers
   
        Data = Data & Cells(r, "A") & "," & c - 1 & "," & Cells(r, c) & vbNewLine
    Next c
   
  Next r
Open myfile For Append As #1
Print #1, Data
Close #1


MsgBox myfile & "  has been Created"

End Sub

Thank you so much.

This is exactly what I need. Works great. I just need to run it from a button now and change the file location but I can handle that.

 

[rpaulson]

glad to help