?
Solved

Excel automation problems in Access

Posted on 2009-02-11
4
Medium Priority
?
307 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Kelvin Sparks
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 800 total points
ID: 23618692
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 23618717


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

to

.Range("A" & iRow + 1 &":" & "A270").Select
    Selection.EntireRow.Delete
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 23621194
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
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 23801570
Not totaaly solved through here, but gave me the leads
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question