I had a thought on this ...... could it be that Code 1 is for importing from a closed workboook and Code 2 is for importing from an open workbook???

..... or could it be that Code 1 workbook does not have a password and Code 2 workbook does ...... does PQ treat them differently?

Hi Graham,

Code 1 is referencing a sheet name and code 2 is referencing a named range.

Mynda

Hi Mynda,

Thank you for the reply. Now you've enlightened me on that I can see it makes complete sense from reading the code 🙂

However, I think there is still a missing piece of the puzzle as I don't have a Named Range that I have defined.  The source workbook is password protected and has to be open for PQ to access the data in it. I think PQ must be dealing with password protected workbooks slightly differently to non-password protected workbooks. I need to go and do some more research on this. If I get to the bottom of it I will post back here.

I also need to look into why password protected files are much slower to retrieve data from as I think this is linked to my issue.

Thanks again ...... I love this site!

Graham

Hi Mynda, 

So I've found out a little bit more. Password protected workbooks appear to be imported differently. If I look at the Power Query Editor at the first step ....  

      Source = Excel.Workbook(File.Contents(myFile), null, true)

..... then I get different results depending on if the workbook has a password or not. I have attached screenshots of both.

I guess this is just how it is designed to work??
It's incredibly slow to import the data ...... about 6 seconds for the non-password protected file .... and about 10 minutes for the password protected file!!

Do you know if there is anything different I can do when dealing with password protected files?

Thanks,

Graham

Hi Graham,

TBH I've never paid a lot of attention to the syntax of the Excel.Workbook function as this is something Power Query typically codes for you automatically. So, after some more research I think the difference between your two queries might be this:

Code 1 was built by Power Query automatically and Code 2 was built manually. In my testing both versions of the code return the same thing, so I suspect the reason one query is slow compared to the other is due to differences in the two files you're connecting to, rather than the code.

I doubt it has anything to do with the password protection. Your code is looking for data on a sheet that isn't in a table or named range. If these sheets have artifacts lingering in them (cells that used to have data/formatting in them that wasn't properly deleted), then this could be slowing down the query. Or maybe it's just a difference in the structure of the file that Power Query has to sift through to find what you want. 

I'd test for artifacts in the worksheet with the keyboard shortcut CTRL+END and if the cell you land in is not the end of the data, then that means there are cells in the file that appear, from Excel's point of view, to still contain data. You can try deleting all rows and columns outside the data range to see if that clears them, although in my experience this doesn't always help.

Mynda

Hi Mynda,

I did already test for artifacts in the worksheet using CTRL+END and that didn't show any anomalies.

Using Code 2 only:
My run time differences are based on the SAME file, once with it being password protected and once without. It really does seem to be that the slow speed is down to the file being being password protected!!

Thanks for your input on this.  I'll mark your original reply (post 3) as the answer to this thread as that gives the answer to the difference between Code 1 and Code 2 🙂

I'm still intrigued on the password bit though!

Thanks

Graham

Hi Graham,

Thanks for clarifying that the tests were done on the same file. One thing to confirm, you cannot get data from an open Excel workbook, so that isn't a possibility for the different code or slow down in the file. It would appear it's down to the password protection. I'll do some testing at my end if I get time today and see if I get the same results.

Mynda

Hi Mynda,

I'm not sure if I'm misreading your comment above when you say "you cannot get data from an open Excel workbook". Just to confirm, I can get data from an open workbook and if a file is password protected then the workbook MUST be open for PQ to be able to access the data.......

Sorry if I've misunderstood what you meant ......

Any luck with testing?

Thanks
Graham 🙂

Hi Graham,

You didn't misread my comment. There seems to be vastly different experiences for getting data from a workbook between your version of Excel and mine, assuming we're following exactly the same steps. I'm using Microsoft 365 and the following steps: Data tab > Get Data > From File > From Workbook.

If in workbook B I get data from an Excel Workbook (call it Workbook A), then workbook A must be closed, otherwise I get this error:

If I put a password on Workbook A then close it and try the same, it then tells me the file is corrupt and won't open it at all. 

Mynda

Keep in mind that in normal pc folder you should see a temporary copy of the file you are opening, starting with "~$" then your file name. You cannot see this file in the folder if your system file setting is hiding these, but they are there. (unhide if you want to see these)

It's the temporary file that PQ tries to access and says that the file is being used. This means that Excel is NOT really opening the file you wanted, it's opening a hidden COPY instead. If you don't save changes, your original file stays untouched. It will be overwritten by the temp copy IF you save changes. During file editing, the original file is CLOSED.

I was able to successfully connect using PQ to an open file by using File from folder instead of From Workbook, Filtering OUT file names that starts with ~

then filter to select only the single file I need.

Care: if you make changes to the temp copy, the changes will NOT be seen by PQ until you SAVE the changes.

For not normal folders, and I mean here OneDrive and sharepoint folders, Excel behaves differently, since few months ago I am no longer seeing the temp copies as in normal folders.

See image attached, if you filter out the temp file, you will be able to access the closed original version, even if the file is "open" (in microsoft style)...

That's interesting, Catalin. However, I still get errors when trying to get data from folder where one or more of the files are open. ‍

What errors?
I tested again, and the trick still works, I can refresh a query from folder even if files are open.

In a folder I have 3 files, one of which is open. I 'get files from folder' in a new workbook and when I click the double down arrow on the Content column containing the Binaries nothing happens. If I add a custom column with =Excel.Workbook([Content]) I get the following error for the workbook that's open:

"DataSource.Error: The process cannot access the file 'C:Usersmyndapq_2.05_dec.xlsx' because it is being used by another process.
Details: C:Usersmyndapq_2.05_dec.xlsx"

Same happens with your sample files.

Mynda

Ok, so I assume you have filtered out the files starting with "~" before extracting data from those files?

= Table.SelectRows(Source, each not Text.StartsWith([Name], "~"))

Strange, I can refresh the query with the files I sent open.

Have you run the query from the file "get data from open books.xlsx" included in archive? That query works with open files. (you just have to change the folder path to your computer)