Read in all sheets of an xls file, I do not have the <List of Sheet Names> option.

[wonka1234]

Hi all,

I am trying to read in many sheets and data within a file to my workflow.

How can I accomplish this? I am wanting to view all the data within these sheets.

Do I have to Input Data for every sheet?

[MartWClimber]

I think so. there are a couple of macro's that are called xls wildcard (or something like that), with those macro's you can read in all xlsx file 

[FrederikE]

You are reading in a .xls ? Can you change the Input to be a .xlsx instead? Alteryx doesn't seem to be able to read in the file names of the old excel format,, .xlsx would work.

If this doesn't work, you would need a batch macro that dynamically reads in the different sheets.

[Emmanuel_G]

Hi @wonka1234 ,

Find a simple test with excel file containing 4 sheets below.

Dynamic input reads all line of each sheet. So you have 26*4 lines ( 104 lines in output anchor of dynamic input ).

Let me know if ti works.

Cheers ! 

[SPetrie]

Since xls files dont give you the list of sheet names, you are really left with just a couple options. You can re-save the files from xls to xlsx manually or with some type or batch, or you can use a vb script to pull the sheet names.

I have to deal with a lot of xls files so it was easier for me to just make a macro.

Someone on the forums had a vb script and I just copied it, not sure who it was or I would give them credit for it. 

Basically, you create a batch macro that will read in each of your xls files you need the tabs for, run the vb script for them and then feed the tab name and original file name to a second macro that will them open them up for you.

The vb script is as follows.

'Create excel object
Set objExcel = CreateObject("Excel.Application")

' Open the excel sheet with the object, providing the file path
Set objWorkbook = objExcel.Workbooks.Open (WScript.Arguments(0))

'Find the count of excelsheets and iterate through the worksheet and find the name
'and store in a variable
for i=1 to objExcel.activeworkbook.sheets.count
mystr=objExcel.activeworkbook.sheets(i).name
mystr1= mystr1 + trim(mystr) + vbcrlf
'msgbox i & " " & mystr
Next
objExcel.Quit
Set objExcel =NOTHING
Set objWorkbook =NOTHING

'create filesystem object to write to a text file
Set myFSO = CreateObject("Scripting.FileSystemObject")

'open textfile, provide the name
Set WriteStr = myFSO.OpenTextFile(WScript.Arguments(1), 8, True)

'write the info to the file
WriteStr.WriteLine mystr1
WriteStr.Close

'close all the objects created
SET WriteStuff = NOTHING
SET myFSO = NOTHING

'close the workbook and application
objExcel.DisplayAlerts = False
objExcel.Application.Quit
objExcel.ActiveWorkbook.Close

save that as a .vbs file and put it in a sub folder with the other macro files and it should run fine for you.

This should also work for xlsb, xlsm, and xlsx files, but be warned it can be slow if you process a large number of files.

For xlsx files, the methods other users have mentioned will work better for pulling in multiple sheets.