?
Solved

Access 97 to Excel XP Automation Problem

Posted on 2005-02-27
25
Medium Priority
?
355 Views
Last Modified: 2010-05-02
Following from the end of this question: http://www.experts-exchange.com/Databases/MS_Access/Q_21328492.html

Basically when I run some code to output an Access 97 query to Excel XP and do some formating, it produces and formats the spreadsheet correctly - but the excel.exe process does not terminate so you can't open the spreadsheet.

This is the function code:

***************************************************
Function OutputToExcel(Query As String, Filename As String)
Dim objXl As Excel.Application
Dim objWbk As Excel.Workbook
DoCmd.OutputTo acOutputQuery, Query, acFormatXLS, Filename, 0
Set objXl = CreateObject("Excel.Application")
Set objWbk = objXl.Workbooks.Open(Filename)
objWbk.Sheets(1).UsedRange.EntireColumn.AutoFit
    Dim cellThing As Range
    Dim boolFound As Boolean
    Dim lngFoundColumn As Long
   
    'NOTE: uncomment the following for production
    objXl.Application.ScreenUpdating = False
   
    Range("A1").Select  'start on col header row
    Do
        'Look for "changed" in column header text
        boolFound = Cells.Find(What:="changed", After:=ActiveCell, _
                LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False).Activate
        If boolFound Then
            If lngFoundColumn < ActiveCell.Column Then
                lngFoundColumn = ActiveCell.Column
            Else    'stop looking if we wrap around to prior column
                Exit Do
            End If
            Range(ActiveCell, ActiveCell.End(xlDown)).Select
            For Each cellThing In Selection.Cells
                If cellThing = True Then
                    cellThing.Offset(0, -1).Font.Bold = True
                    cellThing.Offset(0, -1).Interior.Color = vbYellow
                End If
            Next
            Selection.End(xlUp).Select  'prepare for next col header find
            'hide the changed column
            ActiveSheet.Columns(ActiveCell.Column).Hidden = True
        End If
    Loop While boolFound
   
    'NOTE: uncomment the following for production
    objXl.Application.ScreenUpdating = True
   
    Range("A1").Select  'end on col header row
   
objWbk.Save
objWbk.Close
Set objWbk = Nothing
objXl.Quit
Set objXl = Nothing
End Function
*************************************************

Which is then called from a command button on a form using the following code:

Call OutputToExcel("Table2Delta", "C:\Changed Test.xls")

Any ideas anyone?
0
Comment
Question by:PJRimmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 3
25 Comments
 

Author Comment

by:PJRimmer
ID: 13416450
Also, after the output - periodically the msaccess.exe process goes upto 99% CPU usage then back to 0% again!
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13417308
Seems like it has to be a reference to the excel.application object that is still open. Run debug and be sure that you are in fact setting it to nothing.

Bob
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13419021
I would suggest qualifying all the Excel references.
1. Add a worksheet object variable definition
2. Set the worksheet object variable to objWbk.Sheets(1) or objWbk.ActiveSheet
3. prepend the Excel objects with the appropriate qualifier
Range
Cells
ActiveCell
Selection

4. Replace the ActiveSheet with your new variable (1)

================================
My guess is that the unqualified Excel code references from my
prior question comment are creating another Excel reference on your system.
This is a likely reason you are having trouble opening the file.

My code was written within the Excel macro environment and it allows
the developer to take certain shortcuts.  The non-Excel MSOffice and VB
environments may require you to code more explicitly.  Although the code is
working (formatting the worksheet and cells), it is implicitly creating an
additional connection to the workbook file.  Your initial, explicit, connection
and automation variables are cleaned up properly.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:PJRimmer
ID: 13435189
I thought "objWbk" was my worksheet object variable!

Is that not the case?
0
 

Author Comment

by:PJRimmer
ID: 13435391
OK, my "objWbk" is the workbook the object variable - Duh!

Now I have the following code:

