Get the tab name from an Excel spreadsheet

[#1]

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.

[Kushal_Chawda]

see this

Dynamically Loading Multiple Excel Files

[#3]

Hello Kush141087 -

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

Thank you.

[cwolf · Accepted solution]

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  

[Kushal_Chawda]

see this

  '$(vSheetName)' AS Sheet_name