Still need help with this one: Template Wizard Macro Recordi

JeremyinCanada · Apr 6th, 2002, 01:51 PM

I have designed an input template for an Excel database (using the template wizard). Basically, I want to set up a macro (by pushing a form button)that will automatically update the database.

When I do the save MANUALLY it prompts me for the dialog box asking if I want to 1) update or 2) create a new record. However, when I try to autmote it (after recording the macro) it only SAVES. It does not prompt for how I want to handle the record and THEREFORE DOES NOT update the database. What gives? Any assistance would be of assistance. I can't imagine I'm the only person in the world that can't figure this out...can I?

Was this issue addressed and repaired in a service pack I have yet to download or something? Any help would be appreciated. Has this been fixed with the add-in in Excel 2002?

I did a quick check on a couple of message boards and apaprently there is SOME code out there somewhere floating to fix this...and to force an update to the database. Been working on this since 4PM (2 hours) trying to find a solution....no luck thusfar.

Thank you.

[ This Message was edited by: JeremyinCanada on 2002-04-06 15:09 ]

[ This Message was edited by: JeremyinCanada on 2002-04-07 08:44 ]

Tom Schreiner · Apr 6th, 2002, 04:34 PM

Post your code...

JeremyinCanada · Apr 6th, 2002, 04:46 PM

This is the code associated with the recorded MACRO:
Sub Button6_Click()
'
' Button6_Click Macro
' Macro recorded 06/04/2002 by xxxxxxxx
'
'
ActiveWorkbook.Save
End Sub

As you can see, it is ONLY saving the file in the code. But, when I record the Macro I am actually selecting "update record to database" on another dialog box, but it doesn't actually record that action in the code. All the macro does is SAVE when in fact I need it to UPDATE AND SAVE. The MS website provides no help on this subject ( as far as I can tell).

I've used the Template Wizard "add-in" to develop the "template" sheets that upload to a database as information is saved. When I SAVE manually and choose "Update to database" it works fine. But, when I record with the macro, it doesn't record the update step.

Help?!?

JeremyinCanada · Apr 6th, 2002, 04:47 PM

Also check out:

http://www.mrexcel.com/board/viewtop...1282&forum=2&4

Someone else is having the same problem...

JeremyinCanada · Apr 6th, 2002, 05:24 PM

To get any idea of what I am talking about I woudl suggets putting together a quick spreadsheet/database using the Template Wizard.
Then create a macro designed to save the template and update the database. You will discover that the macro will ONLY save. It won't update the database workbook.

JeremyinCanada · Apr 6th, 2002, 05:45 PM

Have also tried recording the macro using "save as..." and "close..." which also bring up the Dialog Box that allows you to place the record in the database.
No go!
It's as though the Macro doesn't "see" the dialog box that is associated with the Template Wizard with Data Tracking add-in.....very strange!

Jay Petrulis · Apr 6th, 2002, 05:58 PM

Hi Jeremy,

I have never used Excel in this way, but are you trying to grab the new data in the dialog box and then write it to the database?

Is is supposed to go straight to the database? Is it possible to write the fields to a temporary sheet (Sheet1 for instance) and then load it to the next available line in the database? I'm sure that doesn't handle amended records, but may be worth a shot.

Can you record a macro where you only update the database, but do not save? Possibly an intermediate step in between so the code gets generated.

How about the help files, any guidance there?

If I can, I'll try to play with this.

Regards,
Jay

JeremyinCanada · Apr 6th, 2002, 06:06 PM

Hi Jay,

No - the data is in the worksheet. The dialog box simply gives you options on how you want to handle it.
No - there is no way to "update" without 1) saving 2) closing, etc. At least not that I have found.

I just sttarting fiddling with the workbook in VBA editor and noticed there was a 4th "hidden" sheet that has to do with the template wizard.

