?
Solved

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

Posted on 2010-08-23
15
Medium Priority
?
1,052 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:etech0
  • 7
  • 6
  • 2
15 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33507648
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33507651
are you using excel automation to open the file? or some simple open call
0
 
LVL 10

Author Comment

by:etech0
ID: 33507814
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 33507895
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33507913
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
 
LVL 10

Author Comment

by:etech0
ID: 33508019
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33508166
you can use GetObject instead. if null then do createobject
0
 
LVL 10

Author Comment

by:etech0
ID: 33508189
Can you give me an example? I want to know not only if Excel is open, but also if that file is open.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33508768
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
 
LVL 10

Author Comment

by:etech0
ID: 33512623
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 33512719
You can use xlApp.run "macroname"
0
 
LVL 10

Author Comment

by:etech0
ID: 33512785
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33513032
Ok, final result slightly different to what you originally asked for but no problem. let me post my getobject in your other thread
0
 
LVL 10

Author Comment

by:etech0
ID: 33513058
Sorry about that - it turns out that I had 2 problems, so I decided to split them.
Thanks for your help.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33513075
no worries, thats how questions sometime fan out. I posted something in your other thread
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question