Trying to open a spreadsheet that's read only through macro's.

[slay0r]

Hi Guys,

I'm trying to get a macro to open a spreadsheet and copy the data across, All I'm literally trying to do is open it but unfortunately it's a spreadsheet that's constantly updated by one of our systems.

Because the spreadsheet is obviously read only as a result the macro gets this far and just sticks there. Is there a command to bypass this or a way around? Or even that it opens it in read only, I only want it to copy some data across for me!

Thanks for the help
Jonathan

 

[Datsmart]

Please post your code that is not working so we can try and help.

 

[WaterGypsy]

Have you tried:
Workbooks.open (filename, readonly)
???

 

[slay0r]

Sub openstoplist()
'
' openstoplist Macro
' Macro recorded 11/07/2011 by jmartin
'
'
ChDir "S:\ACCOUNTS\Reports\CreditStop"
Workbooks.Open Filename:= _
"S:\ACCOUNTS\Reports\CreditStop\Credit-Stop List.xls"
Windows("copytestmacromaster.xls").Activate
End Sub

Where would I add the readonly bit to that? I'm an absolute noobie at this macro stuff!

 

[slay0r]

Anybody? I'm still a bit lost as I said I don't normally do this kind of thing and I've got nothing to reference to on it. I'm in customer services so I can't really get help from anyone on this!

 

[Cindy Ellis]

Try changing

Workbooks.Open Filename:= _
"S:\ACCOUNTS\Reports\CreditStop\Credit-Stop List.xls"

to

Workbooks.Open ("S:\ACCOUNTS\Reports\CreditStop\Credit-Stop List.xls", ReadOnly)

as described in the previous post.
Hope that helps,
Cindy

 

[slay0r]

Thank you very much Cindy, as I said complete noobie with this so I didn't know about the brackets etc. Thanks for the help, just noticed 999 posts, 1 more to go use it wisely!

 

[slay0r]

In fact that doesn't seem to be working:

Sub openstoplist20()
'
' openstoplist Macro
' Macro recorded 11/07/2011 by jmartin
'
'
ChDir "S:\ACCOUNTS\Reports\CreditStop"
Workbooks.Open ("S:\ACCOUNTS\Reports\CreditStop\Credit-Stop List.xls",ReadOnly)
Windows("copytestmacromaster.xls").Activate
End Sub

Is there anything else I need to change? It just comes up with Syntax error when I try and execute it.

 

[Cindy Ellis]

I typed before thinking...this has been tested (although with my own filenames). It goes back to something closer to the structure you had to start with:

Sub openstoplist()
'
' openstoplist Macro
' Macro recorded 11/07/2011 by jmartin
'
'
ChDir "S:\ACCOUNTS\Reports\CreditStop"
Workbooks.Open Filename:= _
"S:\ACCOUNTS\Reports\CreditStop\Credit-Stop List.xls" , Readonly:= True
Windows("copytestmacromaster.xls").Activate
End Sub

Hope that helps (and sorry about the misdirection before),
Cindy

 

[slay0r]

Thank you very, very much Cindy, that has worked brilliantly! Hopefully I'll get some praise to pass on when this project of mine is done!