Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Script to pull data with structured references from Excel table

Hello,

I have one Excel sheet which has several Tables (new functionality introduced in Excel 2007)

Use structured references in Excel table formulas

Capture.PNG

Excel Tables do not seem to be directly available from QV script (ex: Design or Index)

But Name Formula are instead available in QV, and then

  • I can pull the data from Design_FixedRange_Sheet, refering to  "=Data!$B$2:$D$8"

TEST1.png

  • But not from Design_Table_Workbook or Design_Table_Sheet which refer to "=Design[[#Headers],[#Data]]"

TEST2.png

How can I pull the data using Excel table formula? As it is much more handy. Target is to have an many Tables under the same Excel sheet and user should be able to add lines with no need to update the name formula.

3 Replies
Not applicable

Any help?

Not applicable

Try with ODBC connection

SET vFile = 'O:\Testing\Others\TEST.xlsx';

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

Sheets:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('Sheets')

Let zSheet = Peek('TABLE_NAME', i, 'Sheets');

 

LOAD * From [$(vFile)] (ooxml, no labels, table is [$(zSheet)]);

NEXT

Not applicable

Hi dathu.qv

This method does not work.

In your table 'Sheets', you get every worksheet of your workbook.

For me, I am working with Tables (new features of Excel 2007), and I have several tables in one single worksheet, and their location in this worksheet can change in the future, that's why I am interested to pull the data while refering the table name

Any help from community?