Open and Jum to specific cell

[Jeff Jones]

I am running Excel 2003 SP2 and have a wondering. The spreadsheet
lists by row functions along with tools to support each function. For
example, one row has a tool to support handling risks. The
spreadsheet is stored on a server and we have links to it to open it.
I'd like to be able to open the spreadsheet and then jump to a
specific row rather than to just open it and have the user have to
find the row. I could easily build a macro to accomplish my purpose
but since I don't own the spreadsheet I'm not in a position to update
it.
Has anyone any ideas if this can even be accomplished and if so how to
make it happen?

Thank you,
Jeff

 

[Dave Peterson]

Maybe you can create another workbook that opens the first workbook and jumps to
the location you want (and then closes itself).

Option Explicit
Sub Auto_Open()

Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls")

Application.Goto wkbk.Worksheets(1).Range("a1"), scroll:=True

ThisWorkbook.Close savechanges:=False

End Sub

The code should look very similar to any code you'd write in the original
workbook. But instead of using ThisWorkbook, you'd could refer to wkbk (that
workbook variable).

 

[Jeff Jones]

Good idea. In looking at the code I see no reason why it woudn't
work. A simple goto is what I was playing with in the open workbook
but even establishing a Name Label wouldn't let me pass the label or
anything to the workbook as it was opened. I'll pitch the extra
workbook option to see how it flys.

Thank you Dave.

 

[Dave Peterson]

If you know the named range you want to go to:

Option Explicit
Sub Auto_Open()

Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls")

Application.Goto wkbk.Names("testnamehere").RefersToRange, scroll:=True

ThisWorkbook.Close savechanges:=False

End Sub

 

[Jeff Jones]

The GoTo worked fine. I don't know if folks will want to use this
solution because of the additional number of spreadsheets we'de get to
maintain. However, it was fun building it. If we go this way, I'll
certainly use named ranges to account for future row changes.

Thank you. Good job!!!

 

[Dave Peterson]

You could also use Edit|Find within the macro to search for anything you wanted
and then to jump there.

 

[Jeff Jones]

I agree. I actually do that in a number of tools I built that
aggregate data from othe rspreadsheets for metrics analysis purposes.

Thank you for your suggestion. It was cool!