Referencing another worksheet in a function

[MikeSD]

I have a simple problem that's driving me nuts. I want to reference data in another spreadsheet. I can do it ok, if I hard code the second worksheet into the first but I'd like to be able to select it from a dialog and use the filename in a function in the first spreadsheet. I've tried various combinations of variables and continually get some error. So, I'll ask the experts.

First, here's what I want to do:

1) Open my working spreadsheet that contains my macros and summary data.
2) Run the macro, so that it will open a dialog and let me select the spreadsheet to use
3) That name will be saved in a variable (actually I don't care if the file is opened, left open or remains closed) but I need the name.
4) Compute the AVERAGE of a column of data (column range actually) in that opened spreadsheet and enter the data into the working spreadsheet.
5) Actually I'd like to do #4 one of 2 ways. One by entering the forumula to do the coputation (as below) and the second by just entering the value vs the forumula. But my main problem is how to select that 2nd filename, as a variable, to be used in my AVERAGE function in the 1st.

This works: ActiveCell.FormulaR1C1 = "=AVERAGE('LV_new.csv'!R2C3:R20C3)"

I'd like to replace 'LV_new.csv' with a variable. "LV_new" is the actual 2nd spreadsheet filename but this filename can be different, hence the need for a file picker and filename variable, in the AVERAGE function. The average function is placed in the cell in the first spreadsheet and the average is correctly calculated. I'd like to replace the hard coded LV_new and sheet, with a variable so that I can run the macro, without knowing the filename before hand.

I tried adding:
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Please select a file")
ActiveCell.FormulaR1C1 = "=AVERAGE([NewFN]!R4C3:R9C3)"

But it doesn't work. I get the dialog (actually two times) and it enters the formula as:
"=AVERAGE([NewFN]NewFN!R4C3:R9C3)" which is incorrect (I think). And produces an "REF" error in the cell. I used a MsgBox to display NewFN and it's correct. The file is a CSV file.

I'm also not sure why it's giving me the dialog box twice to choose the file. Might be something with trying to ask me to select the sheet but all I get is another dialog asking for the file. BTW, that is the entire code in the Sub Ftn. I don't have other code that could explain this double request.

This is the entire code.

Please Login or Register  to view this content.

Suggestions on the best way to do this?

The code above doesn't do what I want and produces a REF error in the cell. The data in the second spreadsheet is simple integers and works when I hard code the filename. I think the REF is because the AVERAGE function is treating NewFN as the actual filename instead of a variable. I guess I just need to know how to put a variable there.

[patel45]

attach please two samples

[MikeSD]

Attatch 2 samples of what? Two things that don't work because I have nothing that works. The problem is simple.

Spreadsheet 1: Cell R[4]C[3] is to contain the AVERAGE of data in range of cells in spreadsheet 2
Spreadsheet 2: Contains the following data
Row[4]C[3] = 1
Row[5]C[3] = 2
Row[6]C[3] = 3
Row[7]C[3] = 4
Row[8]C[3] = 5
Row[9]C[3] = 6

This is just an example to get the correct filename reference to be used in Spreadsheet 1.

ActiveCell.FormulaR1C1 = "=AVERAGE([NewFN]!R4C3:R9C3)"

All I need to know is how to reference the data in spreadsheet 2, using a variable for the filename (CSV)