Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

UNC path and searches

  Asked By: Lorenzo    Date: Aug 24    Category: MS Office    Views: 709
  

What I am attempting to do is push data out to another excel file or
append. I am trying to use UNC paths. I guess my first quest is
...does VBA recognize UNC paths for files. It does for saving, but
doesn't look like it is for searches. I thought I had this working
weeks ago, but apparently not and I am trying to get this out this week.



I have an IF statement that searches for the file - if not found it goes
to add it. If the file is actually there I get a "do you want to
overwrite the existing file" and I don't...I want to append. Any help
is much appreciated.



By the way...I used the form for adding user data entry and although it
took me all day to get it to write back to the previous form...it is
working.
This is the search......



myDir = "\\gcoda58\Supplies\Supplies"

'myDir = CurDir

Set fsoFileSearch = Nothing



Set fsoFileSearch = Application.FileSearch



today = Now()

MyDate = DatePart("ww", today)

wbName = "Employee Upload_" & MyDate



With fsoFileSearch



.NewSearch

.LookIn = myDir

.Filename = wbName

.SearchSubFolders = True

.MatchTextExactly = True

.FileType = msoFileTypeExcelWorkbooks





This is the add and save



Workbooks.Add.Activate

ActiveWorkbook.SaveAs Filename:=myDir & "\" & wbName,
FileFormat:= _

xlNormal, Password:="xxxxxxx", WriteResPassword:="",
ReadOnlyRecommended:= _

False, CreateBackup:=False

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Tarron Thompson     Answered On: Aug 24

you need to add

.execute
End with

to the end of the with list. Then you need to examine the FoundFiles object
to see if it found  anything.

 
Answer #2    Answered By: Vid Fischer     Answered On: Aug 24

Sorry...my .execute is in an IF statement...it doesn't find my file
until it tries to create a new one and then tries to save over the
existing file...



With fsoFileSearch



.NewSearch

.LookIn = myDir

.Filename = wbName

.SearchSubFolders = True

.MatchTextExactly = True

.FileType = msoFileTypeExcelWorkbooks



If .Execute() > 0 Then



Workbooks.Open Filename:=myDir & "\" & wbName,
Password:="xxxxxxx"



And the end with is on down.

 
Answer #3    Answered By: Daniel Costa     Answered On: Aug 24

If I had more time, I'd actually try your code, but for now, have you tried
stepping through in the debugger using F8 to see what actually happens when
you run your code and inspecting the results of each variable assignment as
you go to make sure it's what you expect?

I'm not familiar with the FileSearch object - after a successful Execute,
since you have "search subdirectories" turned on, isn't there some way you
must find out the full path  to the found  file, since it can be in a
subfolder? If the subfolder search  is unintentional and all you're trying to
do is see if a certain file  exists in a certain folder, you don't need to
use FileSearch - you can just use the good old Dir() function.

 
Answer #4    Answered By: Grace Ellis     Answered On: Aug 24

I have not been successful with this. If I move the file  to my PC it
works, but I have to run this from a network drive. When I try to map
the drive it still doesn't work. Any suggestions?

 
Didn't find what you were looking for? Find more on UNC path and searches Or get search suggestion and latest updates.




Tagged: