how to create file open dialogue box for consolidation

[agnesz]

I have two documents that I need to consolidate every Monday. Eeach Monday they will be saved in either a different location or with a different filename. How can I add an file open dialogue into my macro to ask user which files they want to consolidate. This is what I have currently, but this forces me to keep the two report names and locations static.

Sub consolidate()

Range("a6").Select

Selection.consolidate Sources:=Array( _
"'C:\Documents and Settings\y000aaz\Desktop\[mce report.xls]mce mcf je - ALL - 1'!R7C24:R5000C169" _
, _
"'C:\Documents and Settings\y000aaz\Desktop\[mcf report.xls]mce mcf je - ALL - 1'!R7C22:R5000C114" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End Sub

Thank you to anyone that can help.

[rory]

You can use GetOpenFileName:
varFileName1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 1")
If TypeName(varFileName1) = "Boolean" Then Exit Sub

You will need to then strip the .xls off the end of varfilename1 and add it into your source string.

HTH
Rory

[agnesz]

Sorry, but since I'm a total newbie at this and whatever i know is self-taught, can you be a little bit more specific about where to make a reference to the varFileName1 and varFileName2?
Sorry to be a pain... I so appreciate your help.
Thank you!

What I did so far was this....

Sub consolidatetest()

varFileName1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 1")
If TypeName(varFileName1) = "Boolean" Then Exit Sub

varFileName2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 2")
If TypeName(varFileName2) = "Boolean" Then Exit Sub

Selection.consolidate Sources:=Array( _
"'C:\Documents and Settings\y000aaz\Desktop\[varFileName1]'!$x$7:$fj$10000" _
, _
"'C:\Documents and Settings\y000aaz\Desktop\[varFileName2]'!$v$7:$fj$10000" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End Sub

[Bob Phillips]

Sub consolidatetest()

varFilename1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 1")
If TypeName(varFilename1) = "Boolean" Then Exit Sub

varFileName2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 2")
If TypeName(varFileName2) = "Boolean" Then Exit Sub

Selection.Consolidate Sources:=Array("'" & varFilename1 & "'!$x$7:$fj$10000", _
"'" & varFileName2 & "'!$v$7:$fj$10000"), _
Function:=xlSum, TopRow:=True, _
LeftColumn:=True, CreateLinks:=False

End Sub

[agnesz]

tried it a few times...and it keeps telling me "cannot open consolidation source file 'mce report.xls'"

any thoughts?

do i have to specify a tab within the report? though there's only one in it...

[rory]

Yes, you need a sheet name - try this:
Sub consolidatetest()
Dim varfilename1, varfilename2
Dim strSheet As String
strSheet = "mce mcf je - ALL - 1"
varfilename1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 1")
If TypeName(varfilename1) = "Boolean" Then Exit Sub

varfilename2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select consolidation file 2")
If TypeName(varfilename2) = "Boolean" Then Exit Sub

Selection.Consolidate Sources:=Array("'" & varfilename1 & strSheet & "'!$x$7:$fj$10000", _
"'" & varfilename2 & strSheet & "'!$v$7:$fj$10000"), _
Function:=xlSum, TopRow:=True, _
LeftColumn:=True, CreateLinks:=False

End Sub


Regards,
Rory