help to modify code to look to correct folder

[Corey]

I have the below code that is suppose to open and search through ALL excel
files withijn a specified folder for a match from the input box value.

It WAS working fine.
But now it seems to be looking to an incorrect folder, as it does not find a
match, although i know there is one.
Is there something i can do so that it ALWAYS looks to the designated excel
files in the folder ?

Sub ExampleTest2()

Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter a CUSTOMER Name (Use from
Examples)", "Company Name Here..")
SaveDriveDir = CurDir
MyPath = "Z:Costing Sheets"
ChDrive MyPath
' ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If LCase(mybook.Worksheets(i).Range("B3").Value) = LCase(input1) Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop
End Sub








Regards

Corey

 

[Corey]

I think the problem is that MS Excel will not STORE my Default Save Location
Network Address as Z:Costing Sheets

Tools- Options - General - (Default Save Location)

This address(z:\Costing Sheets) after i save and close Excel, reverts back
when i reopen Excel to:
\\Office2\my documents\Costing Sheets

WHY will Excel NOT save it a Z ?

 

[Zzyzzyth]

This may be a silly question, but in your Workbook_Open event, can't you just
ChDir "Z:\Costing Sheets"?

 

[Corey]

Thanks for the reply
That seems to working for now.
I will try it for a while to see if it keeps the path fixed.

Corey....

 

[JLGWhiz]

Out of curiosity, do you have a Z:\ drive on your system? I guess it could
be set up but I'm wondering when it would logically apply???

 

[Corey]

Yes it is a mapped network drive