+ Reply to Thread
Results 1 to 3 of 3

VBA can't update query but can be done manually

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    VBA can't update query but can be done manually

    Hi,

    I have a problem with a query in my workbook, which updates fine when prompted manually by the user, but which fails when being updated from VBA.

    The query is constructed using Power Query to pull the data from a named range "Raw_data" (which expands/contracts dynamically to always match the no. of columns/rows in the dataset) in sheet 1, and looks something like this:

    Please Login or Register  to view this content.
    When updated manually from the Power Query pane / data tab, the query refreshes just fine. However, when updated through VBA with the code below, I get the error "Rune-time error '1004': We couldn't refresh the connection 'Query - Test". Here's the error message we got: [Expression.Error] The key didn't match any rows in the table."

    Please Login or Register  to view this content.
    I am using a similar code for all other queries which work with no problem at all (although they pull data from actual tables rather than named ranges). Previously, the query in question was linked to a table rather than a named range, with the VBA working fine. However, going forward, the query needs to pull data from the named range.

    Does anyone have any suggestions on how to solve the issue?

    Thanks!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,432

    Re: VBA can't update query but can be done manually

    I use the following and it works perfectly for me.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: VBA can't update query but can be done manually

    Thanks alansidman,

    Unfortunately, my workbook contains a bunch of queries, so updating all at once is not a viable option.

    Beside, the ActiveWorkbook.RefreshAll unfortunately does not work either in my workbook. Any other suggestions?

    I did try to make the named range static rather than dynamic, with the ActiveWorkbook.Connections(“Query - Test”).refresh actually workung. However, I need the dynamic part to ensure that not too many rows and columns are included which drags on performance (on dataset has about 5000 rows and 10 columns, another has 500 rows but 150 columns, etc.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Manually changing a chart won't update
    By danfleysher in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-28-2018, 05:49 PM
  2. Macro asking to manually update values
    By kbrown825 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2016, 04:05 PM
  3. Replies: 0
    Last Post: 03-20-2006, 03:48 PM
  4. How to update one manually-entered value from another?
    By 1robc57 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2006, 01:10 AM
  5. I can only manually update?
    By jclark419 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2005, 01:05 PM
  6. Manually Update Listindex
    By DB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2005, 12:05 AM
  7. Manually update links
    By Emma in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-22-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1