Prompting file name dialog box in VB and using the name in cells

[mehlerak]

Hi,
I'm trying to prompt a user to select a file (without opening it) and then use the file name to generate links in the source file, pointing to specific cells in the selected file.

My code so far is:
' createNewEntry Macro
'

'
Filename = "='[" & Application.GetOpenFilename("Excel Files (*.xls), *.xls") _ & "]"
Range("A6:AE6").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks _
:=False, Transpose:=False
Range("AE6").Select
Application.CutCopyMode = False
Range("A6:AE6").Select
Selection.ClearContents
Range("B6").Select
ActiveCell.FormulaR1C1 = Filename & "Summary!R1C4"
Range("C6").Select
ActiveCell.FormulaR1C1 = Filename & "Summary!R2C4"
Range("H6").Select
ActiveCell.FormulaR1C1 = Filename & "Summary!R5C2"


End Sub

My main issue is that the references are not being recognized, due to the square brackets not being put in the right place ( '[' ']' ) and the apostrophes not being placed in the right place as well.

End state is that I need the references to work... Then I can reference the data from the workbook the user selected on a master spreadsheet that has this script.

Thanks!

 

[VoG]

I may be wrong but I'm pretty sure that you will need to open the file to do this. If so you need to get rid of the "[" etc.