Open the file selected by user, name a range, use that name-index in another file's cell

[natarajan]

ActiveCell.FormulaR1C1 = "=SUMIF( c:\new\filea.xls!pnof,RC[-4],c:\new\fileb.xls!qtyf)"

This is how the macro formula used to be when I had the file selection as manual as in the user would have to save the files as filea & fileb in the folder c:\new\ .
Later on I thought of making it more interactive & so I used this procedure to get the user to select the file where he has the data (similar to filea & fileb).

X = Application.GetOpenFilename _
("Excel files (*.xls), *.xls", 2, "Open My Files")
Me.Hide
Workbooks.Open X

This did open the account & name ranges (not shown in the code abv.)

But the problem starts at the point as how to use the filename in this formula
ActiveCell.FormulaR1C1 = "=SUMIF( ???? !pnof,RC[-4], ???? !qtyf)"

???? -> how do i get the file name of the file that the user selected in this position ?
the variable X already has the path of the file I mean
X=c:\new\filea.xls
(if the file selected by the user is filea.xls)
I just cant get the filename in the formulaR1C1

Does someone have a solution to this ?

 

[Kenneth Hobson]

Try:

 ActiveCell.FormulaR1C1 = "=SUMIF(" & x & "!pnof,RC[-4]," & x & "!qtyf)"

 

[jindon]

natarajan<SCRIPT type=text/javascript> vbmenu_register("postmenu_1613658", true); </SCRIPT>

ActiveCell.FormulaR1C1 = "=SUMIF( ???? !pnof,RC[-4], ???? !qtyf)"

Above formula seems missing the sheet name...
try something like this

Dim wsName As String, fn As String, FolderName As String, myPath As String
X = Application.GetOpenFilename _
("Excel files (*.xls), *.xls", 2, "Open My Files")
wsName = "Sheet1"  '<- change to suite
fn = Mid$(X, InStrRev(X,"\")+1)
FolderName = Left$(X, InStrRev(X, "\"))
myPath = FolderName & "[" & fn & "]" & wsName
 
ActiveCell.FormulaR1C1 = "=SUMIF('" myPath & "'!pnof,RC[-4],'" myPath & "'!qtyf)"

 

[natarajan]

thanks guys it worked !
ActiveCell.FormulaR1C1 = "=SUMIF(" & x & "!pnof,RC[-4]," & x & "!qtyf)"<!-- / message -->