topic Re: Get the tab name from an Excel spreadsheet in QlikView

Get the tab name from an Excel spreadsheet

Wed, 25 Nov 2020 16:16:04 GMT

Hello QV Community - I have a spreadsheet with multiple tabs, and each tab name contains important information such as the region code for the source of the data. What I want to do is include the tab name as a column in the QV data. Is there a way to do that?

Thank you.

Re: Get the tab name from an Excel spreadsheet

Kushal_Chawda — Fri, 18 Mar 2016 15:41:53 GMT

see this

Dynamically Loading Multiple Excel Files

Re: Get the tab name from an Excel spreadsheet

Fri, 18 Mar 2016 15:48:45 GMT

Hello Kush141087 -

Thank you for the speedy reply. Where is this taking the tab name and making it a column?

Thank you.

Re: Get the tab name from an Excel spreadsheet

cwolf — Sat, 19 Mar 2016 00:07:53 GMT

Helllo Ron,

you can do this with a macro:

function GetWorksheets(pExcelFile)  
  set ExApp = CreateObject("Excel.Application")  
  ExApp.Visible = False  
  set ExWrk = ExApp.Workbooks.Open(pExcelFile)  
  s = ""  
  for i = 1 to ExWrk.Sheets.Count  
    if i = ExWrk.Sheets.Count then  
      s = s + "'" + ExWrk.Sheets(i).Name + "'"  
    else  
      s = s + "'" + ExWrk.Sheets(i).Name + "',"  
    end if  
  next  
  ExWrk.Close  
  ExApp.Quit  
  GetWorksheets=s  
end function

In the script:

set vExcelFile='C:\Temp\Test.xlsx';  
  
let vSheets=GetWorksheets('$(vExcelFile)');  
  
for Each vSheet in $(vSheets)  
  LOAD   
  '$(vSheet)' as Region,  
  *   
  FROM [$(vExcelFile)] (ooxml, embedded labels, table is [$(vSheet)]);  
next

Re: Get the tab name from an Excel spreadsheet

Kushal_Chawda — Sat, 19 Mar 2016 05:10:55 GMT

see this

'$(vSheetName)' AS Sheet_name