Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Opening a read only workbook in Excel 2010 with VBA

This is a discussion on Opening a read only workbook in Excel 2010 with VBA within the Excel Questions forums, part of the Question Forums category; I have some code I have written that is basically supposed to open a read only copy of an Excel ...

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    13

    Default Opening a read only workbook in Excel 2010 with VBA

    I have some code I have written that is basically supposed to open a read only copy of an Excel 2010 workbook. It then leaves it open for a period of time (long enough for a running Powerpoint slideshow to update linked data) and then closes it without attempting to save, then loop. Very simple right? Should be. But I started getting occasional blip where the the code is attempting to open a read only copy of the file but even after the instruction which specifically requests a read only copy, I get the pop up window asking me if I want to "Open a Read only copy, Notify, or Cancel". We of course, code stops and updating stops. What can I do?
    Sub Updater()
    '
    If Workbooks.Count > 1 Then
    Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
    Else
    End If
    Workbooks.Open FileName:="\\tbbc2-me1\share\share\Departmental Board Metrics\WO Data Xtractor Tools\WO Data Xtractor Template.xlsm", ReadOnly:=True
    Application.OnTime Now + TimeSerial(0, 45, 0), "Closer"

    End Sub
    Sub Closer()
    If Workbooks.Count > 1 Then
    Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
    Else
    End If
    Application.OnTime Now + TimeSerial(0, 1, 0), "Updater"

    End Sub

    If the code which runs smoothly most of the time, is specifically requesting a read only copy, then why the pop up as if it was not specified? Help please.

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,020

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    Perhaps add this before your If stmt in both of the codes:
    Application.DisplayAlerts = False

    You should turn it back on at the end of the code before the End Sub line
    Application.DisplayAlerts = True
    rallcorn using: Excel 2007

  3. #3
    New Member
    Join Date
    Jul 2012
    Posts
    13

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    OK. I tried that, and oddly enough,that caused it to do it every single cycle. Is there a way to just say,
    If (some code that refers to the pop up) choose "Read Only". I know that sounds kind of like a cheap fix, but I have been struggling with this a while so I am willing to be a bit cheesy if it solves this problem. Thanks for any input.

  4. #4
    New Member
    Join Date
    Jul 2012
    Posts
    13

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    Just to be clear, this is what I did
    Sub Updater()
    '

    Application.DisplayAlerts = False
    If Workbooks.Count > 1 Then
    Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
    Else
    End If

    Workbooks.Open FileName:="\\tbbc2-me1\share\share\Departmental Board Metrics\WO Data Xtractor Tools\WO Data Xtractor Template.xlsm", ReadOnly:=True
    Application.DisplayAlerts = True
    Application.OnTime Now + TimeSerial(0, 44, 0), "Closer"

    End Sub
    Sub Closer()
    Application.DisplayAlerts = False
    If Workbooks.Count > 1 Then
    Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
    Else
    End If
    Application.DisplayAlerts = True
    Application.OnTime Now + TimeSerial(0, 1, 0), "Updater"

    End Sub

    Did I do it wrong? Again, thanks for any help!.

  5. #5
    New Member
    Join Date
    Jul 2012
    Posts
    13

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    I'm hoping to revive this question of mine with more responses. I foolishly responded back to my one responce on a Friday night which of course will get zero attention. I tried the one piece of advice I was given but that did not solve the issue. If you read my other posts after the original, hopefully there is a simple solution to this strange problem.

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,020

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    Good morning.

    If I trim down the code to test just the opening of the file (as read only), the file opens as read only and I am not prompted.

    Code:
    Sub Updater()
    '
    Application.DisplayAlerts = False
    
    Workbooks.Open Filename:="C:\RA Test\WO Data Xtractor Template.xlsm", ReadOnly:=True
    
    Application.DisplayAlerts = True
    End Sub
    rallcorn using: Excel 2007

  7. #7
    New Member
    Join Date
    Jul 2012
    Posts
    13

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    Correct. Thats the crazy part. Even without your Display Alerts addition, it will work as it should for several cycles, sometimes hours, but eventually it will prompt you, even though it is promting you in a response to a direct instruction that should have removed the need for the prompt. It is a wierd anomaly that only occurrs every "random #" of cycles. That is why I was asking for somewhat of an If, or Error or Event handling remedy. Since it only happens randomly.

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,020

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    I don't have Excel 2010 & can't replicate the error you're experiencing. I'm afraid I don't know how to help - sorry.
    rallcorn using: Excel 2007

  9. #9
    New Member
    Join Date
    Jul 2012
    Posts
    13

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    So should I repost and wait for someone else to reply?

  10. #10
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,020

    Default Re: Opening a read only workbook in Excel 2010 with VBA

    Although it is against forum rules, I would probably start new post & reference this one - some people only look at posts with zero responses. Sorry I couldn't be of assistance.
    rallcorn using: Excel 2007

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com