Howdy from Oklahoma!!

I am not an excel person so i am having trouble with this situation.

We have a program that builds a .csv file with our payroll data that is sent
to our home office for importing into the corporate payroll system.

Now for the situation, our home office has made some changes to the
accounting system and the charge# (i.e. 77074) that we used to identify what
account the payroll time needed charged to. this charge# now is a 8-digit
number with a '850' added to the original charge# so it now it should look
like this "85077074" with the 850 in front of the original 77074.
Is there any way I can build a macro or some other wizard that i can create
for our payroll people here that can take the column that has the charge# and
do something like adding 85000000 to each charge# in the column? or if it
can just add the 850 to the beginning of each charge#???

Again, I am not an excel person so this may be a Simple Fix but i just do
not know enough about excel to set up something like this...

As Always, THANKS IN ADVANCE!!!!
Chip

format cell as: 85000000


"Chip" wrote:

Howdy from Oklahoma!!

I am not an excel person so i am having trouble with this situation.

We have a program that builds a .csv file with our payroll data that is sent
to our home office for importing into the corporate payroll system.

Now for the situation, our home office has made some changes to the
accounting system and the charge# (i.e. 77074) that we used to identify what
account the payroll time needed charged to. this charge# now is a 8-digit
number with a '850' added to the original charge# so it now it should look
like this "85077074" with the 850 in front of the original 77074.
Is there any way I can build a macro or some other wizard that i can create
for our payroll people here that can take the column that has the charge# and
do something like adding 85000000 to each charge# in the column? or if it
can just add the 850 to the beginning of each charge#???

Again, I am not an excel person so this may be a Simple Fix but i just do
not know enough about excel to set up something like this...

As Always, THANKS IN ADVANCE!!!!
Chip

WORKED GREAT!!!

THANKS!!!!

"Teethless mama" wrote:

format cell as: 85000000


"Chip" wrote:

Howdy from Oklahoma!!

I am not an excel person so i am having trouble with this situation.

We have a program that builds a .csv file with our payroll data that is sent
to our home office for importing into the corporate payroll system.

Now for the situation, our home office has made some changes to the
accounting system and the charge# (i.e. 77074) that we used to identify what
account the payroll time needed charged to. this charge# now is a 8-digit
number with a '850' added to the original charge# so it now it should look
like this "85077074" with the 850 in front of the original 77074.
Is there any way I can build a macro or some other wizard that i can create
for our payroll people here that can take the column that has the charge# and
do something like adding 85000000 to each charge# in the column? or if it
can just add the 850 to the beginning of each charge#???

Again, I am not an excel person so this may be a Simple Fix but i just do
not know enough about excel to set up something like this...

As Always, THANKS IN ADVANCE!!!!
Chip

Hi Chip:

The solution works nicely if the cells are in the same position each month
and opening and closing the csv file does not casue any problems.

Another way is to use a macro and do a replace and replace all the accounts
with the new accounts but you have to be careful that you don't replace
amounts or employee numbers etc.

As you didn't give an example of the data it is a little difficult to comment.

I would actually prefer a macro that reads the file processes it and then
saves it again with a parameter sheet specifing the requirements.

If the file is a fixed format file then you could also use a shadow file ie

in the shadow file you link each cell to the original file and then adjust
the accounts that need to be adjusted and then you just relink the file each
month and save as csv.

Hope it's given you some ideas.
--

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Teethless mama" wrote:

format cell as: 85000000


"Chip" wrote:

Howdy from Oklahoma!!

I am not an excel person so i am having trouble with this situation.

We have a program that builds a .csv file with our payroll data that is sent
to our home office for importing into the corporate payroll system.

Now for the situation, our home office has made some changes to the
accounting system and the charge# (i.e. 77074) that we used to identify what
account the payroll time needed charged to. this charge# now is a 8-digit
number with a '850' added to the original charge# so it now it should look
like this "85077074" with the 850 in front of the original 77074.
Is there any way I can build a macro or some other wizard that i can create
for our payroll people here that can take the column that has the charge# and
do something like adding 85000000 to each charge# in the column? or if it
can just add the 850 to the beginning of each charge#???

Again, I am not an excel person so this may be a Simple Fix but i just do
not know enough about excel to set up something like this...

As Always, THANKS IN ADVANCE!!!!
Chip

Here is a sample of the data as it looks when exported:

PF6 CMDC 204004 350003 70000 4.5
PF6 CMDC 204004 350004 72724 14.85
PF6 CMDC 204004 350004 72803
PF6 CMDC 204004 350004 72803 44.65
PF6 CMDC 204007 350003 70000
PF6 CMDC 204007 350003 70000
PF6 CMDC 204007 350003 70000 53.9

