I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!

Hi Ladymuck

You can use the add-in
http://www.rondebruin.nl/merge.htm

Or add browse code (untested)
For others example workbook is on this page
http://www.rondebruin.nl/fso.htm

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("Shell.Application")

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then

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 If

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!

Hi Ladymuck,

At its simplest"

MyPath = InputBox("Please enter the filepath")
For something more sopisticated, giving the user the ability to browse to the folder, create a funtion coded as:
Function GetFolder(Optional Title As String, Optional RootFolder As Variant) As String
On Error Resume Next
GetFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
End Function

Then you can use:
MyPath = GetFolder(Title:="Find a Folder", RootFolder:=&H11)

Note that there's no error-checking in the above examples.

--
Cheers
macropod
[MVP - Microsoft Word]


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!

Works perfectly, thank you!

"Ron de Bruin" wrote:

Hi Ladymuck

You can use the add-in
http://www.rondebruin.nl/merge.htm

Or add browse code (untested)
For others example workbook is on this page
http://www.rondebruin.nl/fso.htm

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("Shell.Application")

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then

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 If

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!

Thanks for the suggestion, I used Ron's as it tied in with his existing code
but I've kept this snippet as it's bound to come in useful in the future

"macropod" wrote:

Hi Ladymuck,

At its simplest"

MyPath = InputBox("Please enter the filepath")
For something more sopisticated, giving the user the ability to browse to the folder, create a funtion coded as:
Function GetFolder(Optional Title As String, Optional RootFolder As Variant) As String
On Error Resume Next
GetFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
End Function

Then you can use:
MyPath = GetFolder(Title:="Find a Folder", RootFolder:=&H11)

Note that there's no error-checking in the above examples.

--
Cheers
macropod
[MVP - Microsoft Word]


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!

Works perfectly, thank you!

You are welcome

Will add it to the website this week
Maybe also useful for others

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
Works perfectly, thank you!

"Ron de Bruin" wrote:

Hi Ladymuck

You can use the add-in
http://www.rondebruin.nl/merge.htm

Or add browse code (untested)
For others example workbook is on this page
http://www.rondebruin.nl/fso.htm

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("Shell.Application")

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then

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 If

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!

I add the example to the workbook that you can download on the site

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Works perfectly, thank you!

You are welcome

Will add it to the website this week
Maybe also useful for others

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
Works perfectly, thank you!

"Ron de Bruin" wrote:

Hi Ladymuck

You can use the add-in
http://www.rondebruin.nl/merge.htm

Or add browse code (untested)
For others example workbook is on this page
http://www.rondebruin.nl/fso.htm

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("Shell.Application")

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then

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 If

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!