MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Locating excel files somewhere else on a network for merge purposes

  Asked By: Everett    Date: Dec 11    Category: MS Office    Views: 649

We want any network user to run an excel file containing a macro that
will merge the data from three other excel files located in some
unknown location, to produce a new excel file.
Is there a piece of VBA code I can put in the macro file that will
locate the pathname of the three files elsewhere on the network?



1 Answer Found

Answer #1    Answered By: Abbas Hashmi     Answered On: Dec 11

I think there would be some requirements that have to be met.
Does the user  have a drive mapped to the network  servers?
Does the user have read permission for the network folder and files.
(And all "parent" folders)
It would help if you could narrow the search.
I mean, at my location, I have read access to 2,301,000 files
located in 345,000 folders. (which is certainly not ALL of the data  stored on
our servers!
I certainly wouldn't want to search through all of them if I didn't have to!
You can modify this to search for specific files:
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = "cmd*"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
MsgBox "There were no files  found."
End If
End With

hope this gets you moving in the right direction!