open then modify file via hyperlink

[retepallen]

I want to open a file from an e-room and then work from it.
I have the working hyperlink below and I want to set it as the “formfileâ€.

ActiveWorkbook.FollowHyperlink(Address:="e-room file address.xls")

That code works to open the file, but it just opens it and then carries on
with the code (and fails because it needs to open the above sheet) because it
isn’t defined as a sheet I want to work from.

Putting this infront doesn’t seem to work:

Set theformfile =

If i use:
Set theformfile = ActiveWorkbook.FollowHyperlink(Address:=.......

then it returns an error which says "expected function or variable"


So I’m a bit stuck.

Any help would be appreciated.

Thanks.

 

[Dave Peterson]

I'd drop the hyperlink and just open the file:

dim eRoomWkbk as workbook
set eroomwkbk _
= workbooks.open(filename:="C:\somefolder\e-room file address.xls")

Then I could do what I wanted to that workbook.

eRoomWkbk.worksheets(1).range("a1").value = "hi there!"

eroomwkbk.close savechanges:=true