Excel 97/VBA (Excel 97 SR1)

[mark8965]

I am using Excel 97. I want to make a popular workbook a template and have set up so that it will save certain contents to Access. This workbook is how our company quotes jobs. We want to save this info so that we can do analysis later. In this workbook, I have a macro that will print it and then clear it. Bottom line is that I want to be able to save the info, print it then clear the contents.

I saw in the Excel help that you can create a template that copies worksheet data to a database. I set up the template and linked the fields in the worksheet to the Access database. I guess that I have to learn more about saving the information to an Access database.
At what point does the information get sent and saved?
Could I automate this task with a macro?
Then I could add it to the macro that print and resets the worksheet.

My problem is that we will do multiple quotes at a time (one after another) and I need to be able to send the data to the database when we are done with a quote, like when the quote is printed.
If the saving is done when it closes, then is there a way to send the data to the database with a macro?


Any ideas?

Thanks,
Mark

PS. I hope that I expalined it ok.

[dave_hill]

Mark-
Excel has to initiate sending the data to Access.
It can be automated with a macro or VBA procedure.
Are they automatic links to the database? Please post the exact name of the Excel Help tip you're using if you need more help.

[mark8965]

Dave,

The name of the Excel help tip is "Create a form template that copies worksheet data to a database".
At the end it talks about getting external data, but nothing else about sending data.

What do you mean by automatic links to the database?

Thanks and yes I need more help.

Mark

[mark8965]

Thanks alot.
I'm gonna read the article and try it out.

Mark

[dave_hill]

The help tip you mentioned works like this:
After opening the Template Wizard, you identify which single cells in the Excel sheet will contain data, and the related field in the Access table that you want them to be associated with.
The Template Wizard creates a template based on your design.
You select File, New and the name of the template to open a workbook based on the template.
When you close or save the workbook, the data in the fields you identified gets written to the table.
This seems to be o.k. for casual data entry, but it's not a very robust solution if you need to add a series of records. Again, you can only select one cell at a time, not a range of cells.
For more information on how to automate this, try the MS Knowledge Base file
"Office Developer Samples and Tools Available for Download" at

<A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q253/3/38.ASP>http://support.microsoft.com/support/kb/ar...s/Q253/3/38.ASP</A>

Edited by Charlotte to add link

[mark8965]

Ok, I'm back.
I got the Excel workbook to write to Access when I click the save button.
I want to automate it so that I can just have a button that will do that.
When I click the save button it asks me, in a dialog box, whether I want to either update (existing record), create ( a new record) or continue.
I tried to do a macro that pushes the appropriate button - create.
But pushing that create button during recording of the macro didn't work.
It doesn't save it at all.

Is there come code that I can put in the macro to do that?

Thanks,
Mark