How to get the file path and name into Consolidate Sources:

Hi I just start using vba macro and I need to know how to get the file path and name into Consolidate Sources when the user using browse to open the file.
I try to take it from the my_FileName but it always show error which is cannot identify the path and the name of the file.
This is my code for that part:

Dim my_FileName As Variant
Dim data As Variant




my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")




    If my_FileName <> False Then




        Workbooks.Open Filename:=my_FileName




    End If
    
data = my_FileName
    
With Worksheets("Sheet2")
    .Range("A1").Consolidate Sources:="'" & [data] & " Sheet1'!C1:C8", Function:=xlAverage, _
        TopRow:=True, LeftColumn:=True, CreateLinks:=False
End With

Hopefully there is someone out there who can show me what is wrong with that and how to fix it.
Thanks before.

Re: How to get the file path and name into Consolidate Sources:

Quote from AlanSidman;778717

Try this:

Code

Option Explicit




Sub GetFileNamePath()
Dim GetFile As String
Dim Path As String
GetFile = ActiveWorkbook.FullName
Path = ActiveWorkbook.Path
MsgBox (Path)
MsgBox (GetFile)
End Sub

Display More

Thanks for the reply. But how I put the path and name into the consolidate sources to make it works?