Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trying to open Excel 2003 file in ReadOnly mode based on condition

Posted on 2011-02-25
9
Medium Priority
?
359 Views
Last Modified: 2012-05-11
Hello, I'm very new to VBA. This was easy enough to do in Word, but after trying to convert this to do the same thing in excel, I'm having trouble. I am Trying to open Excel 2003 file in ReadOnly mode based on condition. Here is what I have so far,

I am getting a Compile error "Method or data member not found on the Module at '.xlApp' and then I am getting another error when I try to access the "ActiveWorkbook.FullName" - Any help would be appriciated. thx

This Workbook:
=================================
Public Sub Workbook_Open()
   SetAutoOpen
End Sub

==================================
Module:
==================================
Option Explicit
Dim objAppClass As New ClassModule01
Public FirstNewXL As Boolean
Public oldNoOfOpenXL As Long

Public Sub SetAutoOpen()
    Set objAppClass.xlApp = Excel.Application
    oldNoOfOpenXL = 0
    FirstNewXL = True
End Sub


Class Module
===================================================
Private WithEvents xlApp As Excel.Application

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
    Dim thefilepath As String
    thefilepath = ActiveWorkbook.FullName
    If InStr(1, thefilepath, "Temporary Internet", vbTextCompare) Then
        ActiveWorkbook.Close
        Workbooks.Open Filename:=thefilepath, ReadOnly:=True
    End If
End Sub
0
Comment
Question by:k_r_63
[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
  • 5
  • 4
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34985849
1) ActiveWorkbook.FullName will not give you the full path. It will only give you the name for example "Book1"
2) What exactly is your objective? Maybe we might not need a class after all?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34985853
If you are trying to get the path then try this

ActiveWorkbook.Path

Sid
0
 

Author Comment

by:k_r_63
ID: 34993203
Thanks for the reply. Ultimitally, I am trying to open any email attachments in Read-only permissions. When the user opens the file, I would like to close the file. Then, reopen it in Read-only. Since I am reopening the file, I will need the exact location (which already saved in a Temporary Internet file location), therefore, I need the 'ActiveWorkBook.FullName'. 'AcriveWorkbook.Path' will not give the file name I need to reopen the file. I am getting an error when accessing the  'ActiveWorkBook.FullName' -

Bumping this up to 250 points
0
Technology Partners: 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!

 

Author Comment

by:k_r_63
ID: 35001569
I'm getting closer. I found this link below that did help a little. The file is opening in ReadOnly permissions. However, it is now I'm stuck in an infinite loop becase of two open events. Can someone help me to get this to open the file in ReadOnly permission only once?

LINK WHERE I GOT THIS IDEA FROM
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23667140.html?sfQueryTermInfo=1+10+30+activeworkbook+object+set+variabl


'CODE:
'ThisWorkBook
'===============
Private oAppEvents As CappEvents
Private Sub Workbook_Open()
    Set oAppEvents = New CappEvents
    Set oAppEvents.appExcel = Excel.Application
End Sub

'=======================
'Class Module CappEvents
'========================
Public WithEvents appExcel As Excel.Application
Private Sub appExcel_WorkbookOpen(ByVal Wb As Workbook)

    MsgBox Wb.FullName
    Dim filepath As String
    filepath = Wb.FullName
   
    If InStr(1, filepath, "Temporary Internet", vbTextCompare) Then
        ActiveWorkbook.Close
        Workbooks.Open Filename:=filepath, ReadOnly:=True
    End If
End Sub

'=========<end code>======================

0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1500 total points
ID: 35003508
Try this.

Replace the code

Private Sub appExcel_WorkbookOpen(ByVal Wb As Workbook)

    MsgBox Wb.FullName
    Dim filepath As String
    filepath = Wb.FullName
   
    If InStr(1, filepath, "Temporary Internet", vbTextCompare) Then
        ActiveWorkbook.Close
        Workbooks.Open Filename:=filepath, ReadOnly:=True
    End If
End Sub

by

Private Sub appExcel_WorkbookOpen(ByVal Wb As Workbook)
    If Not Me.ReadOnly Then
        MsgBox Wb.FullName
        Dim filepath As String
        filepath = Wb.FullName
    
        If InStr(1, filepath, "Temporary Internet", vbTextCompare) Then
            ActiveWorkbook.Close
            Workbooks.Open Filename:=filepath, ReadOnly:=True
        End If
    End If
End Sub

Open in new window


Sid
0
 

Author Comment

by:k_r_63
ID: 35011122
Your suggestion did not work. When I add the "If Not Me.ReadOnly" statement, I get a message that reads "Method or data member not found." -  "ReadMe" does not show in the dropdown list as a choice. Only "appExcel" does.
0
 

Author Comment

by:k_r_63
ID: 35017924
Actually, Sid's idea is a good suggestion. I just needed to change the 'Me.ReadOnly' to 'Wb.ReadOnly'' - It works fine now.

Private Sub appExcel_WorkbookOpen(ByVal Wb As Workbook)
    If Not Wb.ReadOnly Then
        MsgBox Wb.FullName
        Dim filepath As String
        filepath = Wb.FullName
   
        If InStr(1, filepath, "Temporary Internet", vbTextCompare) Then
            ActiveWorkbook.Close
            Workbooks.Open Filename:=filepath, ReadOnly:=True
        End If
    End If
End Sub
0
 

Author Closing Comment

by:k_r_63
ID: 35017952
The service was good. But I think this should have been an easy solution to an experienced VBA expert. I was hoping to get more responses. Instead, I only got one response over a few days. It was eventually solved througy experts-exchange.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35017994
>>I only got one response over a few days.

That's because there was no code with your original post :)

You posted the code on 03/01/11 and you got my reply on the same day :)

Also please note that experts are not hired by EE. The 'Experts' are just volunteers who like to share their knowledge and they can answer only when they get the time to do so :)

Glad it is resolved.

Sid
0

Featured Post

Technology Partners: 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

597 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