From excel timesheet create a import file csv

[Kaz5208]

Hi, really hoping for some help please.
I have a excel timesheet which details hours worked against different pay elements. For example , columns would be ...
Ee no., name, no.of hrs, hourly rate, total pay. Header above theses columns would be PE Basic pay( 88). Following to the right , the next columns would be again, no.of hours, hourly rate, total pay. However the header above this set of columns would be PE overtime 1.5 (89) .

I am looking to create a csv file from this data. We currently copy and paste, but this is taking an age!!!
I would need the columns in the following order.

Ee no. PE code I.e 88, no. of hours, hourly rate
We dont need the data rows where there are no hours for a pay element, so need to delete those.
Then directly under the data rows have Ee no. PE code 89, no.of hours, hourly rate. So the import file is effectively has 4 columns but many rows.

Hope the above makes sense... I would appreciate any help.
Many thanks in advance

 

[Kaz5208]

Any help please?

 

[DanteAmor]

Hi @Kaz5208, welcome to the forum!

To perform a macro, you can provide the following data:
- Initial column of your data
- Final column of your data.
- Initial row of data.
- Row of headings.
- In which column values ​​equal to 0 should be removed.
- Which columns will be copied and what will be the order in the file.
- File name.
- File path.
- CSV separated by commas?

 

[Kaz5208]

Hi, thanks for your message. Sorry for the delay... was hoping that I could load a test timesheet template to show you, but I don't know if its possible on a thread chat?

So column data...

Initial column of your data A
- Final column of your data.AM
- Initial row of data. 12
- Row of headings.11
- In which column values ​​equal to 0 should be removed = From the "hours" column for each element
- Which columns will be copied and what will be the order in the file. Copied will be column A "Emp", Pay element "88" (in cell "C8") then Basic hours column C, then rate from column D.
- File name. Import file creation
- File path. C:// my documents
- CSV separated by commas? Yes by commas.

Basic Pay Rate	Column A	STARTS ON ROW 12
Basic Pay	Column B
Basic Pay Hours	Column C
Basic Pay Rate	d
Basic Pay	e
Basic Pay Hours	f
Basic Pay Rate	g
Basic Pay	h
Basic Pay Hours	i
Basic Pay Rate	j
Basic Pay	k
Basic Pay Hours	l
Basic Pay Rate	m
Basic Pay	n
Basic Pay Hours	o
Basic Pay Rate	p
Basic Pay	q
Holiday	Hours	r
Holiday Pay	Rate	s
Holiday	Pay	t
Holiday	Hours	u
Holiday Pay	Rate	v
Holiday	Pay	w
Training Hours/Courses	x
Training Rate	y
Training	z
Shadowing Hours	AA
Shadowing Rate	AB
Shadowing	AC
Xmas Pay Hours	AD
Xmas Pay Rate	AE
Xmas pay	AF

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

The following columns .....................

We will need "ee(col A), Pay element (BEcol R11) then always 1.00 hour (this isn't on the timesheet) then rate will be the "total amount" (AG Col R12)

Travel Time	AG
Expenses	AH
Dom Care On Call	AI
Senior Increment	AJ
Pay Adjustment	AK
Missed Calls	AL
Finders Fee	AM

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Travel Time	AG
Expenses	AH
Dom Care On Call	AI
Senior Increment	AJ
Pay Adjustment	AK
Missed Calls	AL
Finders Fee	AM

<tbody>
</tbody>

</body>

I have done very basic VBA, so if you can justs start me off with the coding, I can always have a go at finishing it off.

Another thing to note is that new employees can be added to the timesheet each week, so row91 may not always be the last data row. So I was thinking of using the follow

'Copy the data<o></o>
<o></o>
Sheets("Timesheet").Range("A12",Range("A12").End(xlDown)).Copy

Is this correct?

Many thanks in advance.

K

 

[DanteAmor]

Please, Put the letter of the column.

- In which column values ​​equal to 0 should be removed.

I don't understand which columns go in the file.
Do you want to put all the columns?
Is the order of the columns in the file the same as you have it in the excel sheet?

 

