Log In
Register
Home
Active Member
April 20, 2019
Offline
I have a weird problem involving Power Query, VBA and (I think) the Excel User Interface.
I have a single cell named "Search" in an Excel sheet. It is used as a search box. It is a source for a Power Query.
The associated query returns a table to the same sheet. The table is named "tList". The table entries look like this:
=HYPERLINK("\\NAS\Movies-name that contains search argument1.mp4")
=HYPERLINK("\\NAS\Movies-name that contains search argument2.mp4")
etc...
(Note that there is no leading apostrophe involved, nor is one needed.)
A button below the "Search" cell invokes a simple two line macro. The macro is:
Sub SearchReplaceEq()
ActiveWorkbook.RefreshAll 'Invoke the Power Query to do the search
[tList].Replace What:="=", Replacement:="=", LookAt:=xlPart 'Make the =Hyperlink become a formula.
End Sub
Pretty straight forward, right? Here's what happens:
The Power Query works and returns the table as expected.
The VBA Replace statement appears to work, but does not!
If however, we now do a Find & Replace in the Excel UI (Ctrl+H) to make the same change of = sign to = sign, the =HYPERLINKs do become formulae, as expected. The hyperlinks are clickable and work. Similarly, if I invoke another macro with just the single Replace command in it, it also works.
It seems that the Replace function will not actually work until you have returned to the Excel UI from the SearchReplaceEq macro, and then either manually performed the replace = sign (Ctrl+H) or run a macro that only contains the Replace command. But the Replace does not work when it immediately follows the ActiveWorkbook.RefreshAll command in the macro shown above.
What is going on?
I've spent hours trying to resolve this. Obviously without success. (Sigh)
Thanks in anticipation
Les
See Answer
October 5, 2010
Offline
Hi Les,
Can you provide the workbook please.
Phil
Active Member
April 20, 2019
Offline
Hi Philip,
Certainly. It's a little different from my simplified description of the problem, but not that much.
You'll need to point Source1 in the Query to a set of folders containing filenames (mp4) to search.
I realise that such a search could be done more simply with Windows File Explorer. I'm teaching myself Power Query and this is just a simple task I've generated to help learn it. I'm really enjoying Power Query. And then this bug appears and although there are workarounds, it still shouldn't happen.
Thanks
October 5, 2010
Offline
Hi Les,
It's a timing issue. The VBA is working, but when you do ActiveWorkbook.RefreshAll VBA doesn't wait for the PQ query to do its thing. It goes right on to the next line of code and executes [tList].Replace What:="=", Replacement:="=", LookAt:=xlPart
PQ then returns the results and overwrites the values in tList.
If you step through the VBA using F8, you'll see that the code is working correctly.
You can read more about debugging VBA
Cheers
Phil
Answers Post
Active Member
April 20, 2019
Offline
Hi Phil,
Yes, that makes sense. I had tried putting a DoEvents in the macro after the RefreshAll, but that had no affect.
What does work is to uncheck the "Enable background refresh" option in the Query Properties.
Very simple, as so many bugs are.
Thank you.
Have a great Easter.
Les
1 Guest(s)
