Hi All,
I'm am using Ron de Bruin's code to Merge Data from all workbooks in a
folder. The workbooks that I have been merging have been moved onto
sharepoint and I need to update my macro to deal with this. I need to allow
users to browse to the sharepoint folder as the name will change each month.
My attempts to update the code (shown below) allows the user to browse
sharepoint but I can't make a selection until I get to an individual excel
file (and I want to select all files within a folder, including any
subfolders) and then I get an error messsage. (RDB's brilliant code calls on
other macros which I haven't copied here.)
Can anyone help??
Thanks lots
P
Sub ConsolidateDetail()
Dim MyFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("InternetExplorer.Application")
oApp.Visible = True

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'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:=True, _
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:="Move", _
SourceShIndex:=1, _
SourceRng:="", _
StartCell:="A27", _
myReturnedFiles:=MyFiles

End If

'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

If your users have access to the sharepoint site itself, then they have
access to the "regular" directory structure as well. Basically, treat it like
they are on a network drive, with a path that starts with 'share'; ask your
IT department for the exact path- ours is:
share.ourcompanyname\directory\subdirectory

Then you can use standard coding to select directories and process files,
without having to use IE and access these files via a web interface.

Note: This approach works for a sharepoint site within our company, but I
haven't tried accessing "public" sharepoint sites from outside a company or
firewall

HTH,
Keith


Option Explicit
Global asd 'variant 1-D array

Sub SrchForFiles()
Dim fldr As String

fldr = "\\share.ourcompany.com\finance\bxcs\"
asd = ListFiles(fldr, True)
etc.


"PJ71" wrote:

Hi All,
I'm am using Ron de Bruin's code to Merge Data from all workbooks in a
folder. The workbooks that I have been merging have been moved onto
sharepoint and I need to update my macro to deal with this. I need to allow
users to browse to the sharepoint folder as the name will change each month.
My attempts to update the code (shown below) allows the user to browse
sharepoint but I can't make a selection until I get to an individual excel
file (and I want to select all files within a folder, including any
subfolders) and then I get an error messsage. (RDB's brilliant code calls on
other macros which I haven't copied here.)
Can anyone help??
Thanks lots
P
Sub ConsolidateDetail()
Dim MyFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("InternetExplorer.Application")
oApp.Visible = True

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'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:=True, _
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:="Move", _
SourceShIndex:=1, _
SourceRng:="", _
StartCell:="A27", _
myReturnedFiles:=MyFiles

End If

'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub