[AccessD] Access Controlling Excel Via Windows Automation - Problem Resolved

Brad Marks bradm at blackforestltd.com
Thu Feb 5 21:21:25 CST 2015


All,

It appears that this problem is resolved.

Background - I am trying to run an Access application that controls Excel.  I need to get this application running on Windows Server 2012 R2 - Access 2010 Runtime - Excel 2013 - Late Binding.

Here is the piece that I was missing ...

I needed to change the "Compatibility" for Excel.exe to "Run this program in compatibility mode for Windows 8".

There is no option for Windows Server 2012.  Evidently the Windows 8 setting is what is needed for Windows Server 2012.

Thanks again to everyone who provided ideas on how to attack this problem.

I hope that others may benefit from what I have discovered.

Brad 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Thursday, February 05, 2015 9:02 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Controlling Excel Via Windows Automation - More Info - Excel Add-In???

Hi Brad,

When you go to the Excel Options screen you'll first see the list of Excel Add-In's.  At the bottom of that screen is a drop-down list named 'Manage'.
The 2nd item in that list is COM Add-In's.  ACT! might be a COM Add-In.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Thursday, February 05, 2015 17:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Controlling Excel Via Windows Automation - More Info - Excel Add-In???

Dan and Bill,

Thanks for the help, I really appreciate it.

The code provided (below) is very similar to the code that I was using.

Just for fun, I copied the code below into a new little test Access application.

It works nicely on my PC (Office 2007), but it does not work on the machine with Access 2010 and Excel 2013.

Earlier, the possibility of the problem being caused by an "Add-in" was suggested.

I installed Excel 2013 a few days ago and did not intentionally do anything with any Add-in.  However, when I ran tests this afternoon I fired up Excel directly (not from Access). 

When I did this, I saw a small pop-up that said "Loading Add-ins (1 of 1)
ACT!    Documents Tab Add-In"

I now believe that this Add-In is the source of the problem.

I have not worked much with Add-Ins.  I found a page on Microsoft's Website that describes how to remove Add-Ins from Excel, but when I tried to do this, the Add-In was not visible.

I plan to do more digging on how to remove this add-in.

Thanks again for your help.

Brad



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  

----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Wednesday, February 04, 2015 10:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Controlling Excel Via Windows Automation - More Info

It's not me with the need Dan, probably Brad you are trying to help, no?

I have never had trouble automating Excel. My code looks similar to yours I am sure. It's not rocket science really, however maybe Brad has stumbled across a problem with plug-ins or adding that don't like being automated. I am eager to see what he learns.
On Feb 4, 2015 6:02 PM, "Dan Waters" <df.waters at outlook.com> wrote:

