VB Macro issues

[#1]

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

[#2]

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

[#4]

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

[#6]

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