[AccessD] Open Excel to modify Workbook

Jim Hewson jm.hwsn at gmail.com
Mon May 24 12:29:30 CDT 2010


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





More information about the AccessD mailing list