• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

=HYPERLINK problem involving Power Query, VBA and the Excel UI |Power Query|Excel Forum|My Online Training Hub

You are here: Home / =HYPERLINK problem involving Power Query, VBA and the Excel UI |Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower Query=HYPERLINK problem involving Power …
sp_PrintTopic sp_TopicIcon
=HYPERLINK problem involving Power Query, VBA and the Excel UI
Avatar
Les Firth
Melbourne

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
April 20, 2019
sp_UserOfflineSmall Offline
1
April 20, 2019 - 12:32 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1516
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
April 20, 2019 - 2:56 pm
sp_Permalink sp_Print

Hi Les,

Can you provide the workbook please.

Phil

Avatar
Les Firth
Melbourne

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
April 20, 2019
sp_UserOfflineSmall Offline
3
April 20, 2019 - 4:47 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1516
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
April 20, 2019 - 5:52 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Les Firth
Melbourne

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
April 20, 2019
sp_UserOfflineSmall Offline
5
April 20, 2019 - 6:08 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Alan Sidman, Ngoc Tinh, Dieneba NDIAYE, Alexandra Radu, Natasha Smith, Monique Roussouw
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.