[AccessD] Access Controlling Excel Via Windows Automation - More Info

Bill Benson bensonforums at gmail.com
Wed Feb 4 22:00:47 CST 2015


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
>


More information about the AccessD mailing list