Hi Andy

I have no time now but I try to make a example this evening or tomorrow for you.
I go Bowling now, see you



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Andy T" wrote in message ...
No probs Ron, it's a pleasure. I'm probably pushing my luck here and I'll
definitely owe you a beer for this one but is it possible to do a
Find/Replace action from the master spreadsheet? In the example you kindly
provided for me the code in the master spreadsheet determines what "text or
data" will be updated into Cell A1 of the spreadsheets in my remote folder.
Is it possible from the master spreadsheet to state that you want to "Find" a
certain piece of data in all of the spreadsheets in the remote folder and
"Replace" it with another piece of data?

I know this is incredibly cheeky so I thank you for your patience.

Kind Regards

Andy Tallent

"Ron de Bruin" wrote:

Hi Andy

Thanks for your feedback


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Andy T" wrote in message ...
Thanks Ron, it works a treat. I really appreciate your help.

Regards

Andy

"Ron de Bruin" wrote:

Hi Andy

I can't test it for you but try this.
Copy it all in a normal module


Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Sub ChangeOneCell_1()
Dim mybook As Workbook
Dim FNames As String

ChDirNet "\\JELLE\SharedDocs"

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Range("A1").Value = "Hi"
mybook.Close True
FNames = Dir()
Loop
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Andy T" wrote in message ...
Hi Ron,

Thanks for this. I am so close here but feel I am being a bit Tuesday
afternoon dim. Could you indulge me and let me know where exactly the code
should be included in your own. Many, many thanks.

Kind Regards

Sub ChangeOneCell_1()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
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
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Range("A1").Value = 250
mybook.Close True
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


"Ron de Bruin" wrote:

Hi Andy

Try to use this in my code example
http://www.rondebruin.nl/copy4.htm

'Previously posted by Rob Bovey:

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

' sample usage

Sub test()
Dim FName As Variant
ChDirNet "\\JELLE\SharedDocs"
FName = Application.GetOpenFilename
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Andy T" wrote in message ...
I am trying to find a way of using a master spreadsheet to update all
spreadsheets within a named folder. Ron be Bruin gives an excellent solution
to this exact problem where the named folder is located on your PCs hard
drive. My question is whether this functionality can be extended to a named
folder on a shared network drive.

Any assistance would be greatly appreciated.
--
Andy T