Advertisement

05.23.2008 at 11:26AM PDT, ID: 23428516
[x]
Attachment Details

Excel Automation In Access Problem

Asked by quadhelp in Microsoft Access Database, Visual Basic Programming, Microsoft Excel Spreadsheet Software

Tags: Microsoft, Access, 2003, VBA, VBA

The code below is executed in Microsoft Access to open an Excel file, find any blank rows (where the formula in the cell returns an empty string) and delete them.  The code works as expected to delete the rows, however EXCEL is left in memory after the code executes, preventing it from being run more than once without exiting Access and re-opening the database first.  The problem seems to have something to do with this line:

Do Until i >= xlWs.Cells(65536, "A").End(xlUp).Row

If I condition out the .End(xlUp).Row portion, the code runs (but doesn't delete the rows) and EXCEL leaves memory as expected.  Any thoughts on how to delete the rows AND force EXCEL out of memory?

Thanks!
ChadStart Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
Function delrows()
 
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim strXLFile As String
Dim i As Integer
 
strXLFile = "i:\report.xls"
 
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(strXLFile)
Set xlWs = xlWb.Worksheets("Report")
 
xlWs.Range("A2").Select
i = 1
Do Until i >= xlWs.Cells(65536, "A").End(xlUp).Row
If (Trim(ActiveCell.Value) = "") Then
xlWs.Rows(i).Delete
xlWs.Cells(i, 1).Select
Else
ActiveCell.Offset(1, 0).Select
i = i + 1
End If
Loop
 
xlWs.Range("A2").Select
xlWb.Save
Set xlWs = Nothing
Set xlWb = Nothing
 
xlApp.Quit
Set xlApp = Nothing
 
End Function
[+][-]05.23.2008 at 11:40AM PDT, ID: 21634630

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.23.2008 at 12:32PM PDT, ID: 21635139

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.24.2008 at 06:55AM PDT, ID: 21639209

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 03:07PM PDT, ID: 21656175

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.28.2008 at 03:05AM PDT, ID: 21659003

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Access Database, Visual Basic Programming, Microsoft Excel Spreadsheet Software
Tags: Microsoft, Access, 2003, VBA, VBA
Sign Up Now!
Solution Provided By: rorya
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628