• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Access 97 to Excel XP Automation Problem

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
PJRimmer
Asked:
PJRimmer
  • 11
  • 7
  • 3
1 Solution
 
PJRimmerAuthor Commented:
Also, after the output - periodically the msaccess.exe process goes upto 99% CPU usage then back to 0% again!
0
 
Bob LambersonSoftware EngineerCommented:
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
 
aikimarkCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
PJRimmerAuthor Commented:
I thought "objWbk" was my worksheet object variable!

Is that not the case?
0
 
PJRimmerAuthor Commented:
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
 
PJRimmerAuthor Commented:
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
 
aikimarkCommented:
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
 
aikimarkCommented:
qualify the After:=ActiveCell reference
0
 
PJRimmerAuthor Commented:
What do you mean by "qualify the After:=ActiveCell reference"?

Please explain!
0
 
aikimarkCommented:
<<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
 
Bob LambersonSoftware EngineerCommented:
Suggest you award the points to akimark and close the question

Bob
0
 
PJRimmerAuthor Commented:
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
 
aikimarkCommented:
PJRimmer,

I will still offer what help I can without having to reopen the question.  Where do you stand on this problem?
0
 
PJRimmerAuthor Commented:
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
 
aikimarkCommented:
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
 
PJRimmerAuthor Commented:
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
 
PJRimmerAuthor Commented:
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
 
aikimarkCommented:
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
 
PJRimmerAuthor Commented:
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
 
PJRimmerAuthor Commented:
Also, closing Access ends the Excel task.
0
 
Bob LambersonSoftware EngineerCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now