can a vba be written to start another macro if a triggering event has happened?

I'm using the following macro to close out error messages which occasionally pop up during data queries:

Please Login or Register  to view this content.

A typical error might say "[expression.error The column of "Date" of the table wasn't found.]. Closing this error message appears to work... because I see the circling icons in the data queries indicating that the query is stuck. If I manually close out the error message, the tables will not refresh even though it looks like they are (error closes out. circles appear again briefly. looks like all is well. but its not. all of the tables have not been refreshed). So.. is there a method where the macro determines if it has been invoked... and if so, then click the "refresh all"? I know that an alternative solution is to set up another macro to "refresh all" every x minutes or so but this is not practical for my purposes. Thanks.

Yes I do understand your message and that was my initial approach. But I need help in determining the source of these errors, so on July 31 I posted a question here describing one of the errors, but no one has responded to that post.

While waiting for help, I decided to try to discover the solution on my own. The challenge though is that these errors are not specific to an particular query (I have multiple queries). I changed various queries and their settings, but these attempts didn't work.

As I was going through these procedures though, I noticed that every time I closed out an error message to try to dig in and find a solution, the spinning circles stopped and there were no yellow messages (on the queries themselves) indicating that data hadn't loaded. So I thought all of the tables were updated. I guessed that the errors could have been due to network delays or an internal bug in Excel (overwhelmed with too much work at once) or something similar.

But.... I was wrong in my assumption. So if anyone knows how to trace a db query error message to its source, or help me force a "refresh" after an error, I'll appreciate some guidance. (forcing a refresh always works... thus the purpose of my post here

Thanks

Thanks Zero. Here's my original post which shows one error:

orig post

Here's a screenshot of another error.
exampleerror2.png
Could a problem possibly be that I have some queries loading to rows below another query?

Note that most of the time if I see an error, three of the data queries are "spinning," not just one. When I close out the error message to begin diagnosing the problem, all of the circles stop spinning, so I don't know which one was causing the issue. I could quickly recreate the queries in separate notebooks to see where errors occur. Does that sound like a reasonable starting point? And/or I could paste the actual query here (as found in the Edit console) if that helps.

All of the queries are web based. I use the Excel feature to paste in a URL, then I go to Edit, and will often remove or save columns using the edit GUI.

Thanks Teddi. So is this the correct syntax? (I'm stupid with VBA!)

Please Login or Register  to view this content.

Hmm..... in the workbook there are three dynamic tables with a date (or Date) column. I do not manually label any of the column headings in these tables.

Two of the dynamic tables with dates have headings which are pulled from the web query itself which Excel is creating via the "get data from web" function.
The third table (which begins at column H) is populated from a MySql database. I checked the database itself to see how that column is labeled and it has a small "d" date (and again, I do not manually label the column. The heading is pulled from the db).

I think at this point I'm going to create three independent workbooks, each with a single web query and see what happens. Thanks for sticking with me on this!

If that is what you need then that is correct.
keep in mind to change FileName and Macroname into the file name and macro you want to run when an error occurs.

Originally Posted by jrtaylor

Thanks Teddi. So is this the correct syntax? (I'm stupid with VBA!)

Please Login or Register  to view this content.

Hi Zero, I created the table using Excel's "get data from MySql" feature. I begin by entering the db's credentials. Excel makes the connection and shows me the tables in the db to choose from. I click the one I want and that creates the basic query. I click "edit" on the query which opens the Power Query Editor. Since I only want to keep the last x rows of the data (which goes back maybe 5 years) I click on the "Keep Rows" button and keep the bottom 30 rows. Then I "Load to" cell H1 and the table populates on the worksheet. (And on the other tables which obtain data from the web, I go through the same process, except for the first step. Instead of using the "get data from MySql" feature, I use "get data from web")

This is the actual query, (I obfuscated db names but all else remains same).

I can send a screenshot of the actual mysql schema if that helps.

Please Login or Register  to view this content.

And here's a screenshot of the powerquery editor

powerqueryeditor.png

Thank you