Dynamically insert datatable in Excel table or resize the table size as soon as data is added

Help Activities
, ,
#1

Hello guys,

I am using a template excel, in which there is this fixed sheet let’s say “Credit” and in this sheet, there is an existing excel table named “table1” , so I have to append data in this sheet and whenever I am doing so the data is appended but the data is no included in the table and it is important that the appended data should be in the table because it is used in many other sheets and formula are written in the table.

P.s I cant use insert row because I have to append large datatable
I have used excel scope it does add the data in excel table but The format of data is changed so its not an option

Please find the ss below

image

whenever I insert data after 302 till any n rows eg. till 355 the size of the table should change to =$A$1:$K$355

Please suggest a solution through vbnet or c# code or else I can manual keep the resize formula once in excel itself and thereafter it shall increase any of the solutions is welcomed but without uiautomation or short cut key.

@NIVED_NAMBIAR @prasath17 @balupad14

1 Like
Forum Engagement Daily Reports
#2

Hi @AS07

did u mean to resize the cells of excel file ?

#3

Hi @NIVED_NAMBIAR ,

There is a pre table in the excel sheet, I want resize that table size and that too dynamically. So earlier it will be $A1:$J$2
As the data will added eventually the size should be increased.

image

image

#4

table size means the no of rows ?

#5

@NIVED_NAMBIAR ,

Yes newly added row should be included in the excel table.

#6

why can’t u try with add datarow activity ?

#7

Hi @Nived_Nambiar,

I am able to append data in excel like for eg there are already 2 rows in the excel and I am able to add 3 more rows till index 5 rows, but there is a table in excel, that table size remains the same till 2 rows, and that table as some formulas, so I need to change the table last row till newly added last index row. And can use insert rows activity since the data to be appended is large.

#8

@AS07 Why dont you append data and later for executing the formulas use macros to either refresh or trigger the formula.
Or simply use the formula in UiPath.
This will be easier than changing the table range in the formula.
Or maybe point the entire columns as range. In this way, whenever data is added to new rows, formulas will be automatically refreshed.

Hope this helps.

#9

So, that means your table can be $A:$J and remove 1 from A1 and 2 from J2

#10

Hey @songoel ,

I want to do the same thing as append data and then by using vb net code or c# code refresh or resize the table size, I searched a lot in online sites but couldn’t get any solution.

Or maybe point the entire columns as a range. In this way, whenever data is added to new rows, formulas will be automatically refreshed.

For this point I want a method that whenever I add data it should be automatically refreshed, P.S. Cant keep the table size till 10k+ or the last index row since when I do so and count the rows of the sheet through uipath it will give me the last index row even though when the actual non-empty row is till 50th row or something and when I loop on that sheet it will loop till last index row.

Or simply use the formula in UiPath.
This will be easier than changing the table range in the formula.

It’s not just a single formula, this table is linked with different sheets in the same excel so the table content is used for further calculation as well as conditional logic in different sheets.

#11

@AS07

I have read your problem statemen.
I would assume that you need your table to resize as per your data.

Two options for that, keep your data in datatable and create a table using vb.net code
or reference your table data from a different sheet and refresh it using vb.net code

#12

@AS07 ,

Found this link that suggests a macro to refresh all data in an excel:

Now, about pointing the entire columns as DT, it should be for within the excel and not UiPath.
In UiPath, read the excel table using read range activity which will give you entire data set with appropriate range.

#13

Hi @Lakshay_Verma ,

Correct! I need to resize the table as per my data.

For the first one I cant create new table since it doesn’t allow to create data in already existing data,
On how to refresh the data using vb.net can you give me the xaml file or the code.
when I try to open the second link it gives me this screen.

image
image1366×768 155 KB

#14

@AS07 Yes, you can’t create new data over existing one. What i meant by this is, Clear your content from sheet/Delete your existing sheet or else rename it for bcp purpose. And either recreate new sheet with same name. Assuming you do not have any formulas that you are referencing from it.

For refresh code, let me get it to you.

#15

Hi @Lakshay_Verma ,

Unfortunately, yes I don’t have all the formulas, its approximately 50 + formulas in the sheet, and this table name is also used in another sheet, I am basically using a template and appending the data. On the first date, the table size is at 2 rows when I will append data it shall Resize till that last row index, the next day I will append data on the same sheet further from the last written index row.

@songoel ,

I used Application.CalculateFull in Invoke code activity it gives me an exception

#16

Hey,

It wont work in Invoke Code.

You need to set it up in the excel.
This should trigger every time excel file is opened. There will be a setting with which we can achieve this.

#17

@AS07
I stuck with some r&d on it.
Found a viable solution using invoke code.

Dim excel As Microsoft.Office.Interop.Excel.Application 'Init Excel application
Dim wb As Microsoft.Office.Interop.Excel.Workbook ' Init Excel Workbook Dim ws As Microsoft.Office.Interop.Excel.Worksheet 'Init Worksheet
`Dim ob As Microsoft.Office.interop.Excel.ListObject 'Init ListofObject

Try excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’
wb = excel.Workbooks.Open("ExcelFilePath->AbsolutePath")'Open the excel the file' excel.Visible=True
ws=CType(wb.Sheets("SheetName"),Microsoft.Office.Interop.Excel.Worksheet)'select a sheet and activiates' ws.Activate
ob=ws.ListObjects("Table1") 'Fetch your table ob.Resize(ws.Range(“A1”,“D115”)) 'Give Range here
wb.Save excel.Quit
Catch es As Exception System.Windows.MessageBox.Show(es.Message)
End Try

This will resize your table at runtime, all you need to do is pass the range value
For reference I am attaching Xaml as well.

Note: There is a dependency on interop, you need to install Excel Interop from manage package

ResizeTable.zip (2.3 KB)