[AccessD] Open Exce to modify Workbook

Thanks, Jim and Bill
I'll work with this tomorrow.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
(vbacreations)
Sent: Monday, May 24, 2010 3:44 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Open Exce to modify Workbook

Agreed... might test the path with 

Set F = CreateObject("Scripting.FileSystemObject")

Msgbox strFileName & " can" & iif(F.FileExists(strFileName),""," not") & "
be accessed."


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Monday, May 24, 2010 4:38 PM
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 No virus found in this incoming
message.
Checked by AVG - www.avg.com
Version: 9.0.819 / Virus Database: 271.1.1/2891 - Release Date: 05/24/10
02:26:00


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