Macro Directory Question

Posted by RoB on February 08, 2002 4:28 PM

Hi,

I macro that saves a file, and names it whatever is in cell A1. I would like the macro to also open another file, and put into the last empty cell a hyper link to the file it just saved.

With my limited VB knowledge, I can open the 2nd file, and find the last empty cell, but how do I get the macro to "catch" the WHOLE directory it just saved to and make that directory a hyperlink in the cell?

Any help is appreciated,
Thanks.

Posted by Dan Aragon on February 09, 2002 1:09 PM

You could do something like this: Save the file, then capture the filename and path by declaring a string variable and setting the value of it as the path and filename of the workbook. Something like:
Dim fileName as string
fileName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

Then open your new workbook and place the code to add the hyperlink (in this case it places the hyperlink in cell C1):

ActiveSheet.Hyperlinks.Add Anchor:=Range("C1"), Address:= fileName

HTH. I haven't been able to look at this forum as regularly as I have been in the past, so if I don't respond to your followups, feel free to email me.

Dan