Permission Denied opening an Excel File using Excel 12.0 Library & VB6

Question

I have used Excel in my VB6 apps many times before, and have never run into such a weird problem trying to accomplish something very easy..

I am trying to open an excel (xls or xlsx) file and read through values, as you can probably see.

When I try to open the file, I get an error 70 (permission denied) error. The odd thing is that there is no other instance of excel open (in task manager apps or processes). No one else is trying to access the file whatsoever. I can open the file in excel with no warning, and I can also open/read/close the file in VB6 with the basic "Open File for Input as #1" syntax without error. I can delete the file using Kill() so it can't be a directory permissions issue - Please help - I am at a loss!!!

  Dim xlApp As New Excel.Application

  Dim xlWBook As Excel.Workbook

  'Error Occurs Here
  Set xlWBook = xlApp.Workbooks.Open(File)

  Dim xlSheet As Excel.Worksheet
  Set xlSheet = xlWBook.Sheets.Item(1)

  Dim y As Integer
  For y = 1 To 99999
    If Len(xlSheet.Cells(y, 1)) > 0 Then
      Send xlSheet.Cells(y, 1) & " - " & xlSheet.Cells(y, 2) & "<br>"
    End If
  Next

  Set xlWBook = Nothing
  Set xlApp = Nothing

-Jay

Solution 1

Can you open a newly created empty spreadsheet document?

If that doesn't work it might be that your Excel installation needs to be re-registered. Open a command prompt and navigate to the folder where Excel is installed, typically something like

cd "C:\Program Files\Microsoft Office\Office12"

and then start Excel with the option /regserver

excel.exe /regserver

If this doesn't help you could go to Control Panel -> Add or Remove Programs and start a repair of Microsoft Office.

Another thing to check would be whether there are any add-ins loaded. If so, try to disable them one by one and see whether the problem disappears.

If the problem still persists you might want to check for any Office updates available.

I don't know if all this is related to your problem, it's rather standard troubleshooting techniques of Office applications...

UPDATE: Maybe troubleshooting with Procmon will reveal where the problem lies (see http://support.microsoft.com/kb/286198).

Solution 2

Have you checked your DCOM config.

Had a similar issue today, where a web service that was trying to create the Excel.Application was receiving a access denied.

In my instance I had to add the internet guest account to have access permissions to the component.

You can get to the DCOM config from Start -> Run

Type DCOMCNFG - hit enter

Then browse to Component Services/Computers/My Computer/DCOM Config/Microsoft Excel Application

Right click, properties...

The settings are under the Security tab.

Solution 3

Dim xlApp As New Excel.Application    
Dim xlWBook As Excel.Workbook    
'Error Occurs Here   
Set xlWBook = xlApp.Workbooks.Open(File) 

There is no defined which File is. ie.

File="C:\myDocuments\myexcel.xlsx". 'Because File string is empty.

Comments

[shen]

This is the best answer. It will lead you to the solution. Follow the steps provided by @0xA3. particularly repair office, use procmon to troubleshoot the "access denied" err msg. And the use of the Add spreadsheet methdo is another good one.