how to do some calculations within the different file paths using VBA

hi, everyone, I am new for VBA, please someone helps me on this problem.

the question is after i got the data from my local drive, i have a difficulty to calculate my weighted average in specific cell. I put what i wrote down for a reference. very appreciate your help.

sub WeightedAverageCalculation()
Dim pathname As String
Dim filename As String
Dim fullname As String
'S:\............" is my local file path
pathname = "S:\Jobs and Income Indicators Project\1994-2006 Files\Provinces\"
'filename is my using workbooks
filename = "Table27.1aAllYears.xls"
fullname = pathname & filename

'Range("b5").Select
'ActiveCell.FormulaR1C1 = _
'"='fullname'!r5c14"

Range("b6").Select
ActiveCell.FormulaR1C1 = _
"=sumproduct('fullname'!r6c3:r9c3,'fullname'!r6c14 :r9c14)/sum('fullname'!r6c3:r9c3)"
End Sub

leo

Sorry, you can't do it this way. You need to open an Excel Object to deal with it.

Hi, boblarson

Could you let me know how to do it? or provide an example for me? very appreciate you.

leo

Well, this code shows how to use the Excel Object Model to export to a file. It would be similar, to some point. Not sure exactly what you are trying to do and you might try restating what your intended outcome is (not the code, but what it is you are actually trying to accomplish).

hi, there,
thank you very much,,,,,.
I read your code you actually provided, but I have some difficulties to get the referable ideas from that, sorry about that.

Leo

What is it you are really needing? What are you actually needing as a result?

boblarson said:

What is it you are really needing? What are you actually needing as a result?

Click to expand...

hey, there,

I have a VBA module that I need to reference a file path in the module.

(1) I am wondering is there a way that I can make define the path as a varible and then reference the variable? Instead of typing:S:\Jobs and income indicators project\1994-2006 files\workbook's name.xls every time to revise my code.

(2) i am wondering that can I define a variable to be equal to the path such as Path = S:\Jobs and income indicators project\1994-2006 files\workbook's name.xls, then in the coding just use "Path" in place of S:\Jobs and income indicators project\1994-2006 files\workbook's name.xls

(3) if so, then i don't need to copy and paste this module over many times and then i just need to change the file name on every copy on the top of my code in the module once to be done.
that's it what i need.

Thank you for your kindly help and very appreciate.
Leo