I need a VBA code to open .doc files as read only,No luck with replies on other Forums, please help!

[JonBiegen]

Hi all,

I am new to excel and see how helpful everyone is here.

This one has been plaguing me for weeks.

I am creating a spreadsheet for work that has several links to word files. All of them are protected by admins and I have no access to making them not Read Only. Clicking the Read only button would be no problem, except that the window opens BEHIND the excel sheet and to the untrained user, excel appears to freeze. My work around is to open task manager to switch to the "password" window and click read only.

I have found many VBA codes that make excel sheets open as read only automatically. Is there a code that I can use for other files? Specifically ".doc" and ".ppt" ?

Thank you in advance!



I am using Excel 2007

 

[MWhiteDesigns]

The below will open a document on the users desktop (change path as necessary)

sub open()
    Dim WordApp As Object
    Dim WordDoc As Object
     
    Set WordApp = CreateObject("Word.Application")
    Set WordDoc = WordApp.Documents.Open(Filename:="C:\Documents and Settings\" & Environ("UserName") & "\Desktop\" & "test.doc", ReadOnly:=True)
    WordApp.Visible = True

     
    Set WordDoc = Nothing
    Set WordApp = Nothing
end sub

 

[Kenneth Hobson]

Welcome to the forum!

Sub ken()
  Dim s As String
  'C:\Documents and Settings\ken\My Documents\MSWord\ken.docx
  s = Environ("userprofile") & "\My Documents\MSWord\ken.docx"
  
  MsgBox s
  SetAttr s, vbReadOnly
  Shell "cmd /c " & """" & s & """", vbNormalFocus
End Sub

 

[JonBiegen]

Thanks for all the help guys! You are so helpful and quick to respond and it is much appreciated!

Perhaps I need a little bit more tweaking as it is still not working for me. In order here is what is happening:

First I receive a pop up that says:

"Opening [TABLE="width: 382"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]http://sharepoint/Direct/CC/CC%20PR/Exchanges.Overview.doc[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

Some files can contain viruses or otherwise be harmful to your computer.
It is important to be certain that this file is from a trustworthy source.

Would you like to open this file?"

I click Ok.

Then I am encountering the same freeze problem. when I have excel and VBA up on both screens the only way to get to the "password" window to click read only is by using task manager.

If I wait long enough I receive another message that reads:

"Microsoft Office Excel is waiting for another application to complete an OLE action."

Since this is a sharepoint document I have tried changing the code so that it is not using a user profile as well but still am having no luck. I also made sure that I had created a new Sub for this bit of code specifically but it is not recognizing. any other thoughts?

Thanks again so much. I'm learning a lot here.

 

[Kenneth Hobson]

I don't use SharePoint so I would not be much help.

The virus check has to do with your security settings.

I am thinking that you might be better off downloading the file first.
e.g. In a Module:

Option Explicit

Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
 
Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
Alias "DeleteUrlCacheEntryA" ( _
ByVal lpszUrlName As String) As Long
 
Const ERROR_SUCCESS As Long = 0
Const BINDF_GETNEWESTVERSION As Long = &H10
Const INTERNET_FLAG_RELOAD As Long = &H80000000
Const FILE_ATTRIBUTE_TEMPORARY = &H100
 
Function DownloadFile(sSourceURL As String, _
    sLocalFile As String) As Boolean
     'Dim sLocalFile As String
     
     'Download the file. BINDF_GETNEWESTVERSION forces
     'the API to download from the specified source.
     'Passing 0& as dwReserved causes the locally-cached
     'copy to be downloaded, if available. If the API
     'returns ERROR_SUCCESS (0), DownloadFile returns True.
    DeleteUrlCacheEntry sSourceURL
    DownloadFile = URLDownloadToFile(0&, _
    sSourceURL, _
    sLocalFile, _
    BINDF_GETNEWESTVERSION, _
    0&) = ERROR_SUCCESS
End Function
 
Sub Test_DownloadFile()
    Dim fn As String, url As String
   
    fn = Environ("temp") & "\ken.xls"
    url = "http://home.aaahawk.com/~khobson/data/Excel/HelloWorld.xls"
    DownloadFile url, fn
    Workbooks.Open fn
End Sub

 

[MWhiteDesigns]

I just uploaded a dummy file to my sharepoint, and copied my address into the below macro. I changed the forward slashes to back slashes.

\\http:\\sharepoint\Direct\CC\CC%20PR\Exchanges.Overview.doc

Make sure the the file extension is indeed .doc and not .docx, as the macro will not be able to find the file. (I thought mine was .doc, however when i downloaded the file, the extension showed as .docx)

With the test below, using my file, it opened my word document as read only perfectly with no prompt. It did take a minute to open though.

sub open()
    Dim WordApp As Object
    Dim WordDoc As Object
     
    Set WordApp = CreateObject("Word.Application")
    Set WordDoc = WordApp.Documents.Open(FileName:="\\sharepoint\Direct\CC\CC%20PR\Exchanges.Overview.doc", ReadOnly:=True)
    WordApp.Visible = True


     
    Set WordDoc = Nothing
    Set WordApp = Nothing
End Sub

 

[JonBiegen]

Thank you Very much for all of your help Kenneth!

This is a little over my head, but I will play around with it and show it to co-workers.

I did not even consider that I may have to do some searching regarding share point as well. Perhaps I can clone the files not as read only to open.

I will keep doing some research and think of alternative ways to open these files read only.

 

[MWhiteDesigns]

Jon,
I tried the code I posted on another machine and once again, no prompt and the file opened fine on Sharepoint.