Solved

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

Posted on 2010-08-23
15
1,002 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms office troubleshooting for users 8 36
Hard coding time and date into Excel 2 32
sql server query from excel 3 57
Excel 2016 loop through 6 0
Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

861 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now