You can add code to browse to the folder
Try
Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then
'run the other code
Else
Exit Sub
End If
myCountOfFiles = Get_File_Names( _
MyPath:=oFolder.Self.Path, _
Subfolders:=False, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)
If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If
Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A1:G1", _
StartCell:="", _
myReturnedFiles:=myFiles
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Mike Lewis" <(E-Mail Removed)> wrote in message news:E83B2B5E-7CB6-4370-A1F6-(E-Mail Removed)...
> Ron de Bruin,
>
> Ok, since you seem to be a wealth of knowledge, I would like to find out
> something else from you. If I am giving this spreadsheet to another person,
> how can I make it where they will not need to go into the macro and change
> the "my Path" section. Can this be done from the spreadsheet itself,
> meaning, can there be a place put on the spreadsheet that would ask them to
> put in their path or something easier so that macro access is not needed?
>
> Thanks so much for your help...You have helped me tremendously.
>
> "Ron de Bruin" wrote:
>
>> > Can you add the sheet to an existing workbook instead of a new workbook?
>>
>> Yes
>>
>> Set BaseWks = Thisworkbook.Worksheets("Yourworksheet")
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Mike Lewis" <(E-Mail Removed)> wrote in message news:03C4E9FA-EAE2-4255-A4B8-(E-Mail Removed)...
>> > Got it.....Now, one last question. The section below....
>> >
>> > 'Add a new workbook with one sheet
>> > Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
>> > Cnum = 1
>> >
>> > Can you add the sheet to an existing workbook instead of a new workbook?
>> > "Ron de Bruin" wrote:
>> >
>> >> Download the example workbook
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "Mike Lewis" <(E-Mail Removed)> wrote in message news:0A7E4186-A61B-4DBB-B00B-(E-Mail Removed)...
>> >> > Hello,
>> >> >
>> >> > I tried cutting and pasting, and I get an error that takes me back to the
>> >> > top with yellow. Here is what I cut and paste. Sub RDB_Merge_Data() turned
>> >> > yellow and Get_File_Names highlighted blue.
>> >> >
>> >> > Thoughts?
>> >> >
>> >> > Sub RDB_Merge_Data()
>> >> > Dim myFiles As Variant
>> >> > Dim myCountOfFiles As Long
>> >> >
>> >> > myCountOfFiles = Get_File_Names( _
>> >> > MyPath:="C:\Documents and
>> >> > Settings\lewism11\Desktop\Survey Subfile", _
>> >> > Subfolders:=False, _
>> >> > ExtStr:="*.xls", _
>> >> > myReturnedFiles:=myFiles)
>> >> >
>> >> > If myCountOfFiles = 0 Then
>> >> > MsgBox "No files that match the ExtStr in this folder"
>> >> > Exit Sub
>> >> > End If
>> >> >
>> >> > Get_Data _
>> >> > FileNameInA:=True, _
>> >> > PasteAsValues:=True, _
>> >> > SourceShName:="", _
>> >> > SourceShIndex:=1, _
>> >> > SourceRng:="A1:G1", _
>> >> > StartCell:="", _
>> >> > myReturnedFiles:=myFiles
>> >> >
>> >> > End Sub
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Start here
>> >> >> http://www.rondebruin.nl/copy3.htm
>> >> >>
>> >> >> Try the add-in
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl/tips.htm
>> >> >>
>> >> >>
>> >> >> "Mike Lewis" <(E-Mail Removed)> wrote in message
>> >> >> news:E7F348DE-6D15-4D7F-87CC-(E-Mail Removed)...
>> >> >> > Hello, I need some help. I have a survey that has one spreadsheet for each
>> >> >> > customer. I also have a master excel spreadsheet that these need to roll up
>> >> >> > to. Does anyone have suggestions of how I can roll up just the data from
>> >> >> > these spreadsheets to the master spreadsheet. I am not sure that I want all
>> >> >> > the spreadsheets to actually roll into the master spreadsheet or just the
>> >> >> > data. I would love to be able to do this with a macro as this will be a
>> >> >> > frequent survey.
>> >> >> >
>> >> >> > Thanks in advance for your help.
>> >> >>
>> >>
>>
>>
Similar Threads
