Make Variable a File Path

[Chris66]

I have a VBA module that I need to reference a file path 3 or 4 times in the module. Is there a way that I can make define the path as a varible and then reference the variable?
Instead of typing:F\dept\matl\Leb\file name.xls, can I define a variable to be equal to the path such as
Path = F\dept\matl\Leb\file name.xls
then in the coding just use Path in place of F\dept\matl\Leb\file name.xls.

I tried using:
Dim path as object
set path = F\dept\matl\Leb\file name.xls
But I got a Compile Error: Type Mismatch I have to copy this module over 200 times and change the file name on every copy, I would really like to just go to the top of the code and change the file name once and be done. Any help would be appreaciated.

 

[RichMorrison]

Dim strPath As String

RichM

 

[WayneRyan]

Chris,

Dim PathName As String
Dim FileName As String
Dim FullName As String

PathName = "F:\dept\matl\Leb\"
FileName = "FileName.xls"
FullName = PathName & FileName

hth,
Wayne

 

[leowang]

Chris,

Dim PathName As String
Dim FileName As String
Dim FullName As String

PathName = "F:\dept\matl\Leb\"
FileName = "FileName.xls"
FullName = PathName & FileName

hth,
Wayne

Hey,Wayne,
I have a question regarding this with the same logic, but I could not calculate using formula, so my question what i should do ? Thank you so much and very appreciate.
sub WeightedAverageCalculation()
Dim pathname As String
Dim filename As String
Dim fullname As String
pathname = "S:\Jobs and Income Indicators Project\1994-2006 Files\Provinces\"
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

 

[boblarson]

Hey,Wayne,
I have a question regarding this with the same logic, but I could not calculate using formula, so my question what i should do ? Thank you so much and very appreciate.
sub WeightedAverageCalculation()
Dim pathname As String
Dim filename As String
Dim fullname As String
pathname = "S:\Jobs and Income Indicators Project\1994-2006 Files\Provinces\"
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

Don't post the same question in multiple places. This is a duplicate of this:

http://www.access-programmers.co.uk/forums/showthread.php?t=181985

EDIT: Sorry, this one was first - the other one was the duplicate, but I guess the instructions are - don't post a follow up to a 6 year old thread. Post a new question and refer back to this one if you want.