Excel automation problems in Access

Please see code snippet below. I've left out much the row by row processing as this works fine. The problem is with the lines

    .Range("A" & iRow + 1 & ",A270").Select
    Selection.EntireRow.Delete

Firstly it does not delete what is in these rows, and secondly, if these rows are present, then the instance of Excel does not close and any further attempts to use the module return an error until Access is closed and retarted.

I feel I'm missing something obvious, but can't see it.

Thanks


Kelvin


Set xl = New Excel.Application
xl.DisplayAlerts = False
sSheet = DLookup("[ParamValue]", "Parameters", "[ParamName] = 'BlankExportSheetStage2'")
 
Set xlw = xl.Workbooks.Open(sSheet)
 
sNewSheet = sPath & "\Number Plan_" & sSite & "_VERIFY_v" & sVer & ".xls"
xlw.SaveAs sNewSheet
 
''Open the Build Data sheet & populate
Set xls = xlw.Worksheets("Build Data")
With xls
 
    iRow = 20
    Do Until rs.EOF
 
        rs.MoveNext
        iRow = iRow + 1
    Loop
 
    .Range("A" & iRow + 1 & ",A270").Select
    Selection.EntireRow.Delete
 
End With
 
Set rs = Nothing
Set xls = Nothing
 
xlw.Save
xlw.Close
xl.Quit
Set xl = Nothing

Open in new window

LVL 23
Kelvin SparksAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Set xl = New Excel.Application
xl.DisplayAlerts = False
sSheet = DLookup("[ParamValue]", "Parameters", "[ParamName] = 'BlankExportSheetStage2'")
 
Set xlw = xl.Workbooks.Open(sSheet)
 
sNewSheet = sPath & "\Number Plan_" & sSite & "_VERIFY_v" & sVer & ".xls"
xlw.SaveAs sNewSheet
 
''Open the Build Data sheet & populate
Set xls = xlw.Worksheets("Build Data")
With xls
 
    iRow = 20
    Do Until rs.EOF
 
        rs.MoveNext
        iRow = iRow + 1
    Loop
 
    .Range("A" & iRow + 1 & ",A270").EntireRow.Delete
 
End With
 
Set rs = Nothing
Set xls = Nothing
 
xlw.Save
xlw.Close
Set xlw = Nothing
xl.Quit
Set xl = Nothing
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:


change this
.Range("A" & iRow + 1 & ",A270").Select
    Selection.EntireRow.Delete

to

.Range("A" & iRow + 1 &":" & "A270").Select
    Selection.EntireRow.Delete
0
 
Rory ArchibaldConnect With a Mentor Commented:
Your issue with Excel remaining running is that you have an unqualified reference to Selection - you would need to change this:


Selection.EntireRow.Delete
to this:
xl.Selection.EntireRow.Delete


but Patrick's version is neater, though you sill need to change the comma in the range reference to a colon I think as capricorn1 did.
0
 
Kelvin SparksAuthor Commented:
Not totaaly solved through here, but gave me the leads
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.