topic Reading range in from Excel to field using Macro in QlikView

Reading range in from Excel to field using Macro

Thu, 20 Aug 2009 09:14:57 GMT

Hello,

Is it possible to read in a range of data from Excel to a field in QlikView by use of a Macro (not whilst Reloading) ?

We have an Aged Debtors calculating spreadhseet. You input the projected Revenue for next 24 months and predicted probabilities of receiving payment within 0-30, 30-60, 60-90 and 90+ Aged periods and it calulates the value of likely receipts over the coming months and the average payment time.

I am able to set the data fields within this spreadsheet and then read back individual cells from Excel into Variables within QlikView. I would however like to read back a range of cells from Excel (the projected Cash Receipts) into a field within QlikView. Not only would this save me having to individually read multiple cells, but enable me to then plot charts with the read data. At the moment I convert read in variables into InputFields as this is the only object I can find with the required Set command:


set fld = ActiveDocument.Fields("NewAgeDist")
set obj = ActiveDocument.GetSheetObject("TB04")
fld.SetInputFieldValue 0, ActiveDocument.Variables("vAgePercNew1").GetContent.String

Any ideas would be much appreciated.

Jonathan

Reading range in from Excel to field using Macro

Thu, 20 Aug 2009 11:31:25 GMT

this should help:

http://community.qlik.com/forums/t/15975.aspx

or using variables like

Dim wsname
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = TRUE
Set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(wsname). Range("A" & 1).Select

Reading range in from Excel to field using Macro

Thu, 20 Aug 2009 12:40:53 GMT

Hi dragonauta,

I'm trying to read data back from Excel as opposed to update it within Excel.

Specifically I'm trying to read backa range of data from Excel instead of just a cell at a time, into a Variable which I then have to push into a InputField in order to be able to plot it.

Any thoughts ?

Jonathan

Reading range in from Excel to field using Macro

Thu, 20 Aug 2009 21:31:08 GMT

I thought the example would be confusing, but what I wanted to point out was the use of the .Range property, which should be what you're looking for.

I'll try to come up with an example and post it later.