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

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

Fri, 29 May 2009 14:20:13 GMT

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

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

Anonymous — Sat, 30 May 2009 02:55:12 GMT

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

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

Sat, 30 May 2009 03:36:25 GMT

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.

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

Wed, 03 Jun 2009 11:54:51 GMT

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?

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

Wed, 03 Jun 2009 12:37:30 GMT

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.

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

Anonymous — Wed, 03 Jun 2009 19:15:08 GMT

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$

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

Thu, 04 Jun 2009 11:50:41 GMT

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.

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

Anonymous — Thu, 04 Jun 2009 19:49:14 GMT

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

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

bumin — Sun, 03 Jun 2012 10:35:06 GMT

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

QV says, that @1 field not found

is the syntax changed for V11?

thanks

Bumin

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

bumin — Sun, 03 Jun 2012 11:43:39 GMT

I have got it

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