use variable in formula

[mocnak]

hi,
i wrote a formula to cell, count SUM of cells from another workbook : Cells(1, 1).Formula = "=(SUM('" & Path & "[" & Filename & "]" & sheetname & "'!" & RangeH & ")"
I am using msoFileDialogFolderPicker to select variable Path and Filename = Excel.xls, otherwise it doesn't work. Is it possible to use only 1 variable for Path and Filename ?
variables sheetname and RangeH are changing after loop, but Path and Filename are the same always.

[shrivallabha]

I am not sure I understand your requirement but to get path and filename together you can try like this:

Public Sub GetPathAndFileName()
Dim strFileName As String
strFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
If strFileName <> "False" Then
MsgBox strFileName
End If
End Sub

[mocnak]

formula without variables looks like this : Cells(1, 1).FormulaR1C1 = "=(SUM('C:/Users/User1/Desktop/[Excel.xlsm]Sheet1'!C8)
and i want "C:/Users/User1/Desktop/[Excel.xlsm]" to be as variable, so I can use it as path in formula in cell A1. but for some reason, it works only when i put path and filename separately.

[shrivallabha]

I don't know if it is possible to produce those square brackets around filename directly using some method. See if the following suits your purpose:

Public Sub GetPathAndFileName()
Dim vFileName As Variant
Dim strFileName As String
strFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
vFileName = Split(.SelectedItems(1), Application.PathSeparator)
vFileName(UBound(vFileName)) = "[" & vFileName(UBound(vFileName)) & "]"
strFileName = Join(vFileName, Application.PathSeparator)
End If
End With
End Sub