Anybody can see why this code doesn't work?

[leowang]

Hi, buddy, anyone can help me to figure out why this one does not work. very appreciate you help. Thanks in advance!


Sub WeightedAverageCalculation()
'
'this one is doing weighted average calculation
'from different file paths, different workbooks and different worksheets
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim path As String
Dim filename As String, tabname As String
Dim fullname As String

'"S:\,,,,,| is my path under S drive
path = "S:\Jobs and Income Indicators Project\1994-2006 Files\Provinces\"
'filename is my workbook name
filename = "[Table27.1aAllYears.xls]"
tabname = "All Atlantic Provinces"
'fullname is combination of path and filename together
fullname = path & filename

'do some calculation in a new workbook and I work at a new worksheet 'named "all atlantic province"

Range("c5").Select
ActiveCell.FormulaR1C1 = "=SUM(" & "" & fullname & tabname & "" & "!r5c3:r7c3)"

'I just ignore the rests of them; because if this one works, others should 'work too

End Sub

 

[namliam]

Note the spaces in your tab name, tabnames either contain no spaces (hence the tip: Anything automated never use spaced names) or if they do contain spaces need to be enclosed by []

 

[chergh]

You may be getting confused between square brackets and apostrophes Namliam. As far as i can see what you need to do is change

ActiveCell.FormulaR1C1 = "=SUM(" & "" & fullname & tabname & "" & "!r5c3:r7c3)"

to

ActiveCell.FormulaR1C1 = "=SUM('" & "" & fullname & tabname & "" & "'!r5c3:r7c3)"

I've just added a couple of apostrophes.

 

[Rabbie]

Hi, buddy, anyone can help me to figure out why this one does not work. very appreciate you help. Thanks in advance!


Sub WeightedAverageCalculation()
'
'this one is doing weighted average calculation
'from different file paths, different workbooks and different worksheets
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim path As String
Dim filename As String, tabname As String
Dim fullname As String

'"S:\,,,,,| is my path under S drive
path = "S:\Jobs and Income Indicators Project\1994-2006 Files\Provinces\"
'filename is my workbook name
filename = "[Table27.1aAllYears.xls]"
tabname = "All Atlantic Provinces"
'fullname is combination of path and filename together
fullname = path & filename

'do some calculation in a new workbook and I work at a new worksheet 'named "all atlantic province"

Range("c5").Select
ActiveCell.FormulaR1C1 = "=SUM(" & "" & fullname & tabname & "" & "!r5c3:r7c3)"

'I just ignore the rests of them; because if this one works, others should 'work too

End Sub

When you say " Doesnt work" what exactly do you mean. Does it compile correctly? Does it give a runtime error? If so which line does it error on? Or does it just give the wrong result. More info will make it easier for us to help you

 

[leowang]

I solved it, and thank you all above to give the suggestion. that's great.

 

[namliam]

You may be getting confused between square brackets and apostrophes Namliam.

Excel vs Access

Yes apostrophes 'Something spaced' for tabs but [some filename] for files in excel, its always nice to be consistant huh M$ ??