macro to paste active cell's full path into another cell

Y

yoram

hello,
i think this will be fairly easy but i'm not able to get it to work.
i need a macro (triggered by a hot key) to capture the full path based
on the active/selected cell in a file. for example if A1 is the active
cell in a file "ABC.xls" the the macro should save the following as a
string: "J:/mydocuments/folder1/ABC.xls/A1"
the 2nd macro when run should then take this stored string and paste
it into the new active cell.

this will useful in documenting the exact location of items we look for
across many files

searching on this group, all i have so far is:

1
Sub path()
Dim sk As String
sk = ActiveWorkbook.path
End Sub

2
Sub pathpaste()
sk = ActiveCell.Value
End Sub

appreciate any help. thanks.
 
M

mark.driscol

I wasn't sure why you wanted two separate macros, but you could use the
lines below and break them up if you need to. I think this may
accomplish what you want.

Sub path()
Dim sk As String
sk = ActiveWorkbook.path & "/" & ActiveCell.Address(False, False)
ActiveCell.Value = sk
End Sub

Mark
 
Y

yoram

Thanks for the prompt response. This works fine if the macro is run
within a worksheet. The idea is to capture the path of one file and
paste that value into a different file. If I split it up it doesn't
work...any ideas? Also is there any way to capture a selection of more
than one cell...ex: i run the capture macro when cell A1:A2 are
selected and then when I paste, the value reflects this range. Thanks
again.
 
M

mark.driscol

Assuming you have two workbooks open, maybe something like this will
work for you.

Sub path()
Dim sk As String
Workbooks(2).Activate
sk = ActiveWorkbook.path & "\" & Selection.Address(False, False)
Workbooks(1).Activate
ActiveCell.Value = sk
End Sub


Mark
 
Y

yoram

I've got the part for multiple cells figured out. I just can't get the
splitting into two to work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top