Consulting

Results 1 to 7 of 7

Thread: Design Dilemma

  1. #1
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location

    Design Dilemma

    Bit of an odd one, and a bit convoluted to explain, but here goes.

    I have an application where I am determining a KPI for many different measures for many properties, and loading the results into another workbook to produce various analyses.

    The basic logic steps are:
    1. A new output file, say Scorecard, is created from a template file
    2. Read multiple Excel files, stage the data from each Excel workbook into separate worksheets in the application
    3. Each worksheet of staged data is loaded into separate Power Query queries, usual bits and bobs done, and output back into tables in Excel
    4. The Excel tables are then copied across to Scorecard


    This was working okay, or so I thought, until I added a new column, run period, in anticipation of accumulating the data for historical analysis. When I did this I found that when a query returned no data the old data was being used (don't ask, but I have fixed that part).

    However, I now have the problem that step 4 doesn't work anymore, the data just does not copy across. I have no idea as yet as to why the copy does not work, the only thing that makes it work is to stop the code with a breakpoint or a STOP, and then resume the code (F5). I have tried various things to correct this, such as
    • make sure that screenupdating is on
    • sprinkled in a few DoEvents
    • added an Application.Wait
    • added an extra message box to interrupt the flow
    but none of these worked. I tried going back to the previous version, but that also had the problem, I think it could have occurred some time ago but was being masked by the problem I mentioned earlier.

    I then took a different tack, instead of outputting the queries to the application I would direct them to the Scorecard. As the Scorecards would be different each time, I needed to change the query output at runtime, I have code that can supposedly do this. First problem was that I got an error 1004, so I activated the Scorecard and the target KPI sheet and then got an error that it couldn't find the query, it can't reference a query across workbooks.

    I could put all of the queries in the template and run them there, but it has been very useful outputting the results into the application and looking at problems and issues there, same with staging the data.

    One thing that occurred to me was to bypass the staging and read the Excel files directly into Power Query, but the files are in OneDrive, have you ever tried opening a OneDrive file in Power Query?

    So, I am an an impasse. I have to continue looking at why the copy doesn't work, it did originally, but I am now wondering if there is a better solution here that I am not seeing.

    Anyone have any thoughts/ideas?
    Last edited by Bob Phillips; 03-22-2024 at 08:21 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,721
    Location
    Try this

    https://www.vitoshacademy.com/vba-co...y-ghost-break/

    Anyhow, this is called “Ghost Break” and there is a good way to go around it, within 5 seconds. These are the steps:

    • Press “Debug” button in the popup.
    • Press Ctrl+Pause|Break twice.
    • Hit the play button to continue.
    • Save the file after completion.

    And that is enough.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    It's not a ghost break Paul, although I have met those many times in my developments. This is more a case of the code not doing something that it should do, a copy that doesn't work.

    Anyway, I think I have found the solution. It seems my code, which does a query refresh, carried on running whilst the query was still executing, so it was copying a blank table.

    The solution is to turn off Background Refresh for the queries, and add a DoEvents in the code. I need to test some more, but seems good so far.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,721
    Location
    If playing with the timing doesn't provide reliable results, maybe the concept behind

    http://www.appspro.com/Utilities/CodeCleaner.htm


    That only works with 32 bit Office, but you could manually do the same thing

    I'm still using 32 bit office and the cleaner has solved many of my problems
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,242
    Location
    I think that may well be the issue, I have had issues like this in the past but all Excel side. Back then I used the same method to refresh the query before I then refreshed the pivot tables to create an all in one refresh scenario. Below is the code I used in case it is any help:

    Sub RefreshQuery()
        Application.ScreenUpdating = False    With Sheet1.ListObjects("tbProjData")
            .AutoFilter.ShowAllData
            .QueryTable.Refresh BackgroundQuery:=False
            .Sort.SortFields.Clear
            .Range.AutoFilter 3, "<>"
        End With
        Sheet3.ListObjects("tbLastSaved").QueryTable.Refresh BackgroundQuery:=False
        Sheet3.ListObjects("tbStandby").QueryTable.Refresh BackgroundQuery:=False
        Sheet3.PivotTables("PivotTable1").PivotCache.Refresh
        Sheet3.PivotTables("PivotTable2").PivotCache.Refresh
        Sheet3.PivotTables("PivotTable3").PivotCache.Refresh
        Sheet3.PivotTables("PivotTable4").PivotCache.Refresh
        Application.ScreenUpdating = True
    End Sub
    Instead of updating pivots i suppose you would run your copy code.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    Quote Originally Posted by Paul_Hossler View Post
    If playing with the timing doesn't provide reliable results, maybe the concept behind

    http://www.appspro.com/Utilities/CodeCleaner.htm


    That only works with 32 bit Office, but you could manually do the same thing

    I'm still using 32 bit office and the cleaner has solved many of my problems
    Paul,

    When I switched to 64-bit Excel and Code Cleaner ceased working for me, so I wrote my own. It is a simple routine that adds a menu item in the VBIDE, and you can either export all modules, or you can be selective. I could not live without that functionality that Code Cleaner provided.

    I know lots of people have used Code Cleaner to export all their modules and to re-import them when they get odd problems, but that is not why I need(ed) it. The reasons I need it are two-fold, first to save libraries of my code, but also to do a compare across versions. I used to have a program called VBADiff that compared all of the modules, exporting the files and then presenting them to WinMerge. Some years my VBADiff stopped working, and I couldn't contact the developer, so I had to forge my own. If I could find the time to automate WinMerge, it has a command line interface, it would be nearly perfect for me (on the problem I raised here I went back over 15 versions trying to see if I had coded in the problem!!!).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    Quote Originally Posted by georgiboy View Post
    I think that may well be the issue, I have had issues like this in the past but all Excel side. Back then I used the same method to refresh the query before I then refreshed the pivot tables to create an all in one refresh scenario. Below is the code I used in case it is any help:

    Sub RefreshQuery()
        Application.ScreenUpdating = False    With Sheet1.ListObjects("tbProjData")
            .AutoFilter.ShowAllData
            .QueryTable.Refresh BackgroundQuery:=False
            .Sort.SortFields.Clear
            .Range.AutoFilter 3, "<>"
        End With
        Sheet3.ListObjects("tbLastSaved").QueryTable.Refresh BackgroundQuery:=False
        Sheet3.ListObjects("tbStandby").QueryTable.Refresh BackgroundQuery:=False
        Sheet3.PivotTables("PivotTable1").PivotCache.Refresh
        Sheet3.PivotTables("PivotTable2").PivotCache.Refresh
        Sheet3.PivotTables("PivotTable3").PivotCache.Refresh
        Sheet3.PivotTables("PivotTable4").PivotCache.Refresh
        Application.ScreenUpdating = True
    End Sub
    Instead of updating pivots i suppose you would run your copy code.
    Yeah, I think it is. I stumbled across the solution because I found an old app of mine where I was querying a database and I had done as you are doing, setting the BackgroundQuery property to False. In my app though I can set that property of the query in the workbook via the GUI, so it is set at all times to False.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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