Excel can't find file after running Access VBA

throbscottle99 · 07-24-2008, 07:57 AM

Hi all, first post on here!
I have created a button on an Access form which opens a file in Excel - it has actually only done this on one occasion after many failures. (The file does exist, it is in the place it's supposed to be, and it's not corrupted!)
I have tried 3 alternative methods in Access VBA, using my object variable's Open() method, using a hyperlink, and using GetObject() (which has produced the single occasion of it working).
The current version goes like this:
Dim myFile As Object
Set myFile = GetObject("P:\file.xls")
On Error Resume Next
myFile.Application.Visible = True
myFile.Parent.windows(1).Visible = True
myFile.UserControl = True
myFile = Nothing
Clicking the button produces the Excel error "cannot access file.xls", followed by any appropriate Access errors (at the moment, I get "automation error
unspecified error")
After this, if I try to open the file directly in Excel, most of the time I get the same error (even after killing off any stray instance of Excel in the task manager) until I close Access.
It probably doesn't make any difference, but the workbook has a bit of code to make it go to a text box when it's opened.

I hope someone can help me with this!
TIA
throbscottle99

David M58 · 07-24-2008, 03:36 PM

You may find the following code useful. In the Access database file, set a reference to the Microsoft Excel Object Library.

Code:

    Dim myFile As Excel.Application
    Set myFile = CreateObject("Excel.Application")
    myFile.Application.Visible = True
    myFile.Workbooks.Open "P:\file.xls"
    myFile.Parent.Windows(1).Visible = True
    myFile.UserControl = True

throbscottle99 · 07-25-2008, 08:24 AM

Fantastic!
I didn't know about the setting references to libraries thing - so I've learnt something there.
Funny thing is, the solution you have given me is very similar to what I tried originally (and of course can't refer back to since it's long gone). Anyways - thank you very much - I can stop banging my head against the wall now....
throbscottle99

07-24-2008, 07:57 AM	#1 (permalink)
throbscottle99 Registered User Join Date: Jul 2008 Location: Cornwall, UK Posts: 2 OS: XP SP3	Excel can't find file after running Access VBA Hi all, first post on here! I have created a button on an Access form which opens a file in Excel - it has actually only done this on one occasion after many failures. (The file does exist, it is in the place it's supposed to be, and it's not corrupted!) I have tried 3 alternative methods in Access VBA, using my object variable's Open() method, using a hyperlink, and using GetObject() (which has produced the single occasion of it working). The current version goes like this: Dim myFile As Object Set myFile = GetObject("P:\file.xls") On Error Resume Next myFile.Application.Visible = True myFile.Parent.windows(1).Visible = True myFile.UserControl = True myFile = Nothing Clicking the button produces the Excel error "cannot access file.xls", followed by any appropriate Access errors (at the moment, I get "automation error unspecified error") After this, if I try to open the file directly in Excel, most of the time I get the same error (even after killing off any stray instance of Excel in the task manager) until I close Access. It probably doesn't make any difference, but the workbook has a bit of code to make it go to a text box when it's opened. I hope someone can help me with this! TIA throbscottle99

07-24-2008, 03:36 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 94 OS: Windows XP SP3	Re: Excel can't find file after running Access VBA You may find the following code useful. In the Access database file, set a reference to the Microsoft Excel Object Library. Code: Dim myFile As Excel.Application Set myFile = CreateObject("Excel.Application") myFile.Application.Visible = True myFile.Workbooks.Open "P:\file.xls" myFile.Parent.Windows(1).Visible = True myFile.UserControl = True

07-25-2008, 08:24 AM	#3 (permalink)
throbscottle99 Registered User Join Date: Jul 2008 Location: Cornwall, UK Posts: 2 OS: XP SP3	Re: Excel can't find file after running Access VBA Fantastic! I didn't know about the setting references to libraries thing - so I've learnt something there. Funny thing is, the solution you have given me is very similar to what I tried originally (and of course can't refer back to since it's long gone). Anyways - thank you very much - I can stop banging my head against the wall now.... throbscottle99