****************************************************
Function OutputToExcel(Query As String, Filename As String)
Dim objXl As Excel.Application
Dim objWbk As Excel.Workbook
Dim objWsh As Excel.Worksheet
DoCmd.OutputTo acOutputQuery, Query, acFormatXLS, Filename, 0
Set objXl = CreateObject("Excel.Application")
Set objWbk = objXl.Workbooks.Open(Filename)
Set objWsh = objWbk.ActiveSheet
objWsh.UsedRange.EntireColumn.AutoFit
    Dim cellThing As Range
    Dim boolFound As Boolean
    Dim lngFoundColumn As Long
   
    'NOTE: uncomment the following for production
    'objXl.Application.ScreenUpdating = False
   
    objWsh.Range("A1").Select  'start on col header row
    Do
        'Look for "changed" in column header text
        boolFound = objWsh.Cells.Find(What:="changed", After:=ActiveCell, _
                LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False).Activate
        If boolFound Then
            If lngFoundColumn < ActiveCell.Column Then
                lngFoundColumn = ActiveCell.Column
            Else    'stop looking if we wrap around to prior column
                Exit Do
            End If
            objWsh.Range(ActiveCell, ActiveCell.End(xlDown)).Select
            For Each cellThing In Selection.Cells
                If cellThing = True Then
                    cellThing.Offset(0, -1).Font.Bold = True
                    cellThing.Offset(0, -1).Interior.Color = vbYellow
                End If
            Next
            Selection.End(xlUp).Select  'prepare for next col header find
            'hide the changed column
            objWsh.Columns(ActiveCell.Column).Hidden = True
        End If
    Loop While boolFound
   
    'NOTE: uncomment the following for production
    'objXl.Application.ScreenUpdating = True
   
    objWsh.Range("A1").Select  'end on col header row
   
objWbk.Save
objWbk.Close
Set objWsh = Nothing
Set objWbk = Nothing
objXl.Quit
Set objXl = Nothing
End Function
****************************************************

And it still does the same thing!
0
 

Author Comment

by:PJRimmer
ID: 13435404
Also, every now and again I get a runtime error:

Run-time error '-2147023174 (800706ba)':

Automation error
The RPC server is unavailable


Clicking Debug on this error highlights the following expression:

        boolFound = objWsh.Cells.Find(What:="changed", After:=ActiveCell, _
                LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False).Activate


If you retry, then it works again - strange!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13435533
that is strange.

That is one of the reasons I prefer to place code inside of the object I want to automate.

Be sure to trap the error.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13435558
qualify the After:=ActiveCell reference
0
 

Author Comment

by:PJRimmer
ID: 13526980
What do you mean by "qualify the After:=ActiveCell reference"?

Please explain!
0
 
LVL 46

Accepted Solution

by:
aikimark earned 500 total points
ID: 13527429
<<What do you mean by "qualify the After:=ActiveCell reference"?>>
Qualify like you did other Excel object references.  Some changes remain undone.
I think you need to always use the Application, Workbook, and Worksheet object
variables as owning entities for references, such as Activecell and Selection.

Example:

boolFound = objWsh.Cells.Find(What:="changed", After:=objWsh.ActiveCell, _
                LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False).Activate

If lngFoundColumn < objWsh.ActiveCell.Column Then
  lngFoundColumn = objWsh.ActiveCell.Column

objWsh.Range(objWsh.ActiveCell, objWsh.ActiveCell.End(xlDown)).Select

objWsh.Columns(objWsh.ActiveCell.Column).Hidden = True

For Each cellThing In objXl.Selection.Cells

objXl.Selection.End(xlUp).Select  'prepare for next col header find
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13703222
Suggest you award the points to akimark and close the question

Bob
0
 

Author Comment

by:PJRimmer
ID: 13967231
This is still not resolved.

I was busy on another project for a while and was unable to dedicate time to this problem.

Can this question be reopened please?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13968837
PJRimmer,

I will still offer what help I can without having to reopen the question.  Where do you stand on this problem?
0
 

Author Comment

by:PJRimmer
ID: 13972691
Thanks aikimark.

I still get the same problem i.e. excel.exe process not terminating so the spreadsheet cannot be opened and the msaccess.exe process going to 99% periodically.

This the code I have now:


