Solved

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

Posted on 2010-08-23
15
989 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
Comment Utility
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
Comment Utility
are you using excel automation to open the file? or some simple open call
0
 
LVL 10

Author Comment

by:etech0
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
you can use GetObject instead. if null then do createobject
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 10

Author Comment

by:etech0
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You can use xlApp.run "macroname"
0
 
LVL 10

Author Comment

by:etech0
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
no worries, thats how questions sometime fan out. I posted something in your other thread
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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

6 Experts available now in Live!

Get 1:1 Help Now