for each problem

[chris2727272727]

For Each cell In Range (\\Office_server\My Documents\Thames Valley\[Thames Valley Database.xls]Employee Contacts'!$B$2:$B$144)

i have the above code but it does not work as it is a remote reference h

 

[Zack Barresse]

Hi there,

It doesn't work because it's not a valid range if the workbook is closed. You need to have the workbook open, which also means you do not need to have the entire path in there - unless you have duplicate named spreadsheets floating around. In either case, you can always check if the workbook is opened or not, if not then open it, if so then use it.

HTH

 

[chris2727272727]

shut workbook

ok but does anyone know how to work the FOR EACH problem with a shut workbook

 

[just_jon]

Re: shut workbook

ok but does anyone know how to work the FOR EACH problem with a shut workbook

I think Zack already said it can't be done.

 

[erik.van.geit]

Hello, Chris,
perhaps you mean nobody can see it is opened ?

start your code with

With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With

at the end of your code put

Application.ScreenUpdating = True

kind regards,
Erik