[AccessD] Open Exce to modify Workbook

Jim Lawrence accessd at shaw.ca
Tue May 25 09:29:52 CDT 2010


Brilliant.

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
Sent: Tuesday, May 25, 2010 7:18 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Open Exce to modify Workbook

The Access accdb is located on the desktop, it is one file and has not been
split yet and I'm trying to access the Excel file on the desktop.

I attempted to use Bill Benson's code to test the path, but according to one
member on Experts Exchange, MS removed that capability for A20007.  I
couldn't get it to work.

I finally figured it out.  I am a contractor for the government... that
being said, one thing they install on all computers is a program called
"ApproveIt" by Silanis.  It keeps malicious code from running on the
computers.  When my hard drive was replaced and MS Office was installed the
ApproveIt add-in to Excel was also installed.
I removed the add-in.

The key word here is "permissions" -- thanks Darryl
 
Thanks for all your help.
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Monday, May 24, 2010 6:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Open Exce to modify Workbook


____________________________________________________________________________
___________

Note: This e-mail is subject to the disclaimer contained at the bottom of
this message.
____________________________________________________________________________
___________



Agreed, I would also check if your permissions to access the file location
where the Excel file is has changed. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, 25 May 2010 6:38 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Open Exce to modify Workbook

Given the error, it still does not sound like the network connection is
being made...

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
Sent: Monday, May 24, 2010 7:11 AM
To: AccessD
Subject: [AccessD] Open Exce to modify Workbook

Access 2007, Excel 2007, Win XP (32 bits)... I've used Windows 7 (64 bits)
same results.

I have a button on a form to export data to an Excel Workbook.
Because of the limitations of Access queries, I need to export to three
worksheets within the Workbook.
I use Transferspreadsheet to export the queries to the workbook.
I give the workbook a specific name and thus use a variable to be
consistent.

Exporting to Excel three times creates three worksheets within the same
workbook.
This works well...
But what I need to do is open the workbook that was just created, insert a
worksheet and then put the parameters of the export in specific cells.  I
then need to combine the other three worksheets into one and then format the
worksheets.

Everything was working fine for 3 or 4 months -- now it doesn't.

My work machine lost network connectivity and nothing that was done would
restore it.  
I do not have admin rights to the machine.  The "help" desk personnel stated
the hard drive was going bad and needed to be replaced.  They gave me a new
hard drive and installed Office on it. 

Now I cannot open Excel through Access.
I've checked references.  I scoured the internet to see if my code was
wrong.  But, I'm using the code that is recommended.  I could find nothing.

Error message:
OLE Automation Error 440, <Object variable or with With block variable not
set> Sometimes, I'll get... run-time error '-2147417851 (80010105)' 
set> Method
'Open' of object 'workbooks' failed.

The breakpoint is at:

With xlapp

	.Workbooks.Open strFileName

It doesn't go past that line.

Any suggestions?

MTIA
Jim


--
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
____________________________________________________________________________
___________

The information transmitted in this message and its attachments (if any) is
intended only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in
reliance upon this information, by persons or entities other than the
intended recipient is prohibited.

If you have received this in error, please contact the sender and delete
this e-mail and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or
distribute the information contained in this e-mail and any attached files,
with the permission of the sender.

This message has been scanned for viruses.
____________________________________________________________________________
___________

--
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