Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Still need help with this one: Template Wizard Macro Recordi

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Post your code...

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?!?


  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also check out:

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

    Someone else is having the same problem...

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •