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:
- A new output file, say Scorecard, is created from a template file
- Read multiple Excel files, stage the data from each Excel workbook into separate worksheets in the application
- Each worksheet of staged data is loaded into separate Power Query queries, usual bits and bobs done, and output back into tables in Excel
- 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
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.
- make sure that screenupdating is on
- sprinkled in a few DoEvents
- added an Application.Wait
- added an extra message box to interrupt the flow
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?
Reply With Quote