Open Excel file from Word VBA, why is it read only?

I've created VBA code in Word that opens and edits a specific Excel file. When I try to save the file manually, I find that it is read only. Is there a way to make Word open the Excel file so that it is not read only?
Thanks
LVL 10
etech0Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
You can use xlApp.run "macroname"
0
 
rockiroadsCommented:
Was this readonly in the first place? If so then maybe you can remove the attributes before you open
eg     SetAttr "c:\myfile.xls", vbNormal

or are you updating the file after you opened it in vba
0
 
rockiroadsCommented:
are you using excel automation to open the file? or some simple open call
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
etech0Author Commented:
Thanks for answering.
No, the actual file isn't read only.
I'm opening the file like this:

Dim xlApp As Object
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\file.xlsm")

also, I get an error with the following:
xlWB.sheets(1).Cells(1, 3).Select
0
 
rockiroadsCommented:
cant see anything wrong with that code. What is the exact error you get when you try to select the cell?

are you definitely opening a file that exists? excel will still open if file does not exist and there will be no worksheet so that could be one reason why your xlWB.sheets line fails.
0
 
rockiroadsCommented:
sample code which creates the file for you
You need to specify the fileformat when performing a saveas. Since you are using macro enabled, you need number 52

Const xlOpenXMLWorkbookMacroEnabled = 52
Dim xlApp As Object
Dim xlWB As Object
Dim sFile As String


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
sFile = "C:\Documents and Settings\file.xlsm"
If Dir$(sFile) <> "" Then
    Set xlWB = xlApp.Workbooks.Open(sFile)
Else
    Set xlWB = xlApp.Workbooks.Add
    xlWB.SaveAs sFile, xlOpenXMLWorkbookMacroEnabled
End If
xlWB.Sheets(1).Cells(1, 3).Select
0
 
etech0Author Commented:
No, the file definitely exists. What's interesting is that I don't get the error every time - I can't tell you what it is right now because right now my code works fine :). I think it was something like "application doesn't support that function" or similar.
I think the read only problem was because it turned out that I already had that file opened. Is there a way to check if I have the file opened, and then set the focus to that file instead of opening it again?
0
 
rockiroadsCommented:
you can use GetObject instead. if null then do createobject
0
 
etech0Author Commented:
Can you give me an example? I want to know not only if Excel is open, but also if that file is open.
0
 
Rory ArchibaldCommented:
xlWB.sheets(1).Cells(1, 3).Select
will only work if the first sheet is active. You could use:
With xlWB.sheets(1)
   .Select
   .Cells(1, 3).Select
End With


although you don't generally need to select anything to manipulate it in code.


As regards the read-only bit, we'd need to see the rest of the code that manipulates Excel. I'd bet there are unqualified references that are causing the problem.
0
 
etech0Author Commented:
This is the whole code. The reason I was selecting that cell is because I originally wanted to run a macro in Excel. I couldn't get Word to run the macro, so I made it happen whenever that cell is selected, and made Word select that cell. Is there a better way to do this?

Dim xlApp As Object
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\file.xlsm")
xlWB.sheets(1).Cells(1, 3).Select
0
 
etech0Author Commented:
Thanks, that fixes it. I'm going to post my question about checking if the file is open yet before opening it in a separate thread.
0
 
rockiroadsCommented:
Ok, final result slightly different to what you originally asked for but no problem. let me post my getobject in your other thread
0
 
etech0Author Commented:
Sorry about that - it turns out that I had 2 problems, so I decided to split them.
Thanks for your help.
0
 
rockiroadsCommented:
no worries, thats how questions sometime fan out. I posted something in your other thread
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.