My suggestion, as before to get the hang of what is going on is to quickly make a very simply template/database using the wizard (it's an add-in).

See what you can come up with. Like I said before, it's like the macro doesn't see the dialog box when I am recording. And since there is no "alternate" means of updating the database....I have no idea. It might take a REAL wizard to figure this one out.

If it would simply record this for me, it would save me a HUGE headache.

Thanks.
Jeremy

JeremyinCanada · Apr 6th, 2002, 06:07 PM

Oh yeah - no help with the help files either. Very very basic on that front...

JeremyinCanada · Apr 6th, 2002, 07:42 PM

Starting fiddling with VBA editor, but I don't have enough experience with it. If you setup using the Template Wizard, I think you will be able to see the types of objects it creates and perhaps the modules it is acutally using to update to the master database. Once those are identified, it should be easy to make up a macro based on the code.

Still plugging away....4 hours later.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 6th, 2002, 01:51 PM	#1
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	I have designed an input template for an Excel database (using the template wizard). Basically, I want to set up a macro (by pushing a form button)that will automatically update the database. When I do the save MANUALLY it prompts me for the dialog box asking if I want to 1) update or 2) create a new record. However, when I try to autmote it (after recording the macro) it only SAVES. It does not prompt for how I want to handle the record and THEREFORE DOES NOT update the database. What gives? Any assistance would be of assistance. I can't imagine I'm the only person in the world that can't figure this out...can I? Was this issue addressed and repaired in a service pack I have yet to download or something? Any help would be appreciated. Has this been fixed with the add-in in Excel 2002? I did a quick check on a couple of message boards and apaprently there is SOME code out there somewhere floating to fix this...and to force an update to the database. Been working on this since 4PM (2 hours) trying to find a solution....no luck thusfar. Thank you. [ This Message was edited by: JeremyinCanada on 2002-04-06 15:09 ] [ This Message was edited by: JeremyinCanada on 2002-04-07 08:44 ]

Apr 6th, 2002, 04:34 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Post your code...

Apr 6th, 2002, 04:46 PM	#3
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	This is the code associated with the recorded MACRO: Sub Button6_Click() ' ' Button6_Click Macro ' Macro recorded 06/04/2002 by xxxxxxxx ' ' ActiveWorkbook.Save End Sub As you can see, it is ONLY saving the file in the code. But, when I record the Macro I am actually selecting "update record to database" on another dialog box, but it doesn't actually record that action in the code. All the macro does is SAVE when in fact I need it to UPDATE AND SAVE. The MS website provides no help on this subject ( as far as I can tell). I've used the Template Wizard "add-in" to develop the "template" sheets that upload to a database as information is saved. When I SAVE manually and choose "Update to database" it works fine. But, when I record with the macro, it doesn't record the update step. Help?!?

Apr 6th, 2002, 04:47 PM	#4
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	Also check out: http://www.mrexcel.com/board/viewtop...1282&forum=2&4 Someone else is having the same problem...

Apr 6th, 2002, 05:24 PM	#5
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	To get any idea of what I am talking about I woudl suggets putting together a quick spreadsheet/database using the Template Wizard. Then create a macro designed to save the template and update the database. You will discover that the macro will ONLY save. It won't update the database workbook.

Apr 6th, 2002, 05:45 PM	#6
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	Have also tried recording the macro using "save as..." and "close..." which also bring up the Dialog Box that allows you to place the record in the database. No go! It's as though the Macro doesn't "see" the dialog box that is associated with the Template Wizard with Data Tracking add-in.....very strange!

Apr 6th, 2002, 05:58 PM	#7
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi Jeremy, I have never used Excel in this way, but are you trying to grab the new data in the dialog box and then write it to the database? Is is supposed to go straight to the database? Is it possible to write the fields to a temporary sheet (Sheet1 for instance) and then load it to the next available line in the database? I'm sure that doesn't handle amended records, but may be worth a shot. Can you record a macro where you only update the database, but do not save? Possibly an intermediate step in between so the code gets generated. How about the help files, any guidance there? If I can, I'll try to play with this. Regards, Jay

Apr 6th, 2002, 06:06 PM	#8
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	Hi Jay, No - the data is in the worksheet. The dialog box simply gives you options on how you want to handle it. No - there is no way to "update" without 1) saving 2) closing, etc. At least not that I have found. I just sttarting fiddling with the workbook in VBA editor and noticed there was a 4th "hidden" sheet that has to do with the template wizard. My suggestion, as before to get the hang of what is going on is to quickly make a very simply template/database using the wizard (it's an add-in). See what you can come up with. Like I said before, it's like the macro doesn't see the dialog box when I am recording. And since there is no "alternate" means of updating the database....I have no idea. It might take a REAL wizard to figure this one out. If it would simply record this for me, it would save me a HUGE headache. Thanks. Jeremy

Apr 6th, 2002, 06:07 PM	#9
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	Oh yeah - no help with the help files either. Very very basic on that front...

Apr 6th, 2002, 07:42 PM	#10
JeremyinCanada New Member Join Date: Apr 2002 Posts: 11	Starting fiddling with VBA editor, but I don't have enough experience with it. If you setup using the Template Wizard, I think you will be able to see the types of objects it creates and perhaps the modules it is acutally using to update to the master database. Once those are identified, it should be easy to make up a macro based on the code. Still plugging away....4 hours later.