Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA Project - Immediate Window (2000)

    Hi once again,

    I am using a command in an Immediate Window in the VBA Project screen which deletes unwanted characters from data that is imported from an external data source.

    My question is...Is there a way to have the command in the immediate window perform it's function at the same time as when the data is refreshed? Right now I have to open the VBA window, go to the immediate window, put the cursor next to the command and hit enter for it to work. I would like it to do it automatically.

    Is this possible? Thank you in advance.

    -tmg9671 <img src=/S/flags/Ohio.gif border=0 alt=Ohio width=30 height=18> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Project - Immediate Window (2000)

    You could combine the importing or refreshing of the data and the command you execute in a macro. For ease of use, you can assign this macro to a custom toolbar button and/or akeyboard shortcut.

    If you provide more details, we might be able to give more specific advice.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Project - Immediate Window (2000)

    Thanks for the very quick response...when the workbook is opened, a criteria box immediately opens for criteria to run the query to get the data from the external data source, so this already automatically occurs.

    I tried to include the command in the immediate window within a macro, but it would not record. What else can I do? Thanks.

    -tmg9671

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Project - Immediate Window (2000)

    Where does the criteria box come from? There are two types of macro that will be executed when the workbook is opened:
    - A macro named Auto_Open in a standard module.
    - A macro named Workbook_Open in ThisWorkbook
    Can you find either of these? If so, what are the contents?

  5. #5
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Project - Immediate Window (2000)

    The criteria box comes from Microsoft Query. I cannot find the Auto_Open or Workbook_Open marcros.

    -tmg9671

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Project - Immediate Window (2000)

    Apparently, you have a parameter query that retrieves the data, and Automatic Refresh has been set. Any automatic macros execute before the query is refreshed, so you can't use that. You will have to start a macro (from a toolbar button or keyboard shortcut) after the data have been refreshed.

    (Are there Loungers who know how the QueryTable_AfterRefresh event works?)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA Project - Immediate Window (2000)

    The code goes in a class module. This MS Article is pretty straightforward. I played with it and got the Before and AfterRefresh to fire fine.

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Project - Immediate Window (2000)

    Thanks, Steve! The poster (tmg9671) can use this to execute whatever command (s)he now runs from the immediate window, I hope.

  9. #9
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Project - Immediate Window (2000)

    Thank you all for your help. It is greatly appreciated!!!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    -tmg9671

Posting Permissions

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