topic Re: Alteryx write to a .xlsb file's specific cell range in Alteryx Designer Desktop Discussions

Alteryx write to a .xlsb file's specific cell range

kedar26 — Tue, 13 Oct 2020 10:27:52 GMT

When we want to write the data to an xlsx file's specific cell we use something like this:

C:\Users\kk\Downloads\Testfile.xlsx|||'Sheet1$A2:C4'

But this same does not work in the case of .xlsb files, I am able to write data to an xlsb file and to a specific sheet. But not on a specific cell range on a given sheet the way we do in xlsx files. Is there any way I can do this.

Please note: I have installed the MS Access driver 2010 already.

Also when "Create new sheet" is selected and when "Append to existing sheet" is selected, the error slightly differs, what I understand is it is not able to get the get the reference of the file object, attached below are both the errors.

Re: Alteryx write to a .xlsb file's specific cell range

BrandonB — Wed, 14 Oct 2020 15:02:28 GMT

@kedar26 writing to a specific range is not a functionality that is supported with XLSB files. These files have compressed binary components in a proprietary format instead of being in readable XML code which is likely why this is the case.

XLSX is likely the better option for your use case

Re: Alteryx write to a .xlsb file's specific cell range

atcodedog05 — Wed, 14 Oct 2020 15:11:41 GMT

Hi @kedar26

A lot of feature and flexibility is supported for .xlsx (latest commonly used excel version).

While its other file formats like .xlsb , .xls etc looses a lot of feature, flexibility and functionality.

Hope this helps 🙂

Re: Alteryx write to a .xlsb file's specific cell range

atcodedog05 — Wed, 14 Oct 2020 15:23:17 GMT

Hi @kedar26

Work around would be to add blank rows and column with help of union to add rows and formula + select tool to add columns to get the effect.

Hope this helps 🙂

Re: Alteryx write to a .xlsb file's specific cell range

kedar26 — Wed, 14 Oct 2020 15:38:07 GMT

Thanks for the response Brandon. I must add that "programmatically" speaking there are ways in other languages like Python/vba to achieve it. So isn't that something which Alteryx could work on?

Can this be made possible through a product feature update request?

There are a lot of BFSI sectors users who use Alteryx and also .xlsb files for automating boring processes, for them this will be a major performance optimization.

Re: Alteryx write to a .xlsb file's specific cell range

BrandonB — Wed, 14 Oct 2020 15:45:30 GMT

We are always reviewing the ideas that our customers submit for product improvements! If there is ever product functionality that you think would be valuable definitely post it here

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas

Others can vote for it and our product team evaluates how beneficial the idea would be for the community.

Re: Alteryx write to a .xlsb file's specific cell range

kedar26 — Thu, 15 Oct 2020 09:29:15 GMT

added it in the idea section

Re: Alteryx write to a .xlsb file's specific cell range

Karenwonder — Wed, 14 Apr 2021 13:08:59 GMT

Hi Brandon,

I am trying to output my data to .xlsb on a sheet called "TESTA" using the append to existing sheet option.

I keep getting the error of:

Error: Output Data (8): Error opening query: Microsoft Access Database Engine: The Microsoft Access database engine could not find the object 'TESTA'. Make sure the object exists and that you spell its name and the path name correctly. If 'TESTA' is not a local object, check your network connection or contact the server administrator.\3011 = -543884569

I have the database engine 2010 installed and tried the solution to add $, so it becomes "TESTA$". Unfortunately still no luck to have it fixed. If I use exactly same sheet, only change from xlsb to xlsx, everything will be working as expected.

Any help is appreciated.

Cheers.

Karen