Using Sub RDB_Merge_Data()

[Ladymuck]

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!

 

[Ron de Bruin]

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

 

[macropod]

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.

 

[Ladymuck]

Works perfectly, thank you!

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

 

[Ladymuck]

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

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]

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!

 

[Ron de Bruin]

Works perfectly, thank you!

You are welcome

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

 

[Ron de Bruin]

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