Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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?
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.
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$
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.
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
load @1 from test.xlsx.. doesn't work
QV says, that @1 field not found
is the syntax changed for V11?
thanks
Bumin
I have got it
in Excel 2010 you have to enter A, B, C instaed of @1, @2, @3