> Hi Bill,
>
> This is what I use to open an excel file - maybe it will be helpful.
>
> Good Luck!
> Dan
>
> --------------------------
> Public Sub OpenExcelFile(stgSpreadsheetFullPath As String)
>
>     Dim objExcel As Object
>     Dim objWorkbook As Object
>     '    Dim objExcel As excel.Application  '-- TEST
>     '    Dim objWorkbook As excel.Workbook  '-- TEST
>
>     Application.Echo SV.ApplicationEcho
>
>     DoEvents
>
>     '-- Open the Excel application
>     Set objExcel = CreateObject("Excel.Application")
>
>     DoEvents
>
>     '-- Make the application visible
>     objExcel.Visible = True
>
>     DoEvents
>
>     '-- Open the workbook in the spreadsheet file
>     Set objWorkbook = objExcel.Workbooks.Open(stgSpreadsheetFullPath)
>
>     '-- Now that the file is open to the workbook close the object 
> variables
>     Set objWorkbook = Nothing
>     Set objExcel = Nothing
>
>     Application.Echo True
>
> End Sub
> -------------------------------
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> Sent: Wednesday, February 04, 2015 16:49 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access Controlling Excel Via Windows Automation
> - More Info
>
> I'm stumped, however I am beginning to consider the possibility of 
> some plug-in or Addin to their Excel causing the issue. Would you feel 
> comfortable asking them to disable any plug-ins like Adobe, etc - and 
> temporarily uninstall any Add-ins (COM or otherwise) - and retry?
>
> On Wed, Feb 4, 2015 at 12:57 PM, Brad Marks <bradm at blackforestltd.com>
> wrote:
>
> > All,
> >
> > Thanks for the help on this issue.
> >
> > Summary - Access application controlling Excel via Windows Automation.
> >
> > Late Binding is used
> >
> > Works properly on PC with Access 2007 and Excel 2007
> >
> > Works properly on a second PC with Access 2013 (Runtime) and Excel
> > 2013
> >
> >
> > Does not work on a machine with Access 2010 (Runtime) and Excel 2013.
> >
> > I actually have two small Access applications that I am using to 
> > debug this problem.
> >
> > One was working fine, the other was not working.
> >
> > The first Access application had "xlApp.Visible = False" and was 
> > working nicely.
> >
> > I changed this line to "xlApp.Visible = True" and now this 
> > application is also not working.  I now see an error message that 
> > says "We're sorry, but Excel has run into an error that is 
> > preventing it from working
> correctly.
> > Excel will need to be closed as a result."
> >
> > So, it appears that the command "xlApp.Visible = True" is at the 
> > crux of the problem on the machine with Access 2010 (Runtime) and 
> > Excel
2013.
> >
> > I feel like I am getting closer, but still no cigar.
> >
> > Brad
> >
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> > Sent: Wednesday, February 04, 2015 10:36 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Access Controlling Excel Via Windows 
> > Automation
> >
> > It works just fine for any app I have used, so long as late binding 
> > us used.
> >
> > I particular I have clients who ONLY have Office 2010 - and who 
> > recently bought Access 2013 standalone: and my Access App which 
> > automates their Excel (without a ref to Excel) works just fine.
> >
> > I would look elsewhere.
> >
> > You say there is no error message Brad. Have you had them look in 
> > Task Managet, maybe the instance is opening invisibly? In which case
> >
> > XLApp.Visible = True
> >
> > would resolve.
> > On Feb 4, 2015 11:07 AM, "John W. Colby" <jwcolby at gmail.com> wrote:
> >
> > > You are trying to automate a later version from an earlier version.
> > > That is never a good idea, and I would not expect it to work.
> > >
> > > John W. Colby
> > >
> > > On 2/4/2015 10:14 AM, Brad Marks wrote:
> > >
> > >> All,
> > >>
> > >> I just ran a test of the Access application that creates an Excel 
> > >> file on a PC that has Access 2013 and Excel 2013.  It worked
> perfectly.
> > >>
> > >> However, when I run the same application on the machine that has 
> > >> Access
> > >> 2010 Runtime and Excel 2013, the application gets hung up.  There 
> > >> is no error message.
> > >>
> > >> I have rebooted this machine and this did not make any difference.
> > >>
> > >> I thought that Access 2010 Runtime would play nicely with Excel 
> > >> 2013, but for some reason this appears to not be the case.
> > >>
> > >> I am open to any ideas.
> > >>
> > >> Thanks,
> > >> Brad
> > >>
> > >>
> > >> -----Original Message-----
> > >> From: accessd-bounces at databaseadvisors.com
> > >> [mailto:accessd-bounces@ databaseadvisors.com] On Behalf Of Bill 
> > >> Benson
> > >> Sent: Tuesday, February 03, 2015 4:02 PM
> > >> To: Access Developers discussion and problem solving
> > >> Subject: Re: [AccessD] Access Controlling Excel Via Windows 
> > >> Automation
> > >>
> > >> Did you (1) use late binding but nonetheless (2) left a reference 
> > >> to Excel?
> > >> That would do it if the Excel you are using is not in the same 
> > >> location as the next user's. And it has happened to me.
> > >>
> > >> On Tue, Feb 3, 2015 at 4:14 PM, Brad Marks 
> > >> <bradm at blackforestltd.com>
> > >> wrote:
> > >>
> > >>  All,
> > >>>
> > >>> I don't have a lot of experience with this realm but I have had 
> > >>> some success using Access VBA to control Excel via Automation.
> > >>>
> > >>> I have one Access 2007 application that creates a number of reports.
> > >>> Recently I have added new VBA code to also create an Excel file.
> > >>> This works nicely on my development PC that has Access 2007 and 
> > >>> Excel
> > 2007.
> > >>>
> > >>> However, I have run into some problems when trying to deploy 
> > >>> this application on a machine that has Access 2010 Runtime and 
> > >>> Excel
2013.
> > >>>
> > >>> All of the reports work properly via Access 2010 Runtime.
> > >>>
> > >>> However, when I try to run the VBA code that generates the Excel 
> > >>> file, the application hangs up.  There are no error messages.
> > >>>
> > >>> I am using "Late Binding".
> > >>>
> > >>> I am not sure where to start looking.
> > >>>
> > >>> Should Access 2010 Runtime work properly in conjunction with 
> > >>> Excel
> > 2013?
> > >>> (both are 32 bit)
> > >>>
> > >>> Is anyone else using this combination?
> > >>>
> > >>> I have thought about upgrading Access Runtime from 2010 to 2013, 
> > >>> but I would prefer to not do this if I can resolve the current
> problems.
> > >>>
> > >>> Thanks,
> > >>> Brad
> > >>>
> > >>> --
> > >>> AccessD mailing list
> > >>> AccessD at databaseadvisors.com
> > >>> http://databaseadvisors.com/mailman/listinfo/accessd
> > >>> Website: http://www.databaseadvisors.com
> > >>>
> > >>>  --
> > >> AccessD mailing list
> > >> AccessD at databaseadvisors.com
> > >> http://databaseadvisors.com/mailman/listinfo/accessd
> > >> Website: http://www.databaseadvisors.com
> > >>
> > >>
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list