here is what i need the data to look like before subitting to payroll:

PF6 CMDC 204004 350003 85070000 4.5
PF6 CMDC 204004 350004 85072724 14.85
PF6 CMDC 204004 350004 85072803
PF6 CMDC 204004 350004 85072803 44.65
PF6 CMDC 204007 350003 85070000
PF6 CMDC 204007 350003 85070000
PF6 CMDC 204007 350003 85070000 53.9


I am not sure how to set up a Macro, i have never used Macros before, any
assistance would be appreciated

THANKS!!!
Chip



"Martin Fishlock" wrote:

Hi Chip:

The solution works nicely if the cells are in the same position each month
and opening and closing the csv file does not casue any problems.

Another way is to use a macro and do a replace and replace all the accounts
with the new accounts but you have to be careful that you don't replace
amounts or employee numbers etc.

As you didn't give an example of the data it is a little difficult to comment.

I would actually prefer a macro that reads the file processes it and then
saves it again with a parameter sheet specifing the requirements.

If the file is a fixed format file then you could also use a shadow file ie

in the shadow file you link each cell to the original file and then adjust
the accounts that need to be adjusted and then you just relink the file each
month and save as csv.

Hope it's given you some ideas.
--

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Teethless mama" wrote:

format cell as: 85000000


"Chip" wrote:

Howdy from Oklahoma!!

I am not an excel person so i am having trouble with this situation.

We have a program that builds a .csv file with our payroll data that is sent
to our home office for importing into the corporate payroll system.

Now for the situation, our home office has made some changes to the
accounting system and the charge# (i.e. 77074) that we used to identify what
account the payroll time needed charged to. this charge# now is a 8-digit
number with a '850' added to the original charge# so it now it should look
like this "85077074" with the 850 in front of the original 77074.
Is there any way I can build a macro or some other wizard that i can create
for our payroll people here that can take the column that has the charge# and
do something like adding 85000000 to each charge# in the column? or if it
can just add the 850 to the beginning of each charge#???

Again, I am not an excel person so this may be a Simple Fix but i just do
not know enough about excel to set up something like this...

As Always, THANKS IN ADVANCE!!!!
Chip

Chip:

This is the macro:
Option Explicit

Const cszSheetName As String = "Main" ' name of the sheet with the file names
Const cszScrFileCell As String = "C4" ' cell with the name of original file
Const cszDstFileCell As String = "C6" ' cell with the name of the new file
Const cszColumnChange As String = "E:E" ' column to change in E:E format
Const cszNumberFormat As String = "85000000" ' format for the change

' get the name of the file to process
Sub GetFileNameToOpen()
Dim vFileName As Variant
vFileName = Application.GetOpenFilename _
("Head Office Files (*.csv), *.csv")
If vFileName < False Then
ThisWorkbook.Worksheets(cszSheetName).Range(cszScr FileCell) = _
vFileName
End If
End Sub

' get the name of the file to save
Sub GetFileNameToSave()
Dim vFileName As Variant
vFileName = Application.GetSaveAsFilename _
(ThisWorkbook.Worksheets(cszSheetName). _
Range(cszDstFileCell), "Head Office Files (*.csv), *.csv")
If vFileName < False Then
ThisWorkbook.Worksheets(cszSheetName).Range(cszDst FileCell) = _
vFileName
End If
End Sub

' process it
Sub UpdateSubmission()

Dim wb As Workbook
Dim ws As Worksheet
Dim rSrc As Range, rDst As Range
Dim iFileNo As Integer

Set ws = ThisWorkbook.Worksheets(cszSheetName)
Set rSrc = ws.Range(cszScrFileCell)
Set rDst = ws.Range(cszDstFileCell)
'check if src file exists
If Dir(rSrc, 7) = "" Then
MsgBox "The original file is missing, " & _
"please check the name or the file and retry.", _
vbOKOnly, "Error..."
Exit Sub
End If
'check if dst file exists
'if does then ok file name valid
If Dir(rDst, 7) = "" Then
'not exist so check if can name file of name
On Error Resume Next
iFileNo = FreeFile
Open rDst For Output As iFileNo
' if not equal to 0 then error opening file... not there or error
If Err.Number < 0 Then
MsgBox "The revised file folder is incorrect " & _
"or missing, please check the name or the file and retry.", _
vbOKOnly, "Error..."
Err.Clear
Exit Sub
End If
' close it and delete (kill) it
Close iFileNo
Kill rDst
End If
' check if continue
If (vbNo = MsgBox("Are you sure you want to convert " & _
vbCr & rSrc & vbCr & "and save it as " & vbCr & _
rDst, vbYesNo, "Confirmation...")) Then Exit Sub
' turn off warnings
Application.DisplayAlerts = False
' open file
Set wb = Workbooks.Open(ws.Range(cszScrFileCell))
' set the format
wb.Worksheets(1).Columns(cszColumnChange).NumberFo rmat _
= cszNumberFormat
' save it
wb.SaveAs Filename:=rDst, FileFormat:=xlCSV, CreateBackup:=False
' close it
ActiveWindow.Close False
Application.DisplayAlerts = True
' confirm done
MsgBox "Converted " & rSrc & vbCr & "Saved as " _
& rDst, vbOKOnly, "Finished..."
End Sub

