k_r_63
asked on
Trying to open Excel 2003 file in ReadOnly mode based on condition
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
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
If you are trying to get the path then try this
ActiveWorkbook.Path
Sid
ActiveWorkbook.Path
Sid
ASKER
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
Bumping this up to 250 points
ASKER
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
https://www.experts-exchange.com/questions/23667140/Run-time-error-when-calling-ActiveWorkbook-object-in-Personal-xls.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(ByVa l 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>===================== =
LINK WHERE I GOT THIS IDEA FROM
https://www.experts-exchange.com/questions/23667140/Run-time-error-when-calling-ActiveWorkbook-object-in-Personal-xls.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(ByVa
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>=====================
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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(ByVa l 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
Private Sub appExcel_WorkbookOpen(ByVa
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
ASKER
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.
>>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
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
2) What exactly is your objective? Maybe we might not need a class after all?
Sid