Function OutputToExcel(Query As String, Filename As String)
Dim objXl As Excel.Application
Dim objWbk As Excel.Workbook
Dim objWsh As Excel.Worksheet
DoCmd.OutputTo acOutputQuery, Query, acFormatXLS, Filename, 0
Set objXl = CreateObject("Excel.Application")
Set objWbk = objXl.Workbooks.Open(Filename)
Set objWsh = objWbk.ActiveSheet
objWsh.UsedRange.EntireColumn.AutoFit
    Dim cellThing As Range
    Dim boolFound As Boolean
    Dim lngFoundColumn As Long
   
    'NOTE: uncomment the following for production
    'objXl.Application.ScreenUpdating = False
   
    objWsh.Range("A1").Select  'start on col header row
    Do
        'Look for "changed" in column header text
    boolFound = objWsh.Cells.Find(What:="changed", After:=ActiveCell, _
                    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False).Activate
        If boolFound Then
            If lngFoundColumn < ActiveCell.Column Then
                lngFoundColumn = ActiveCell.Column
            Else    'stop looking if we wrap around to prior column
                Exit Do
            End If
            objWsh.Range(ActiveCell, ActiveCell.End(xlDown)).Select
            For Each cellThing In objXl.Selection.Cells
                If cellThing = True Then
                    cellThing.Offset(0, -1).Font.Bold = True
                    cellThing.Offset(0, -1).Interior.Color = vbYellow
                End If
            Next
            objXl.Selection.End(xlUp).Select  'prepare for next col header find
            'hide the changed column
            objWsh.Columns(ActiveCell.Column).Hidden = True
        End If
    Loop While boolFound
   
    'NOTE: uncomment the following for production
    'objXl.Application.ScreenUpdating = True
   
    objWsh.Range("A1").Select  'end on col header row
   
objWbk.Save
objWbk.Close
Set objWsh = Nothing
Set objWbk = Nothing
objXl.Quit
Set objXl = Nothing
End Function
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13978229
try
Dim objXl As New Excel.Application
Dim objWbk As Excel.Workbook
Dim objWsh As Excel.Worksheet
DoCmd.OutputTo acOutputQuery, Query, acFormatXLS, Filename, 0
Set objWbk = objXl.Workbooks.Open(Filename)
Set objWsh = objWbk.ActiveSheet

=============================
It is possible that two instances of Excel were/are being instantiated and that you are only getting rid of one of them.  Also, it might be caused by the DoCmd.OutputTo statement, since it is creating the Excel workbook.  Please do the following:
1. start your Task Manager and select the Processes tab
2. Step through your function and look at the proccesses in your Task Manager application.
3. look for multiple Excel tasks
0
 

Author Comment

by:PJRimmer
ID: 14203121
I can't seem to be able to single step through my code - how do you do this?

However, whenever I open the database - CPU usage goes to 99% all used up my the msaccess.exe process, and that's before I've run any code! I am starting to suspect an Access 97 with XP problem.
0
 

Author Comment

by:PJRimmer
ID: 14475249
I've just tried all the above on a plattform with Access & Excel 2000 - it behaves exactly the same way.

So it's not an Access 97 to Excel XP problem.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 14476094
set a breakpoint in your MSAccess code either by clicking on the gray border to the left of your code or typing a STOP statement.

When you run your code, the execution will stop before it executes the breakpoint statement (or on the stop statement).  Press the F8 key to step through the code.
0
 

Author Comment

by:PJRimmer
ID: 14480290
OK, done that. I do not get multiple instances of excel tasks.

The "DoCmd.OutputTo" statement does NOT create an excel task. The "Set objXl = CreateObject("Excel.Application")" statement creates the excel task.
0
 

Author Comment

by:PJRimmer
ID: 14480309
Also, closing Access ends the Excel task.
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 14515421
The only suggestion I could make is what aikimark getting at with the comment on the 19th. Step through your code with Task Manager running and processes visible, and watch for the point your code creates an instance of the process Excel.exe. The idea being that you should be able to track the creation and destruction of any created instance of excel and this should tell you where it is not being destroyed as you expect. Possibly if you have a reference to it and don't realize it, the excell process won't be destroyed when you expect.

Bob
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

770 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