Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
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
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

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