Hello all! I am a total noob trying to write code so forgive me in advance, but I do catch on quickly! Also there might be more than one need for a solution to achieve what I am trying to do in this post. Whatever you can do to help would be appreciated!
I am trying to create a macro in excel 2003 to import specific data from a worksheet into a different workbook. I’ve almost got it, but realized there are a number of exceptions that I did not account for and could use your help figuring out how to make this work.
Background: Our company uses an online time punch system to capture the time employees clock in and clock out each day. Every two weeks we will be exporting this online data into a .csv file that is then formatted and imported into a different workbook that contains timecards which we print out and turn in.
The macro will be used to import specific cells of data from a workbook with a single worksheet whose name will change often, but whose location will remain the same. This data will be imported into a different workbook into the currently active worksheet, where it will be saved and then printed. This different workbook contains 26 worksheets that are formatted to look like timecards. Each timecard contains half the month divided by days, ie 1-15, 16-31.
The imported data will not fill every date in the two week timecard period because we turn timecards in early, which is why we need the second workbook to allow employees to manually enter the remainder of their schedule before turning it in.
The code I’ve put together is below. I’ve also uploaded 3 example .csv files of data to work with and an example of the different workbook they will be imported into.
https://app.box.com/s/455k848v80j4lxngiojk
What I need it to do:
Compare dates in source (data) .csv file and if dates match in target (timecard) .xls file, copy punch in/out times from source .csv file to proper location in target .xls file.
A user would typically punch in when they arrive at work, punch out for lunch, punch in again after lunch, and punch out at the end of the day. This is ideal, but hardly ever the case…
What I have successfully written code to do thus far:
- formatted dates and times in source file
- formatted system computed daily time totals (STD) into hours
- added as many comments in code to clarify what I am trying to do in each step
- I used two different formulas to round to the quarter hour based on 7/8 minute split. Not sure if one formula is better than the other, maybe you could give me your opinion, they both seem to work ok.
EXAMPLE ROUNDING:
7:53 - 8:07 rounded to 8:00
8:08 - 8:22 rounded to 8:15
8:23 – 8:37 rounded to 8:30
8:38 – 8:52 rounded to 8:45
8:53 – 9:07 rounded to 9:00
What I think I need help doing:
- Creating a nested loop that searches through columns (Date In, Date Out) in the source .csv files to find dates that it can compare and match against in target .xls file (starting in A14) before pasting punch times from source file columns (Time In, Time Out) for those corresponding dates in target file (B14, D14, F14, H14) while at the same time making sure there are no exceptions and if they exist taking them into account before importing to target .xls file.
I started the loop, which is at the bottom of all the code, but it only copies the first date, and does not take into consideration any of the exceptions present in the three example workbooks I uploaded, but does perform a successful comparison of dates before pasting the corresponding first time into the timecard. Its just a starting point.
- feedback on this whole process and where, if I can condense and simplify code and all these processes that are performed. And if this is the best way to achieve my goals?
- the exceptions in the example .csv worksheets 1, 2, & 3 that I uploaded.
- added comments in code where I am unsure if lines are necessary or better way to do something, assistance welcomed
- I said “what I think I need help doing” because maybe I am not communicating my needs correctly?
- maybe I need to just forget the actual times and just copy over the total time before and after lunch if the dates match? total time in the “Sub Hour” field can be used for this purpose if typical punch is performed, but not sure how to do this with exceptions involved.
Exceptions I cannot figure out (and they are many):
- the exceptions are the messiest and hardest part for me to figure out how to compare/total up dates/times and include adjustments to produce a single time value to copy into target file and enter total time in hours.
Exceptions occur when a manager has to manually modify an employees timecard data online, which adds InFlags/OutFlags and Notes based on what occurred.
- The example exceptions I found that need to be accounted for:
I will have to post the exceptions I find in the three .csv files in the comments after I take a better look at them. I think if you can see how the system works you might be able to figure them out.
And lastly a minor question: once completed will this macro be able to work in excel 2010 too?
Code I’ve done up to this point is below. Its around 200 lines so far. Apologies for the long winded explanation. I’ve been a lurker on this site for long enough to know detail can be appreciated when trying to assist others such as me. Thank you for your assistance and genius and please ask any clarifying questions as needed!
I am trying to create a macro in excel 2003 to import specific data from a worksheet into a different workbook. I’ve almost got it, but realized there are a number of exceptions that I did not account for and could use your help figuring out how to make this work.
Background: Our company uses an online time punch system to capture the time employees clock in and clock out each day. Every two weeks we will be exporting this online data into a .csv file that is then formatted and imported into a different workbook that contains timecards which we print out and turn in.
The macro will be used to import specific cells of data from a workbook with a single worksheet whose name will change often, but whose location will remain the same. This data will be imported into a different workbook into the currently active worksheet, where it will be saved and then printed. This different workbook contains 26 worksheets that are formatted to look like timecards. Each timecard contains half the month divided by days, ie 1-15, 16-31.
The imported data will not fill every date in the two week timecard period because we turn timecards in early, which is why we need the second workbook to allow employees to manually enter the remainder of their schedule before turning it in.
The code I’ve put together is below. I’ve also uploaded 3 example .csv files of data to work with and an example of the different workbook they will be imported into.
https://app.box.com/s/455k848v80j4lxngiojk
What I need it to do:
Compare dates in source (data) .csv file and if dates match in target (timecard) .xls file, copy punch in/out times from source .csv file to proper location in target .xls file.
A user would typically punch in when they arrive at work, punch out for lunch, punch in again after lunch, and punch out at the end of the day. This is ideal, but hardly ever the case…
What I have successfully written code to do thus far:
- formatted dates and times in source file
- formatted system computed daily time totals (STD) into hours
- added as many comments in code to clarify what I am trying to do in each step
- I used two different formulas to round to the quarter hour based on 7/8 minute split. Not sure if one formula is better than the other, maybe you could give me your opinion, they both seem to work ok.
EXAMPLE ROUNDING:
7:53 - 8:07 rounded to 8:00
8:08 - 8:22 rounded to 8:15
8:23 – 8:37 rounded to 8:30
8:38 – 8:52 rounded to 8:45
8:53 – 9:07 rounded to 9:00
What I think I need help doing:
- Creating a nested loop that searches through columns (Date In, Date Out) in the source .csv files to find dates that it can compare and match against in target .xls file (starting in A14) before pasting punch times from source file columns (Time In, Time Out) for those corresponding dates in target file (B14, D14, F14, H14) while at the same time making sure there are no exceptions and if they exist taking them into account before importing to target .xls file.
I started the loop, which is at the bottom of all the code, but it only copies the first date, and does not take into consideration any of the exceptions present in the three example workbooks I uploaded, but does perform a successful comparison of dates before pasting the corresponding first time into the timecard. Its just a starting point.
- feedback on this whole process and where, if I can condense and simplify code and all these processes that are performed. And if this is the best way to achieve my goals?
- the exceptions in the example .csv worksheets 1, 2, & 3 that I uploaded.
- added comments in code where I am unsure if lines are necessary or better way to do something, assistance welcomed
- I said “what I think I need help doing” because maybe I am not communicating my needs correctly?
- maybe I need to just forget the actual times and just copy over the total time before and after lunch if the dates match? total time in the “Sub Hour” field can be used for this purpose if typical punch is performed, but not sure how to do this with exceptions involved.
Exceptions I cannot figure out (and they are many):
- the exceptions are the messiest and hardest part for me to figure out how to compare/total up dates/times and include adjustments to produce a single time value to copy into target file and enter total time in hours.
Exceptions occur when a manager has to manually modify an employees timecard data online, which adds InFlags/OutFlags and Notes based on what occurred.
- The example exceptions I found that need to be accounted for:
I will have to post the exceptions I find in the three .csv files in the comments after I take a better look at them. I think if you can see how the system works you might be able to figure them out.
And lastly a minor question: once completed will this macro be able to work in excel 2010 too?
Code I’ve done up to this point is below. Its around 200 lines so far. Apologies for the long winded explanation. I’ve been a lurker on this site for long enough to know detail can be appreciated when trying to assist others such as me. Thank you for your assistance and genius and please ask any clarifying questions as needed!
Code:
Sub PaychexImport()
'
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As Variant
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
'
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
'
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(ActiveSheet.Name)
'
' selects first date to compare in timesheet
targetSheet.Range("A14").Select
targetSheet.Range("A14").Activate '---not sure if this line is necessary
'
' get timecard data from customer workbook
filter = "PayChex Data (*.csv),*.csv"
caption = "Select PayChex Data Source to Import"
ChDrive ("H")
ChDir ("H:\Timecards")
customerFilename = Application.GetOpenFilename(filter, 1, caption, , False)
If customerFilename = False Then
' cancel was pressed
MsgBox "No file was selected"
Exit Sub
Else
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
End If
'
' FORMAT THE DATA IN TIMECARD WORKSHEET
'
' convert string values to time format {hh:mm am/pm}
ActiveSheet.Range("M2").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell = Format(ActiveCell, "[$-409]h:mm AM/PM;@")
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
'
ActiveSheet.Range("U2").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell = Format(ActiveCell, "[$-409]h:mm AM/PM;@")
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
'
' format column type to time
Columns("M:M").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Columns("U:U").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
'
' round “Time In” & “Time Out” to nearest 15 minute using 7/8 split to match system rounding
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
'
Columns("W:W").Select
Selection.Insert Shift:=xlToRight
'
ActiveSheet.Range("N2").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]*96,0)/96"
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
'
ActiveSheet.Range("W2").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell.FormulaR1C1 = "=TIME(HOUR(RC[-1]), ROUND((MINUTE(RC[-1])/60)*4, 0) * 15, 0)"
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
'
' replace formulas with values (using paste special)
Columns("N:N").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AC1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("N2").Select
'
Columns("V:V").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AC1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("V2").Select
'
' divide "STD" time (minutes) into "Sub Hours" time (hours)
Columns("AE:AE").Select
Selection.Insert Shift:=xlToRight
'
ActiveSheet.Range("AE2").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell.FormulaR1C1 = "=RC[-1]/60"
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
'
' format column to number format {#.##}
Columns("AE:AE").Select
Selection.NumberFormat = "0.00"
'
' replace total time with total time values (using paste special)
Columns("AE:AE").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AE1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("AE2").Select
'
' rename column headers for reference clarity
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Sub Hours"
'
Range("W1").Select
ActiveCell.FormulaR1C1 = "Rnd TimeOut"
'
Range("V1").Select
ActiveCell.FormulaR1C1 = "Time Out"
'
Range("S1").Select
ActiveCell.FormulaR1C1 = "Date Out"
'
Range("N1").Select
ActiveCell.FormulaR1C1 = "Rnd TimeIn"
'
Range("M1").Select
ActiveCell.FormulaR1C1 = "Time In"
'
Range("K1").Select
ActiveCell.FormulaR1C1 = "Date In"
'
' center columns
Columns("M:N").Select
With Selection '---is there any reason to have these extra
'---lines of code if below example works too?
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'
' center columns -- this works too
Columns("V:W").Select
With Selection
.HorizontalAlignment = xlCenter
End With
'
' center columns -- this works too
Columns("AD:AE").Select
With Selection
.HorizontalAlignment = xlCenter
End With
'
' fit columns
Columns("M:N").EntireColumn.AutoFit
Columns("V:W").EntireColumn.AutoFit
Columns("AE:AE").EntireColumn.AutoFit
'
'
' IMPORT/COPY DATA INTO TIMECARD WORKBOOK
'
'---all--code--below--this--line--is--a--placeholder--only-----
'
'
' copy "Time In" data from customer to target workbook
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
'
'---currently copies first cell successfully, but need to modify to read & compare rest of dates
sourceSheet.Range("K2").Select
sourceSheet.Range("K2").Activate '---again, not sure if this line is necessary
Do Until IsEmpty(ActiveCell.Offset(, -1))
If sourceSheet.Range("K2").Value = targetSheet.Range("A14").Value Then
targetSheet.Range("B14").Value = sourceSheet.Range("M2").Value
End If
' move down to the check the next date and compare again
ActiveCell.Offset(1, 0).Select
Loop
'
'
'
' copy "Time Out" data from customer to target workbook
'
'
'
' verify "Sub Hour" match, if YES, do nothing, if no, COPY "Sub Hours" data
'
'
'
' Close customer workbook (timecard data) without saving changes
customerWorkbook.Close False
'
End Sub
Last edited by a moderator:
