Still need help with this one: Template Wizard Macro Recordi

JeremyinCanada

New Member
Joined
Apr 5, 2002
Messages
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?!? :)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top