Method 'Open' of object 'Workbooks' failed 1

[CuckooNut]

Hi everyone,

I have a procedure in my database that exports some information from Access 2007 into an Excel spreadsheet. The procedure works perfectly whenever I run it on my computer, but when one of my co-workers tries to run the same procedure on his computer, he gets a "Method 'Open' of object 'Workbooks' failed" error. It opens the spreadsheet, but then stops with that error before any information is exported. We both have Office 2007 installed on our computers.

I wondered if it might have something to do with the fact that he was trying to run it from a replica, but I tried it from the replica on my computer and it still worked perfectly.

Here is the portion of the code that seems to be experiencing the problem:

----------------------------------------------

Dim ExcelApp As Excel.Application
Dim ExcelWkb As Excel.Workbook
Dim ExcelWksht As Excel.Worksheet
Dim WorkbookName As String
Dim SheetName As String

'Store the name of the Excel File
WorkbookName = txt_Select_File.Value

'Specify which tab the production statistics should go to
SheetName = "Production Stats"

'Set up and open the Excel application object
Set ExcelApp = New Excel.Application
With ExcelApp
.Visible = True
Set ExcelWkb = .Workbooks.Open(WorkbookName)
Set ExcelWksht = ExcelWkb.Worksheets(SheetName)
End With

------------------------------------------------

Any ideas as to why it would work on my computer and not his?

If you need any additional info in order to help me with this problem, just let me know.

Thanks!

 

[gkoliver]

I have had this EXACT error multiple times. In my experience, it's always been an Excel add-in. The main one has been an Approve-it add-in, but I have also seen the same error with an Adobe add-in, IIRC. The approve-it add-in has been the bane of my existence, so if your user has that one, it's the culprit, I'm sure.

Otherwise, start disabling add-ins, see which one is the issue...

gko

 

[SkipVought]

what is the value, how is this assigned to txt_select_file and does it accurately reflect the correct workbook on whatever PC???

WorkbookName = txt_Select_File.Value

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CuckooNut]

gkoliver,

Thanks for your response. We actually went through and compared which add-ins he had on his system with the ones I had on mine,and they match up. We both have the ApproveIt add-in, but it doesn't seem to be causing any issues on my computer. Maybe I will have him disable it, though, and see if it makes a difference on his.

Worth a try!

 

[CuckooNut]

Skip,

The user clicks a "Browse" button and then selects a file in the file dialog that appears. txt_Select_File is the text box that receives the pathname of the selected file, so it should be accurate every time.

 

[bubba100]

When the code fails move the cursor over Workbookname it should give the file name you are trying to open. Does it? Have you compared the References between the machines?

 

[CuckooNut]

bubba100,

I have not tried that. I don't think the file name is the issue, because it actually opens the file (he sees it on his screen), but then gives the error before any of the cells are populated with the export information.

 

[CuckooNut]

Well, sure enough, the problem lies with the Approve-It add-in. We disabled it on my co-worker's computer, and the procedure now works perfectly. Go figure. We both had the same add-in installed, but for some reason it was only an issue on his machine.

Oh well. Thanks, gkoliver, for your solution! And thank you Skip and bubba for weighing in.

 

[gkoliver]

I'm glad I could help! That particular add-in has caused me an unbelievable number of hours of angst (before I realized the issue!)

The only difficulty is if you need approve-it