[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2010-08-23
15
Medium Priority
?
1,047 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 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