stokeboy86
New Member
- Joined
- Dec 15, 2014
- Messages
- 6
Hi,
New member so hope I am posting in the correct manner?!
I am using Excel 2013 on Windows 7.
I am trying to create a macro to automate a routine for a business costing process. To summarise:
We are a printing company and can run machine reports that summarise the jobs the jobs produced in a specific time period; these contain data such as:
· Machine serial number (to identify the specific machine)
· Job number
· Consumable costs
· Time on press
This data then needs to be sorted into a format that can then be imported into our management system (this handles the estimating/costing etc.) so the costs of the jobs can be recorded without manual intervention or user input.
We separate the labour and material costs as 2 x separate imports, so the ideal situation would be an Excel sheet with 4 x tabs:
1. Data - this is the machine data that will be copied in from a machine report
2. Labour - after the macro is run the labour elements will be copied into this worksheet and then subsequently saved to a specific network location
3. Material - after the macro is run the material elements will be copied into this worksheet and then subsequently saved to a specific network location
4. Exception - when the macro is run, each row that does not contain specific criteria (that means it cannot be imported into the management system) will be copied into this sheet for manual inspection
The machine data will be presented with the following headers (not all are required):
<tbody>
</tbody>
There are several "curveballs" that need to be ironed out first:
· The "job name" will be a file name that will have the following syntax: "job number"_"job description"."file extension" (e.g. 123456_brochure.pdf). The critical information is the job number and this will always be the first 6 numbers, if this is not contained, the import will not work, so the macro must isolate the 1st 6 characters, and if these are not numbers, copy these rows to the "exception" worksheet. The remaining rows that do have 6 x numerical characters as the 1st 6 x digits can be assumed to be the correct job number and can then be sorted.
· The "printed date" will be supplied in US format (e.g. mm/dd/yyyy) but needs to be imported in UK format (e.g. dd/mm/yyyy)
· The "net print time" is supplied as hh:mm:ss but needs to be converted to decimal hours for the import.
When the macro runs, it will need to first interrogate the data and move all rows that do not have their first 6 x characters as numbers into the "exception" sheet. It will then need to somehow amend the data to the correct format as specified above. The data will then need to be copied into both the labour and material worksheets with the following format:
Labour
Column 1 = Job No (1st 6 numbers of the "job name" field in the data sheet)
Column 2 = Date (e.g. the "printed date" converted from mm/dd/yyyy to: dd/mm/yyyy)
Column 3 = Cost Centre (this will be the Press ID and when imported into the management system will calculate the correct cost rate. At the moment we are only looking at 2 x cost centres, so if the "Press ID"=30005413, the cost centre should be 0813, if the "Press ID"=45000591, the cost centre should be 0812.
Column 4 = Operation (this can default to "2")
Column 5 = Operator (this can default to "160")
Column 6 = Time (this needs to be the "net print time" converted to decimal hrs)
Material
Column 1 Job No = (1st 6 numbers of the "job name" field in the data sheet)
Column 2 Date = (e.g. the "printed date" converted from mm/dd/yyyy to: dd/mm/yyyy)
Column 3 Cost Centre (this can default to 1006)
Column 4 Supplier (this can default to INDI02)
Column 5 Order Number (this can be left blank)
Column 6 Cost (this is the "separations" multiplied by 0.00675)
Column 7 Description
The final part of the macro routine will then save the labour and material worksheets as separate .csv files into different specific network locations (same location and file name each time - when the management system import collects the .csv file, it will delete it). The user who runs the routine will then be left with the exception report which will essentially be a list of printed jobs that do not have job numbers. These can then be investigated and the correct file names be manually added to the next import.
I have an example worksheet that contains some sample data in the data worksheet, and this has been distributed to the separate work sheets as if the macro has run as an example - however I cannot see an option to upload?
If anyone can help, this would be greatly appreciated! Many thanks in advance.
Regards,
Chris
New member so hope I am posting in the correct manner?!
I am using Excel 2013 on Windows 7.
I am trying to create a macro to automate a routine for a business costing process. To summarise:
We are a printing company and can run machine reports that summarise the jobs the jobs produced in a specific time period; these contain data such as:
· Machine serial number (to identify the specific machine)
· Job number
· Consumable costs
· Time on press
This data then needs to be sorted into a format that can then be imported into our management system (this handles the estimating/costing etc.) so the costs of the jobs can be recorded without manual intervention or user input.
We separate the labour and material costs as 2 x separate imports, so the ideal situation would be an Excel sheet with 4 x tabs:
1. Data - this is the machine data that will be copied in from a machine report
2. Labour - after the macro is run the labour elements will be copied into this worksheet and then subsequently saved to a specific network location
3. Material - after the macro is run the material elements will be copied into this worksheet and then subsequently saved to a specific network location
4. Exception - when the macro is run, each row that does not contain specific criteria (that means it cannot be imported into the management system) will be copied into this sheet for manual inspection
The machine data will be presented with the following headers (not all are required):
| Press ID | Job Name | Starts | Sheets | Separations | Copies | Duplex | Printed Date | Total Elapsed Time | Net Print Time |
<tbody>
</tbody>
There are several "curveballs" that need to be ironed out first:
· The "job name" will be a file name that will have the following syntax: "job number"_"job description"."file extension" (e.g. 123456_brochure.pdf). The critical information is the job number and this will always be the first 6 numbers, if this is not contained, the import will not work, so the macro must isolate the 1st 6 characters, and if these are not numbers, copy these rows to the "exception" worksheet. The remaining rows that do have 6 x numerical characters as the 1st 6 x digits can be assumed to be the correct job number and can then be sorted.
· The "printed date" will be supplied in US format (e.g. mm/dd/yyyy) but needs to be imported in UK format (e.g. dd/mm/yyyy)
· The "net print time" is supplied as hh:mm:ss but needs to be converted to decimal hours for the import.
When the macro runs, it will need to first interrogate the data and move all rows that do not have their first 6 x characters as numbers into the "exception" sheet. It will then need to somehow amend the data to the correct format as specified above. The data will then need to be copied into both the labour and material worksheets with the following format:
Labour
Column 1 = Job No (1st 6 numbers of the "job name" field in the data sheet)
Column 2 = Date (e.g. the "printed date" converted from mm/dd/yyyy to: dd/mm/yyyy)
Column 3 = Cost Centre (this will be the Press ID and when imported into the management system will calculate the correct cost rate. At the moment we are only looking at 2 x cost centres, so if the "Press ID"=30005413, the cost centre should be 0813, if the "Press ID"=45000591, the cost centre should be 0812.
Column 4 = Operation (this can default to "2")
Column 5 = Operator (this can default to "160")
Column 6 = Time (this needs to be the "net print time" converted to decimal hrs)
Material
Column 1 Job No = (1st 6 numbers of the "job name" field in the data sheet)
Column 2 Date = (e.g. the "printed date" converted from mm/dd/yyyy to: dd/mm/yyyy)
Column 3 Cost Centre (this can default to 1006)
Column 4 Supplier (this can default to INDI02)
Column 5 Order Number (this can be left blank)
Column 6 Cost (this is the "separations" multiplied by 0.00675)
Column 7 Description
The final part of the macro routine will then save the labour and material worksheets as separate .csv files into different specific network locations (same location and file name each time - when the management system import collects the .csv file, it will delete it). The user who runs the routine will then be left with the exception report which will essentially be a list of printed jobs that do not have job numbers. These can then be investigated and the correct file names be manually added to the next import.
I have an example worksheet that contains some sample data in the data worksheet, and this has been distributed to the separate work sheets as if the macro has run as an example - however I cannot see an option to upload?
If anyone can help, this would be greatly appreciated! Many thanks in advance.
Regards,
Chris
