From excel timesheet create a import file csv

Kaz5208

New Member
Joined
Nov 7, 2019
Messages
6
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

New Member
Joined
Nov 7, 2019
Messages
6
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 RateColumn ASTARTS ON ROW 12
Basic PayColumn B
Basic Pay HoursColumn C
Basic Pay Rated
Basic Paye
Basic Pay Hoursf
Basic Pay Rateg
Basic Payh
Basic Pay Hoursi
Basic Pay Ratej
Basic Payk
Basic Pay Hoursl
Basic Pay Ratem
Basic Payn
Basic Pay Hourso
Basic Pay Ratep
Basic Payq
HolidayHoursr
Holiday PayRates
HolidayPayt
HolidayHoursu
Holiday PayRatev
HolidayPayw
Training Hours/Coursesx
Training Ratey
Trainingz
Shadowing HoursAA
Shadowing RateAB
ShadowingAC
Xmas Pay HoursAD
Xmas Pay RateAE
Xmas payAF

<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 TimeAG
ExpensesAH
Dom Care On CallAI
Senior IncrementAJ
Pay AdjustmentAK
Missed CallsAL
Finders FeeAM

<tbody>
</tbody>
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
Travel TimeAG
ExpensesAH
Dom Care On CallAI
Senior IncrementAJ
Pay AdjustmentAK
Missed CallsAL
Finders FeeAM

<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:p></o:p>
<o:p></o:p>
Sheets("Timesheet").Range("A12",Range("A12").End(xlDown)).Copy

Is this correct?

Many thanks in advance.

K
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

New Member
Joined
Nov 7, 2019
Messages
6
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 NoEmployeeBasic Pay HoursBasic Pay RateBasic PayBasic Pay HoursBasic Pay RateBasic PayBasic Pay HoursBasic Pay RateBasic PayBasic Pay HoursBasic Pay RateBasic PayBasic Pay HoursBasic Pay RateBasic PayBasic Pay HoursBasic Pay RateBasic Pay
T & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NIT & NI
1102Jo Bloggs24.008.21197.04 34.008.35283.90 8.460.00 8.500.00 8.600.00 8.710.00
So csv file

1102,88,24,8.21
1102,89,34,8.35
1102,90,0,8.46Delete this line as no hours to import
headers for csv file ...........
pay no, pay element, hours,rate

<tbody>
</tbody>

Thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

New Member
Joined
Nov 7, 2019
Messages
6
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

VBA Code:
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
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top