Activating an open work book from text in a cell

  • Thread starter Thread starter Andyjim
  • Start date Start date
A

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
 
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
 
Thanks Mike! Works beautifully!

Mike H said:
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
Click to expand...
 
Back
Top