Jim Lawrence
accessd at shaw.ca
Mon May 24 15:37:48 CDT 2010
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)' 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