Still need help with this one: Template Wizard Macro Recordi

[JeremyinCanada]

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

 

[JeremyinCanada]

Still figuring more out..ok, here's the deal:
The add-in controlling the database updates, etc. is WZTEMPLT.XLZ (apparently also known as 3424.xls).
So, for you Excel Wizards out there, from there I need a macro that:
1) opens the menu that gives you the option to UPDATE the database OR just continuing without updating. AND
2) selecting the option of UPDATING the database.

If we can figure those two steps out and write a macro for those based on WZTEMPLT.xla - I can do the rest using the recorder. Who ever can figure this out should win one of those Mr. Excel t-shirts or whatever....this is tough stuff.

 

[JeremyinCanada]

.

 

[Ivan F Moala]

The wizard template isn't exposed until you
set a reference to it in you application.

I couldn't find and Doc on it (doesn't mean there isn't any?)...so I would suggest creating a reference to the Template in your project and seeing if it exposes any methods
that you can use. After you create the
reference, look in the object browser (F2 and select the reference)to see what methods
are exposed.

You will have to experiment with what you see

Good luck

 

[isbister]

I'm having the same problem as JeremyinCanada trying to create a button which will update a database using the Template Wizard. I wish I could email the participants to find out how they *solved* this.

I am a novice at macros and I don't understand how to set a reference to the template in my worksheet to explore Ivan's suggestion below:

The wizard template (WZTEMPLT.XLZ,
apparently also known as 3424.xls)
isn't exposed until you set a reference
to it in your application.

I couldn't find and Doc on it (doesn't
mean there isn't any?)...so I would
suggest creating a reference to the
Template in your project and seeing if
it exposes any methods that you can
use. After you create the reference,
look in the object browser (F2 and
select the reference)to see what
methods are exposed.

Thanks for the help,
Brad Isbister
brad.isbister@verizon.net
Fort Wayne, IN

 

[isbister]

I found a solution by just writing a macro to update the database in the next blank row.

Unlike the add-in, this macro only adds new records and is not capable of appending or editing old records. If you wanted to do that, you could create a similar "edit" macro which used a match routine to find the desired row which I call NextRow.

I attached this code to a button on my form and it works great. The code is located on another thread, http://www.mrexcel.com/board/viewtopic.php?topic=10382&forum=2&4

Thanks to everyone for their help,
and Good Luck

 

[Going Cross Eyed]

Hi - don't know if you've solved this yet but I had the same problem and got round it with this:

Dim AdminPrinter As String
Dim OldPrinter As String
Const File_DB = "COMMON"
Const File_ATW = "WZTEMPLT"
Set CurrWorkbook = ActiveWorkbook
CurrWorkbook.Activate
OldPrinter = Application.ActivePrinter
AdminPrinter = "\londonHP4600N-Admin-1 " & GetNode
Application.Run File_ATW & ".XLA!Commit"
SaveLocation = ""

Hope that helps.

 

[Going Cross Eyed]

I had the same issue and got it to work with this code: hope it helps:

Dim AdminPrinter As String
Dim OldPrinter As String
Const File_DB = "COMMON"
Const File_ATW = "WZTEMPLT"
Set CurrWorkbook = ActiveWorkbook
CurrWorkbook.Activate
OldPrinter = Application.ActivePrinter
AdminPrinter = "\londonHP4600N-Admin-1 " & GetNode
Application.Run File_ATW & ".XLA!Commit"
SaveLocation = "G:"