View Full Version : how to create file open dialogue box for consolidation
agnesz
08-02-2007, 06:38 AM
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.
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
08-02-2007, 08:25 AM
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
08-02-2007, 08:34 AM
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
08-02-2007, 08:39 AM
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...
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.