'________________________________

Instructions:

1. In a new workbook delee all the sheets except 1.
2. Name that sheet 'Main'.
3. Cell A4=Original File:
A6=Revised File:
4. Select all the cells and color them light grey.
5. Color cell C4 and C6 green.
6. Widen column C to about 1/2 the screen width so you can see the file names
7. You now need to paste the code above into a macro.
Open the macro editor (ALT+F11)
Insert a module (ALT+I M)
Paste the cope above in the module.
8. Go back to excel
9. Make sure the forms toolbar is displayed (right click over the toolbars
and ensure forms is ticked)
10. Click on the 'button' button (on the right of the XYZ) the cursor
changes the a cross and twn draw a box in cell D4 a assign macro box opens
and then select GetFileNameToOpen. and change the text on the button to
'Change' (right click over the button in cell D4.
11. Repeat the above for D6 and GetFileNameToSave
12. Repeat the above for D8 and UpdateSubmission and text 'Run'

Save it and it should work.

If you send me your email to (with no spaces) martin_fishlock @ yahoo . co .uk

I will send you the file.
--

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Chip" wrote:

Here is a sample of the data as it looks when exported:

PF6 CMDC 204004 350003 70000 4.5
PF6 CMDC 204004 350004 72724 14.85
PF6 CMDC 204004 350004 72803
PF6 CMDC 204004 350004 72803 44.65
PF6 CMDC 204007 350003 70000
PF6 CMDC 204007 350003 70000
PF6 CMDC 204007 350003 70000 53.9

here is what i need the data to look like before subitting to payroll:

PF6 CMDC 204004 350003 85070000 4.5
PF6 CMDC 204004 350004 85072724 14.85
PF6 CMDC 204004 350004 85072803
PF6 CMDC 204004 350004 85072803 44.65
PF6 CMDC 204007 350003 85070000
PF6 CMDC 204007 350003 85070000
PF6 CMDC 204007 350003 85070000 53.9


I am not sure how to set up a Macro, i have never used Macros before, any
assistance would be appreciated

THANKS!!!
Chip



"Martin Fishlock" wrote:

Hi Chip:

The solution works nicely if the cells are in the same position each month
and opening and closing the csv file does not casue any problems.

Another way is to use a macro and do a replace and replace all the accounts
with the new accounts but you have to be careful that you don't replace
amounts or employee numbers etc.

As you didn't give an example of the data it is a little difficult to comment.

I would actually prefer a macro that reads the file processes it and then
saves it again with a parameter sheet specifing the requirements.

If the file is a fixed format file then you could also use a shadow file ie

in the shadow file you link each cell to the original file and then adjust
the accounts that need to be adjusted and then you just relink the file each
month and save as csv.

Hope it's given you some ideas.
--

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Teethless mama" wrote:

format cell as: 85000000


"Chip" wrote:

Howdy from Oklahoma!!

I am not an excel person so i am having trouble with this situation.

We have a program that builds a .csv file with our payroll data that is sent
to our home office for importing into the corporate payroll system.

Now for the situation, our home office has made some changes to the
accounting system and the charge# (i.e. 77074) that we used to identify what
account the payroll time needed charged to. this charge# now is a 8-digit
number with a '850' added to the original charge# so it now it should look
like this "85077074" with the 850 in front of the original 77074.
Is there any way I can build a macro or some other wizard that i can create
for our payroll people here that can take the column that has the charge# and
do something like adding 85000000 to each charge# in the column? or if it
can just add the 850 to the beginning of each charge#???

Again, I am not an excel person so this may be a Simple Fix but i just do
not know enough about excel to set up something like this...

As Always, THANKS IN ADVANCE!!!!
Chip