How can I find the name of all tables in Excel?

Help Activities
, , ,
1

I have 20 tables in excel and I want to add a data to all of these tables.
For this, I plan to process the data by learning the table names and returning them for each.

How can I do it?

2

Hi @ekseri

Can you share the sample input and expect output

3

If no activity is able to extract all table names from excel file there is option to make it using invoke code:

Dim excel As Application = New Application
Dim workBook As Workbook = excel.Workbooks.Open("YourPathToExcelFile")
Dim workSheet As Worksheet
Dim excelTable As ListObject
listOfTableNames = New List(Of String)

For Each workSheet In workBook.Worksheets
	For Each excelTable In workSheet.ListObjects
		listOfTableNames.Add(excelTable.Name)
	Next excelTable
Next

excel.Quit

With list out argument:

Capture
Capture697×198 6.66 KB

It should work with that namespace imported:
Capture

This code should save all excel table names from each worksheet and return them by list argument

1 Like
4

image
image836×397 56.6 KB

image
image700×196 24.3 KB

image
Where am i doing wrong?

5

Just replace first 2 lines with:

Dim excel As Application = New Microsoft.Office.Interop.Excel.Application
Dim workBook As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("YourPathToExcelFile")

You have 2 different types named ‘Application’ and ‘Workbook’.
UiPath does not know which one has to choose :smiley:
We have to clarify from which namespace we want to use that types.
After changing ‘Application’ to full object name ‘Microsoft.Office.Interop.Excel.Application’ error should disappear. Its good to remember what this error means :wink:

1 Like
6

When you say table, do you mean sheets - the tabs at the bottom of the Excel file? This will get a list of them:

7

image

It does not give an error, but there is no data in the list.

Have you ever tried the code? Does it work?

image
image998×452 54.2 KB

8

No, it’s not the sheet names I want to ask.

I am trying to find the table names contained in the Excel file.

9

Hi @ekseri ,

Make sure you have given the Direction of the list argument as Out in Invoke Code Activity.

1 Like
10

I am getting error can you please share the solution

image
image1426×693 56.5 KB

11

Hi can you help me for that please share the workflow I am facing some error

12

Have you gone Excel and enabled “Trust access to the VBA project object model?”

image
image1030×488 15.6 KB

1 Like
13

Dim excel As Application = New Microsoft.Office.Interop.Excel.Application
Dim workBook As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(“C:\Users\Aleem Anwar\Documents\UiPath\get all name of table\files\1.xlsx”)
Dim workSheet As Worksheet
Dim excelTable As ListObject
listOfTableNames = New List(Of String)

For Each workSheet In workBook.Worksheets
For Each excelTable In workSheet.ListObjects
listOfTableNames.Add(excelTable.Name)
Next excelTable
Next

excel.Quit

image
image1828×292 23.5 KB

1.xlsx (11.4 KB)

I have two table in excel and I need to find all name using this code.Please help me where I am wrong
Main.xaml (10.5 KB)

14

I would avoid dim-ing a variable with the same name as the type.

15

Seems like error related to excel library.
I have never seen this.
Maybe try to install Microsoft.Office.Interop.Excel nuget

Capture
Capture729×172 12.6 KB

16

I have imported this package from import tab

17

You don’t install packages from the import tab. You use the Manage Packages button at the top.

18

I have install but still get error please help guys

image
image1890×865 76.1 KB

19

That screenshot doesn’t show us what the error message is. Give us the first few red lines in your Output (in other words, scroll up to the beginning of the error)

20

image
image1838×231 19.8 KB