Excel macro help

Newton

I'm writing a macro in excel that prompts for input from the user and puts it into a spreadsheet. The last bit that I want to do is to add a link to another excel file in the last cell. I'm using UserForms to collect the data and I want a button on the last one that will open an explorer window that will allow me to browse for the file. This seems like it should be pretty easy, but I'm new to this and teaching myself and haven't been able to figure it out yet.

whuppins

Here's a basic bit of VBA that will do what (I think) you want to do:

filesrc = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If filesrc <> False Then Sheets("Sheet1").Hyperlinks.Add Anchor:=Sheets("Sheet1").Range("A1"), Address:=filesrc

This prompts the user for a file via a standard "Open" dialog and adds a hyperlink to the file in cell A1 of Sheet1. If you want the cell's text to be something other than the path and filename, you can add in the "TextToDisplay" argument to the method, e.g. TextToDisplay:="Click Here"

The If/Then logic is just so it doesn't freak out if the user cancels out of the Open dialog.

Is this what you wanted to accomplish or have I misunderstood the problem?

Edit: Oh yeah, the first argument in the GetOpenFilename method is just what is displayed in the "Files of type" drop-down in the Open dialog. The second argument (after the comma) is an actual working filter that you can use to pare down the names and extensions of the files you want the user to search through. In this case, I assumed you'd only want to browse for Excel workbooks, so I limited the search to .xls files.

Newton

That looks like it is probalby exactly what I want. I'll try it later today. Thanks for the help.