Solved: help on workbook connection vba

[VISHAL120]

Hi,

Am actually using this code to connect to different workbook on network :

Wip_file_accessible = False

On Error GoTo After_File_Test
'office connection
'OPENING THE WIP >>"

' Workbooks.Open Filename:="\\130.1.1.138\factory_wip\KNITTING_WIP\Critical_Path_PM_6.xls", ReadOnly:=True

'HOME CONNECTION
Workbooks.Open Filename:="C:\Users\user\Documents\vishal\vs_files\FACTORY_WIP\KNITTING_WIP\Critical_P ath_PM_6.xls", ReadOnly:=True


Wip_file_accessible = True

After_File_Test:
If Not (Wip_file_accessible) _
Then
MsgBox ("Wip <<KNITTING>> File Not accessible;Connection Problem !! ")
GoTo Exit_Sub_2
End If

and my main problem is everytime i bring this to work at home i shall go on the code and change the connection to home or so that i can work otherwise it don't wory for test updates.

is there a way that i can keep the connection on a sheet cell like

in cell B3 : "C:\Users\user\Documents\vishal\vs_files\FACTORY_WIP\KNITTING_WIP\Critical_P ath_PM_6.xls", ReadOnly:=True

And address the connection to take the path on the cell and continue.

As actually i have to change approximate 10 connections like this so that i can continue to program this at home and this is very very time consuming.

Please find attached of an example of how it should be. By this by can just copy my home connection aside and paste for each dept and continue to work normally.

Thanks to help if this is possible to do.

[Bob Phillips]

Take the quotes away, remove ,ReadOnly:=True and use


For Each cell In Range(Range("A3"), Range("A3").End(xlDown))

If cell.Value <> "" Then

Workbooks.Open Filename:=cell.value, ReadOnly:=True
End If
Next cell

[VISHAL120]

Hi Xld,

Thanks you very much it works fine but the problem is its opening all the file at one go. i want it to only open one dept by one dept. because the process of each dept is done in separate code.

i have try something like this that is to open only one dept at one go but it not working can you please help me on that what is wrong :
If Range(Range("A3"), Range("A3").End(xlDown)).Value <> "" Then
Workbooks.Open Filename:=cell.Value, ReadOnly:=True
End If

[Bob Phillips]

For Each cell In Range(Range("A3"), Range("A3").End(xlDown))

If cell.Value <> "" Then

Set wb:=Workbooks.Open(Filename:=cell.value, ReadOnly:=True )
'do your stuff on wb object
wb.Close
End If
Next cell

[visible2you]

Awesome xld.