I need help creating a procedure that will first create, and then update on a regular basis, a table in Access via information in an Excel file. I already know how to link Access to Excel info, but this is going to be a bit more specific.
Description of Excel File:
I have a file with four worksheets. I'm only concerned with one. On that one particular worksheet the first 4 rows mean absolutely nothing to me (titles) and will need to be kept out of the import. There are about 18 columns with column headings starting in the 5 row. I am only concerned with columns 1,14, & 15. Those will become the fields in the new table. The rows are where this begins to get more difficult for me. Basically the first column servers multiple purposes. We have a group of projects that we are keeping track of different types of costs for (again I'm only interested with costs in columns 14 &15), but we have several different companies working on each project collecting costs. So rows in column 1 are organized like this: first 10 or so rows starting in row 6, include the names of the companies working on a project. The row after then last company would then be the project name. The columns to the right of the company names are where the costs by company are recorded. Rows to the right of the project name are where those costs are totaled by project. Then at the next row (under the last project total row) we start with a new list of companies for project 2, and so on down the worksheet.
Basically, I am only interested with the total costs by project (sum of all companies' costs) for two types of costs (columns 14 & 15). My table then would have a project name field, a cost 1 field, and a cost 2 field. The problem is that a new worksheet is published each month with cumulative costs (which is what I want), but I don't want to manually enter these costs for each project in the database table. I want my database to be able to link to this sheet and grab only those costs associated with the project totals, and disregard company totals and costs not in the column 14 or 15 totals. Problem 2 is that this worksheet is somewhat dynamic. It is published by a HQ group that likes to change things occasionally. They will add columns and rows (projects and companies) during the year.
A couple of good things however, are that the name of the cost columns that I want never change, and the formatting of the project totaling rows are always the same. So the column letters and row numbers for the info I need each month could change, but the row 5 column names for column 14 & 15 don't change and the fact that project total rows are always bold, a certain size font, and underlined in a certian way never change (not sure if that can help at all, but it's a thought).
My short question after the long description is.....can I build an intelligent process in Excel or (preferably) Access that will know which rows contain my project totals and which column contain the cost data I need for each project row and have my table updated each month with the new cumulative totals? Wow, that's a long one. Sorry about that. Any help would be greatly appreciated.
Description of Excel File:
I have a file with four worksheets. I'm only concerned with one. On that one particular worksheet the first 4 rows mean absolutely nothing to me (titles) and will need to be kept out of the import. There are about 18 columns with column headings starting in the 5 row. I am only concerned with columns 1,14, & 15. Those will become the fields in the new table. The rows are where this begins to get more difficult for me. Basically the first column servers multiple purposes. We have a group of projects that we are keeping track of different types of costs for (again I'm only interested with costs in columns 14 &15), but we have several different companies working on each project collecting costs. So rows in column 1 are organized like this: first 10 or so rows starting in row 6, include the names of the companies working on a project. The row after then last company would then be the project name. The columns to the right of the company names are where the costs by company are recorded. Rows to the right of the project name are where those costs are totaled by project. Then at the next row (under the last project total row) we start with a new list of companies for project 2, and so on down the worksheet.
Basically, I am only interested with the total costs by project (sum of all companies' costs) for two types of costs (columns 14 & 15). My table then would have a project name field, a cost 1 field, and a cost 2 field. The problem is that a new worksheet is published each month with cumulative costs (which is what I want), but I don't want to manually enter these costs for each project in the database table. I want my database to be able to link to this sheet and grab only those costs associated with the project totals, and disregard company totals and costs not in the column 14 or 15 totals. Problem 2 is that this worksheet is somewhat dynamic. It is published by a HQ group that likes to change things occasionally. They will add columns and rows (projects and companies) during the year.
A couple of good things however, are that the name of the cost columns that I want never change, and the formatting of the project totaling rows are always the same. So the column letters and row numbers for the info I need each month could change, but the row 5 column names for column 14 & 15 don't change and the fact that project total rows are always bold, a certain size font, and underlined in a certian way never change (not sure if that can help at all, but it's a thought).
My short question after the long description is.....can I build an intelligent process in Excel or (preferably) Access that will know which rows contain my project totals and which column contain the cost data I need for each project row and have my table updated each month with the new cumulative totals? Wow, that's a long one. Sorry about that. Any help would be greatly appreciated.
