topic Delete current selection from excel macro in QlikView

Delete current selection from excel macro

Wed, 01 May 2013 13:41:10 GMT

Hi,

I am looking for a macro to delete the currently selected data in a chart from a specified .xls

I am able to write to an excel file via user input in QV, so I know this has to be possible. Trying everything I can think of in VB, but perhaps one of you is/are better than I with VB script?

thanks!

Re: Delete current selection from excel macro

Thu, 02 May 2013 18:02:13 GMT

Hi,

Any ideas on this?

I have a field in the excel document called ' recid ' . It is unique.

In qlikview, I am pulling all of the data from that excel document and displaying it as such.

I want to create a button that when clicked, deletes the current selection from the excel document.

Re: Delete current selection from excel macro

Thu, 02 May 2013 19:24:38 GMT

Hi, I fixed this for myself. In case anyone was wondering:

I created an inputbox with a variable of vMatchKey. User enters in the record ID to delete.

Button with vbscript macro to execute the following:

sub Excel_DeleteRow
MatchKey = (ActiveDocument.Variables("vMatchKey").GetContent.string)

Set oXL=CreateObject("Excel.Application")

f_name="C:\attend.xls"

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

oSH.Cells.Find(MatchKey, , , , 1, 2).EntireRow.Delete

oWB.Save
oWB.Close

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
END SUB

I now have an app where I can enter in data to an excel data source, and delete from. Works nicely.