[Kaz5208]

Branch
Wages
Tax Week
From
To
			8.21			8.35			8.46			8.50			8.60			8.71
	88	88	88	89	89	89	90	90	90	91	91	91	92	92	92	93	93	93
EE No	Employee	Basic Pay Hours	Basic Pay Rate	Basic Pay	Basic Pay Hours	Basic Pay Rate	Basic Pay	Basic Pay Hours	Basic Pay Rate	Basic Pay	Basic Pay Hours	Basic Pay Rate	Basic Pay	Basic Pay Hours	Basic Pay Rate	Basic Pay	Basic Pay Hours	Basic Pay Rate	Basic Pay
		T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI	T & NI
1102	Jo Bloggs	24.00	8.21	197.04	34.00	8.35	283.90		8.46	0.00		8.50	0.00		8.60	0.00		8.71	0.00
So csv file 1102,88,24,8.21 1102,89,34,8.35
1102,90,0,8.46	Delete this line as no hours to import
headers for csv file ........... pay no, pay element, hours,rate

<tbody>
</tbody>

Thanks

 

[DanteAmor]

I don't understand your columns, I need you to put the letter of the column, example: A, B, C, D, etc.

Please, Put the letter of the column.

- In which column values ​​equal to 0 should be removed.

I don't understand which columns go in the file.
Do you want to put all the columns?
Is the order of the columns in the file the same as you have it in the excel sheet?

 

[DanteAmor]

If it is difficult for you to explain it with the columns, then upload 2 files to the cloud, an excel file with data, put the column headings in green and the rows in yellow that should be sent to the CSV file.


You also upload the CSV file to the cloud with the example of the output, obviously based on the same excel file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Kaz5208]

Hi,

Hope this explains what I am trying to achieve.

This is the link to the dropbox, dummy data entered.
Tab 1 = Timesheet
Tab 2 = Data from Timesheet (manually copy & paste)
Tab 3 = The csv file to import into system.


test timesheet to import.xlsx


Many thanks for your patience with me on this forum.

Karen

 

[DanteAmor]

Run this macro "create_csv_file"
The macro only requires 2 sheets, the input and output ("Timesheet" and "Final import")
If necessary change the name of these sheets ("Timesheet" and "Final import") in the macro

Dim k As Long 'This line goes to the beginning of all the code
Sub create_csv_file()
  Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, j As Long
  Dim wPath As String, wFile As String
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh1 = Sheets("Timesheet")
  Set sh2 = Sheets("Final import")
  sh2.Range("A2:D" & Rows.Count).Clear
 
  'copy and paste Data
  k = 2
  For j = Columns("C").Column To Columns("BB").Column Step 3
    i = 12
    Do While sh1.Cells(i, "A") <> ""
      Call Put_data(sh1, sh2, i, j, k, 1)
      i = i + 1
    Loop
  Next
  For j = Columns("BE").Column To Columns("BN").Column
    i = 12
    Do While sh1.Cells(i, "A") <> "" And sh1.Cells(8, j).Value <> ""
      Call Put_data(sh1, sh2, i, j, k, 0)
      i = i + 1
     Loop
  Next
 
  'Save file
  wPath = "C:\trabajo\"
  wFile = "Import file creation.csv"
  sh2.Copy
  ActiveWorkbook.SaveAs Filename:=wPath & wFile, FileFormat:=xlCSV, CreateBackup:=False
  ActiveWorkbook.Close False
 
  MsgBox "End"
End Sub
'
Sub Put_data(sh1, sh2, i, j, k, st)
  If sh1.Cells(i, j).Value <> "" And sh1.Cells(i, j).Value <> 0 Then
    sh2.Cells(k, "A").Value = sh1.Cells(i, "A").Value
    sh2.Cells(k, "B").Value = sh1.Cells(8, j).Value
    sh2.Cells(k, "C").Value = IIf(st = 1, sh1.Cells(i, j).Value, 1)
    sh2.Cells(k, "D").Value = sh1.Cells(i, j + st).Value
    k = k + 1
  End If
End Sub