how to read a particular cell from an excel sheet using Qlikview

[#1]

Hi everyone,

I a have an excel sheet as source and i have to fetch data conditionaly from this excel sheet.

Depending upon the value of a particular cell i have to write some code can any one help me out that how i can refer to the particula cell in an excel sheet through coding in qwlikview.

Thanks,

Rahul

[Anonymous]

Is is definitely possible. Use wizard to open table file, and modify Options - Labels and header size. For example, if I want to get cell B5, the script looks this:

first 1 // need only one row
LOAD
 @2 // only column B
FROM test.xls (biff, header is 4 lines, no labels, table is Sheet1$) // goes to line 5

[#3]

It can also be done from within a macro.

Sub GetCellContents

Set oXL=CreateObject("Excel.Application")

' f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)
f_name="C:\File.xls"

If f_name="False" then
'user cancelled out of dialog box
Set oXL=nothing
Exit sub
End If

Set oWB=oXL.Workbooks.Open(f_name)
Set oSH=oWB.Worksheets.Item(1)

ActiveDocument.Variables("vCellContents").SetContent oSH.Range("A2"), true

oWB.Close

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
End Sub

Note: the commented "f_name=" line would allow the user to choose the file. The one not commented is for a predefined file. The "if f_name" block is not needed for a predefined file.

[#4]

Hi

When i m loading the above scriptin my document ,i m getting a error

Object required: 'ActiveDocument.Variables(...)'

in this line

ActiveDocument.Variables("vCellContents").SetContent oSH.Range("A2"), true

How can i get rid of this?

[#5]

Hi

I have resolved that issue which i asked u before.now my question is that what if i have mutiple sheet in the excel file

For example i have to two sheets name Sales2008 and Sales 2009 in a single Sales Excel Sheet.If i wana select the cell content A2 from Sales 2009 .how can i select ...?because the above script which we have takes by default the first sheet Sales2008 and it will give the cell content A2 of Sales 2008.but i want the cell content A2 of Slaes 2009?how can i?

Regards

Sikandar.

[Anonymous]

Sikandar,
Sheet is explicitly refered in the macro above:
Set oFH=oWB.Worksheets.Item(1)
If you chose to use load without macro as im my example, it is refered by:
... table is Sheet1$

[#7]

Hi

Thank u soo much.It really worked for me.There is one more thing.

if i wana declare a variable in macros for example i wana declare a value 8000 to a variable name My Budget .how can i achieve this?

Regards

Sikandar.

[Anonymous]

It's a different question, so it would be more useful to open another thread for this. Anyway, if you have a variable MyBudget, and wnat to assign value 8000 in macro, it will be:
ActiveDocument.Varuables("MyBudget").Setcontent "8000", true

[bumin]

load @1 from test.xlsx.. doesn't work

QV says, that @1 field not found

is the syntax changed for V11?

thanks

Bumin

[bumin]

I have got it

in Excel 2010 you have to enter A, B, C instaed of @1, @2, @3