May I have some advice, please. I have an Access 2000 database data entry form and report for producing client proposals (a
report will be printed as the proposal). For each record being input into the table form there is a corresponding Excel
workbook that was created from an Excel template with many static worksheets, formulas, functions, etc., for which much
analytical work is done in producing figures for a proposal. The workbooks' spreadsheet named "Cost" has the final figures
for three of the database table/form fields.
Would it be possible to write a macro program in Access whereby the
user is prompted to open the relevant Excel workbook and then the code automatically goes to the spreadsheet named "Cost" and
picks up the values in 3 different static cells, F100, G100, and H100 and transfers the data from these three cells to the
relevant database fields called, say, COST1, COST2, COST3, respectively?
I've used "frmCommon" before in a
Microsoft Word program to open an Excel workbook and was wondering if it can be used in Access code (I've not done any
programming in Access!):
On Error GoTo ErrorHandler
Dim strDatabaseName As String
'strDatabaseName = InputBox("Enter the full path name of your data source file, e.g., C:My Documentsdata.doc.")
frmCommon.comdialogSave.DialogTitle = "Open Your Estimate Spreadsheet"
strDatabaseName = frmCommon.comdialogSave.FileName
If strDatabaseName = "" Then
Any advice on how one would approach this task would be much appreciated. Many thanks!