Open Excel text file as readonly

[grannyM]

I have a macro that opens a daily excel fixed length text file using VBA. This file is received from off-site. It is used by several users and if it is in use, the macro gives the alert box "File is being modified by another user. Open as read-only." I'm trying to bypass this alert box by automatically opening the file as read-only, as it is not feasible to go in each day and manually make the file read-only. However, I have not been able to find where you can open and parse out a text file as read-only. I've tried recording as I open the file, and when I do it manually, it does open as read-only, but the code stays the same as below, and when I execute it, it does not open the file as read-only.

Workbooks.OpenText FileName:=PSFileName$, Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 2), Array(26, 2), Array(41, 1), Array(58, 1), Array(73, 1), Array(86, 1))

Does anyone know how to open this as read only or have another way of bypassing this alert box? Thanks for any help you can give.

granny

 

[PHV]

Have you tried this ?
Application.DisplayAlerts = False

Hope This Help
PH.

 

[grannyM]

Thanks! I believe this will work.

 

[SQLBI]

GrannyM,

Just ensure that when you've executed your code you add

Application.DisplayAlerts = True

to avoid missing any important future messages.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[grannyM]

yes, I did remember to turn the DisplayAlerts back to true, but thanks for the tip because it would have been easy to forget.