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

Dan Waters df.waters at outlook.com
Wed Feb 4 17:00:51 CST 2015


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



More information about the AccessD mailing list