Word Macro opens excel as Read only even though ReadOnly:=False

[nkpan]

Hello,

I am working on a word macro where I have a user from and i want to open an excel file though a command button.

I have declared Xl and WB1 as global variables in a Module.

The code in the command button goes as below
Private Sub cmdDelete_Click()
Dim k As Integer
Set Xl = New Excel.Application
Set WB1 = Xl.Workbooks.Open(AutoAbbreviations.txtPath.Value, ReadOnly:=False)
Xl.Visible = True
WB1.Activate
WB1.Worksheets("Abb-List").Activate
k = WB1.Worksheets("Abb-List").Range("A" & Rows.Count).End(xlUp).Row
,
,
,
End Sub

Even though my code states ReadOnly:= False still the workbook open in read only for the below code line
Set WB1 = Xl.Workbooks.Open(AutoAbbreviations.txtPath.Value, ReadOnly:=False)

Also

I am getting an error on this line:
k = WB1.Worksheets("Abb-List").Range("A" & Rows.Count).End(xlUp).Row

The error msg is
Run Time error 1004
Method 'Rows' of ojbect"_Global' failed


Would really appreciate if some expert can help on this.

Thank you

[nkpan]

Hello,
It worked for me when i changed the code as follows:
Private Sub cmdDelete_Click()
Dim k As Integer

Set WB1 = Workbooks.Open(AutoAbbreviations.txtPath.Value, ReadOnly:=False)



k = WB1.Worksheets("Abb-List").Range("A" & Rows.Count).End(xlUp).Row
,
,
,
End Sub

I am now able to see excel as read write,i can update the same and save it, and the formula also works.

But i am still wondering what went wrong when i declared xl as new excel application

Hello,

I am working on a word macro where I have a user from and i want to open an excel file though a command button.

I have declared Xl and WB1 as global variables in a Module.

The code in the command button goes as below
Private Sub cmdDelete_Click()
Dim k As Integer
Set Xl = New Excel.Application
Set WB1 = Xl.Workbooks.Open(AutoAbbreviations.txtPath.Value, ReadOnly:=False)
Xl.Visible = True
WB1.Activate
WB1.Worksheets("Abb-List").Activate
k = WB1.Worksheets("Abb-List").Range("A" & Rows.Count).End(xlUp).Row
,
,
,
End Sub

Even though my code states ReadOnly:= False still the workbook open in read only for the below code line
Set WB1 = Xl.Workbooks.Open(AutoAbbreviations.txtPath.Value, ReadOnly:=False)

Also

I am getting an error on this line:
k = WB1.Worksheets("Abb-List").Range("A" & Rows.Count).End(xlUp).Row

The error msg is
Run Time error 1004
Method 'Rows' of ojbect"_Global' failed


Would really appreciate if some expert can help on this.

Thank you

[macropod]

i am still wondering what went wrong when i declared xl as new excel application

Most likely you already had the workbook open. Your code now won't work if Excel isn't running.

[nkpan]

Hey Thanks,
well that could be the most possible reason, but the subsequent code has closure of excel workbook and excel application quit as well.

Most likely you already had the workbook open. Your code now won't work if Excel isn't running.

[macropod]

well that could be the most possible reason, but the subsequent code has closure of excel workbook and excel application quit as well.

The fact you can subsequently close the workbook and quit Excel is irrelevant. When you start a new Excel session, as 'Set Xl = New Excel.Application' does, that new session can only update workbooks it opens; if the workbook was already open, the new session can only open it in read-only mode..