I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
Last edited:
