Activating an open work book from text in a cell

[Andyjim]

Hello-

I am trying to enable a macro to take the string from a cell in an open
workbook called fxRM_update.xls whcih includes the path and filename of
another openworkbook. Below is my attempt, but I get a "subscript out of
range" error. An example of the string in the cell in the first workbook is
"C:\Documents and Settings\xbwh93n\My
Documents\userfileNewVersion\userfileNewVersion2.xls" but this string will
be different for each user. If we can solve this, we will have reached the
last hurdle of this project. Thanks for all your help.

Sub update4()



Dim bk As Workbook, bk1 As Workbook

Dim sstr As String

Set bk = Workbooks("fxRM_update.xls")
sstr = bk.Worksheets("lookup").Range("d42").Value

Set bk1 = Workbooks(sstr)



bk1.Activate


End Sub

 

[Mike H]

Hi,

The subscript out of range is because the workbook named in D42 is already
open and therefore the full path is unnecessary. If you substitute the full
path and filename with just the filename in D42 then your code works OK.

You probably need the full path in D42 so instead of changing this to just
the filename you could extract the filename from that path using a function.

Mike

 

[Andyjim]

Thanks Mike! Works beautifully!

Hi,

The subscript out of range is because the workbook named in D42 is already
open and therefore the full path is unnecessary. If you substitute the full
path and filename with just the filename in D42 then your code works OK.

You probably need the full path in D42 so instead of changing this to just
the filename you could extract the filename from that path using a function.

Mike

 

[Mike H]

Your welcome and thanks for the feedback

Thanks Mike! Works beautifully!