Create Table in Excel with two Worksheets

[efialttes]

So, I have successfully created a Table in an Excel with one Worksheet by using the "Create table" action from Excel Online (Business) connector, targeting as "Table range" the following:

A1:E1

Now I manually add a second Worksheet (named Hoja2) and modify the Flow, so in "Create table" I target as "Table range" the following:

Hoja2!A1:E1

Flow executiong fails.

My Flow is extremely simple, please find enclosed a screenshot

Is this use case (i.e. targeting an specific worksheet) currently supported?

Thank you in advance!

[lreinhard7]

Hi, 

Have you found a solution for this issue?

Best regards, 

Ludwig

[v-yamao-msft]

Hi @efialttes,

I assume that Hoja2 is the Sheet name. let’s say that the table that you are going to create has the same name with the worksheet. In the Table name field, please enter the worksheet name and try again.

I have made the following test. There are several sheets in the list, now I am going to format Sheet5 as a table. In the Table name field, enter the sheet name and it just works fine.

 

Best regards,

Mabel Mao

[efialttes]

@v-yamao-msft

Thanks for the tip!

Not sure I understood you. I have followed your advice and verified that:

-if I add a new worksheet manually, leave as the first worksheet on the right, and then execute the flow, the table is created on the first worksheet on the right, no matter the name assigned to the Table. So if I create a new Worksheet named Hoja3, leave it on the right; then execute the flow attempting to create Table named "Hoja5", the Table is created on Worksheet named Hoja3.

-if I add two new worksheets manually (Hoja4 and Hoja5, where Hoja5 is the first worksheet on the right), and then execute the flow targeting a new table name, the new table is not created on the first worksheet on the right (Hoja5) but on Hoja4, no matter the name assigned to it.

-If I execute the flow again, targeting a new table name, expecting new Table would be created on Hoja5, however flow execution fails. If I execute the flow again changing the range to avoid overlappings, the new Table is created on Hoja4.

 Am I missing something? If not, current implementation looks a bit weird

Thanks again for your help!

[Anonymous]

Hi everyone.

I've been trying to create tables in a particular worksheet for some time now.

I don't know if it's relevant, but I'm using the Excel online (Enterprise, not onedrive) - Create table.

My personal notes on the matter:

- It seems that the table is created in the Active Worksheet (dependant on which sheet was active the last time the excel file was saved)

- It doesn't matter the name specified for the table (at least in my case)

- I got it working by specifying the Worksheet name in the table range, similarly to what was initially stated in this post.

So in the table range field you would use (very important to be precise with ' and ! symbols):

='WORKSHEET_NAME'!AX:BY

WORKSHEET_NAME = Name of the desired worksheet as seen in excel

AX:BY = Range desired for the table, A,B = Columns, X,Y = Rows

Example:

='Hoja2'!A1:E1

Hope this helps

origdgg

[Kamacete]

Hi,

I have a problem where.

I have a document that I receive per email, and I had to archive the attachment in one folder. However, I have to take the information from one worksheet to another Excel. the problem is, the attach do not have any table created and I have tried several approaches unsuccessful.

this is base on the last comment.

Could you please help me?

thanks

[efialttes]

@Kamacete

If your challenge is to build an excel table on an excel file which originally didn't have, this article covers the topic

https://sharepains.com/2018/10/17/microsoft-flow-read-large-excel-files-within-seconds-without-creat...

If you face problems when implementing it, my suggestion is to open a new thread. Most superusers tend to pay attention to new unanswered topics, and this seems to have several replies already

Thanx!

[Kamacete]

Hi,

I didn't solve this problem until now.

there is no other way?

[Boipelo]

This works, thank you so much!

[Tobee]

great! thanks! that worked!

nowhere is the exact syntax written to chose a specific worksheet. People say you need an excel script.

But of course it has to be much simpler!

This is the solution!

[PBILover]

@Tobee : What solution did you used?

[Tobee]

Hi

The critical thing that I took from this article was the syntax for the naming of the table range. Because if you want to enter rows in an excel, this can be done only in pre-defined tables. So one needs to create a table. But the ‘Create table’ action misses the field ‘Worksheet’. You have to enter the worksheet name directly in the field of the table range.

The syntax of the table range is as follows: ‘name_of_worksheet’!A1:Z1

Where Z stands for the last column you want to enter data.

It is enough to enter one row because if you enter new rows via Power Automate (PA) the table is extended automatically.

I created flows with which I export data from SP lists to excel on a regular basis (like snapshots, e.g. every day).

Every month I create automatically a new excel file named yyyyMM.xlsx where yyyy and MM are the current year and month.

Every day, I create automatically a new worksheet plus table named with the day: yyyyMMdd.

To distinguish easily between worksheet and table, I call them wyyyyMMdd and tyyyyMMdd.

As a matter of fact, I generate the file with the worksheet and table even a day before I need it. So I acutually create the worksheet and table of tomorrow (= today + 1 day).

In the picture of the flow section you can see that I generate the name of the day in a compose action:

formatDateTime(addDays(utcNow(), 1), 'yyyyMMdd')

So this flow section

• Composes first the content of an empty excel file. See here how to do this: https://www.youtube.com/watch?v=RB_ySjhm9Sg
• Creates the excel file in a SP library using the ‘Create file’ action and the content of the empty excel file.
• Creates a worksheet in the new file. Name of worksheet: wyyyyMMdd
• Creates a table named tyyyyMMdd with the table range ‘wyyyyMMdd’!A1:Z1

Very useful info can also be obtrained from this video:

https://www.youtube.com/watch?v=Kupz71dWYyY

If you need more info, fell free to contact me again.

Cheers