VBA is opening Excel as "read only"

[JMCONNOR]

I'm trying to update an Excel spreadsheet using VBA in an Access database.

I'm opening the spreadsheet like this:

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWkb = ExcelApp.Workbooks.Open(Addr)
Set ExcelSht = ExcelWkb.Worksheets(WkSheet)

Where Addr is the path to the spreadsheet and WkSheet is the specific worksheet I need to update.

When attempting to "SaveAs" after making the updates, I get an error indicating that the spreadsheet is "Read Only" and the changes are not saved.

If I change the Syntax to "Save", a "Copy of..." is saved.

Any ideas?

 

[combo]

Do you have the read-only worning when you open this file in excel? If so, open it (ExcelApp.Workbooks.Open(Addr)) with an optional argument:
[tt]IgnoreReadOnlyRecommended:=True[/tt]

The behaviour you described is rather strange, you should not have problems with the 'SaveAs' method. Are there any limitations in access to the file or any code in it? I would first check it in excel.

combo

 

[JMCONNOR]

No, I don't get the warning upon opening the spreadsheet during the update process inside VBA. I do get the warning when trying to manually open the sheet after trying to update it with VBA.

 

[combo]

Remove manually the read-only status:
- open the workbook manually, answer 'no' to the first read-only warning,
- start the 'save as' process (File>Save as), in the dialog select 'Tools' (top-right of the dialog) and next 'general options',
- untick the 'read-only recommended' in the saving options dialog,
- confirm saving, confirm overwriting.

You can still open the file by code with the optional IgnoreReadOnlyRecommended:=True and SaveAs with ReadOnlyRecommended:=False.

combo

 

[JMCONNOR]

Well, I'm embarrassed to admit that the problem was totally my fault. In order to determine what worksheet the user would like to update, the user is presented with a form containing a combo box that lists the worksheets in the target workbook. When creating this form, I failed to properly close the target spreadsheet after opening it to get a list of worksheets. Once I took care of this, the spreadsheet updates and saves correctly.