topic Re: VB Macro issues in QlikView

VB Macro issues

Wed, 01 May 2013 18:27:45 GMT

Hi,

Staring at this for a long time now, what is wrong with it?

It is supposed to find a record in the specified excel file based on a field currently selected in QV, and then delete the entire row in the excel file.

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(1).Cells

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

oWB.Save
oWB.Close

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

Re: VB Macro issues

Wed, 01 May 2013 18:29:45 GMT

Im sorry, below is the correct code.

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

Re: VB Macro issues

marcus_sommer — Thu, 02 May 2013 11:31:41 GMT

Perhaps this approach is helpful: loop through rows (is vba)

For i = ws2.Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1

For ii = 1 To UBound(arr_K())

Set Wo = ws2.Rows(i).Find(what:=arr_K(ii), LookIn:=xlValues, lookat:=xlWhole)

If Not Wo Is Nothing Then ws2.Rows(i).Delete

- Marcus

Re: VB Macro issues

Thu, 02 May 2013 12:31:51 GMT

Hi,

Thanks for the reply.

When I 'Check', itis saying:

Expected ')'

:=arr_K(ii), LookIn:=xlValues, lookat:=xlWhole)

Any Idea why?

Re: VB Macro issues

marcus_sommer — Thu, 02 May 2013 12:47:26 GMT

You can't applied these code 1:1 - it is vba, not vbscript. I meant the approach with loop through rows. Possibly vbscript supported not the function "find" then you could use autofilter.

- Marcus

Re: VB Macro issues

Thu, 02 May 2013 14:28:06 GMT

Hi, still cant get it to work. Not an expert with VBScript, I just know my way around a little.

Re: VB Macro issues

marcus_sommer — Fri, 03 May 2013 09:12:48 GMT

Often it is easier to do such things directly in vba with open- or change-trigger. There is a lot of examples per google.

Also it could be better to solved in QV themselves - load this excel-file, filter the data and store/export the result.

- Marcus