kevinh7777
New member
My Excel files are xlsb and password protected. When I click on Combine Binaries, it errs "External table is not in the expected format." This is latest PQ (2.48) in Excel 2010. Any help is appreciated.
Combining password protected xlsb files
- Thread starter kevinh7777
- Start date
kevinh7777
New member
I just removed the password on some of the xlsb files. I still get an intermittent error, but sometimes it works. So Excel PQ does not know how to read xlsb files? Really?
Rudi
New member
- Joined
- Jan 18, 2014
- Messages
- 134
- Reaction score
- 0
- Points
- 0
- Location
- Cape Town, RSA
- Website
- www.eileenslounge.com
- Excel Version(s)
- Excel 365 ProPlus
Power Query cannot open/edit password protected files.
You will need to remove the passwords from all of them before you import the content into a new query.
PQ has no problems reading *.xlsb file formats.
You will need to remove the passwords from all of them before you import the content into a new query.
PQ has no problems reading *.xlsb file formats.
kevinh7777
New member
I see the issue (xlsb intermittent reads) seems to be that PQ relies on a buggy ACE Provider. So is there an update or alternative to the ACE Provider?
Rudi
New member
- Joined
- Jan 18, 2014
- Messages
- 134
- Reaction score
- 0
- Points
- 0
- Location
- Cape Town, RSA
- Website
- www.eileenslounge.com
- Excel Version(s)
- Excel 365 ProPlus
That I will not know, sorry.
I deal very little with connectors in PQ. Most of my imports are TXT, CSV and Excel files, so I have little experience with ACE which you are inquiring about.
I'd recommend you start a new thread entitled something like: "Help with ACE Provider or alternative connector". Hopefully your get some assistance from others who have more experience with this.
Cheers
I deal very little with connectors in PQ. Most of my imports are TXT, CSV and Excel files, so I have little experience with ACE which you are inquiring about.
I'd recommend you start a new thread entitled something like: "Help with ACE Provider or alternative connector". Hopefully your get some assistance from others who have more experience with this.
Cheers
kevinh7777
New member
That is incredible. So PQ manages the entry and saving of credentials for servers but not for Excel files. Wow. I'm close to having to dive back into VBA.
kevinh7777
New member
From what I read online, the ACE Provider IS always used by PQ for xlsb files.
- Joined
- Mar 13, 2011
- Messages
- 2,534
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
I've split this topic into a new thread.
For what it's worth, the general rule of thumb that I've heard (and have never tested) is that Power Query won't combine xlsb files. Now having said that, it sounds like that is not true. However the passwords are a big issue.
The problem with Excel passwords is that there is one for each file, unlike a server. In the server world you've usually got one (or maybe two) per server. So it's not really built to handle it.
I'm not sure that you're going to get to far here, but I'm hoping someone comes along and proves us wrong.
For what it's worth, the general rule of thumb that I've heard (and have never tested) is that Power Query won't combine xlsb files. Now having said that, it sounds like that is not true. However the passwords are a big issue.
The problem with Excel passwords is that there is one for each file, unlike a server. In the server world you've usually got one (or maybe two) per server. So it's not really built to handle it.
I'm not sure that you're going to get to far here, but I'm hoping someone comes along and proves us wrong.