Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

VB Macro issues

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

6 Replies
Not applicable
Author

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

marcus_sommer

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

    Next

Next

- Marcus

Not applicable
Author

Hi,

Thanks for the reply.

When I 'Check', itis saying:

 

Expected ')' 

at

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

Any Idea why?

marcus_sommer

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

Not applicable
Author

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

marcus_sommer

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