Macro to call up excel and use range for Email

[james]

All - I used to be able to use a Macro in excel to open the activedocument and than paste in certain cells for varainces to be sent out thru outlook.

Currently in Qlik, I have

Sub Email

Set obj = ActiveDocument.GetSheetObject("CH1")

Obj.ExportBiff "C:\test.xls"

Workbooks.Open Filename:="C:\test.xls"
With ActiveWorkbook ( this is where it fails, it cannot call up the workbook i just created Test.xls

Any Ideas

[#2 · Accepted solution]

Check out this thread, where I go through opening an Excel document, adding rows and deleting rows: http://community.qlik.com/forums/t/15975.aspx

Here's the basics for opening an Excel doc:

Sub Excel_OLE_Automation
Set oXL=CreateObject("Excel.Application")
f_name="C:\test.xls"
Set oWB=oXL.Workbooks.Open(f_name)
Set oSH=oWB.Worksheets.Item(1)
' Excel macro code goes here
oWB.Save
oWB.Close
Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
End Sub

If you're not familiar with Excel macros, you can use the macro recorder in Excel to help. Once you have your macro working in Excel, you can take the contents from there into your QlikView macro. You will have the precede the lines with "oSH."

[james]

Thanks a ton!!

Yes Im familiar, however I than take that sheet and used to format the rnages based on criteris

e.g. IIf(Range("I6").Value > 1, "<font color=""Green"">" & Format(Range("I6").Value, "0.0%")

In Qlik it doesnt like Range...

[#4]

Yes, I have had some problems with Range in the past.

First off, I think Range needs to be preceded with the oSH. Here's a quick sample from one of my apps: ActiveDocument.Variables("vCellContents").SetContent oSH.Range("B3"), true

Even if the Range is in the middle of another statement, it needs the oSH, so the macro knows which object (worksheet) to look at.