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

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

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

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

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

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

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

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

Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?


Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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



"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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

That ChDirNet API call seems to work nicely with mapped (or local) drives, too.

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

could be just
ChDirNet SaveDriveDir

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I think I'd do something like and avoid changing the drive/folder altogether.

Option Explicit
Sub ChangeOneCell_2A()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "\\mysharename\foldername"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
wkbk.Worksheets(1).Cells.Replace What:="Ron", _
Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

wkbk.Close savechanges:=True
Next fCtr
End If

End Sub






Ron de Bruin wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?

Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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

"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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













--

Dave Peterson

Hi Dave

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

I see I forgot to add this in this part of the macro


And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I never see this problem when I test it Dave
Thanks for your example
I will try when I get home

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



"Dave Peterson" wrote in message ...
That ChDirNet API call seems to work nicely with mapped (or local) drives, too.

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

could be just
ChDirNet SaveDriveDir

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I think I'd do something like and avoid changing the drive/folder altogether.

Option Explicit
Sub ChangeOneCell_2A()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "\\mysharename\foldername"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
wkbk.Worksheets(1).Cells.Replace What:="Ron", _
Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

wkbk.Close savechanges:=True
Next fCtr
End If

End Sub






Ron de Bruin wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?

Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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

"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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













--

Dave Peterson

Hi Ron,

Thank you very much for this, it works perfectly. I am sorry to hear the
bowling didn't go too well!

Kind Regards

Andy Tallent

"Ron de Bruin" wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?


Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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



"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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

Hi Dave

Thanks for the lesson Dave and for adding your wisdom to this tread.
You know that I am a youngster<vbg

Is there a other way without using Rob's example to use GetOpenFilename for
a folder on a other system in the network

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


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



"Ron de Bruin" wrote in message ...
Hi Dave

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

I see I forgot to add this in this part of the macro


And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I never see this problem when I test it Dave
Thanks for your example
I will try when I get home

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



"Dave Peterson" wrote in message ...
That ChDirNet API call seems to work nicely with mapped (or local) drives, too.

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

could be just
ChDirNet SaveDriveDir

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I think I'd do something like and avoid changing the drive/folder altogether.

Option Explicit
Sub ChangeOneCell_2A()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "\\mysharename\foldername"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
wkbk.Worksheets(1).Cells.Replace What:="Ron", _
Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

wkbk.Close savechanges:=True
Next fCtr
End If

End Sub






Ron de Bruin wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?

Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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

"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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













--

Dave Peterson

With xl2002+, you could use:
Application.FileDialog(msoFileDialogFolderPicker). Show

I don't have a network to test, but I think the BrowseForFolder stuff doesn't
show those UNC paths.

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

Or John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip29.htm

(Can you test for UNC paths?)

And you could use the application.getopenfilename and ignore the filename.

Option Explicit
Sub testme02()

Dim myFolder As Variant
Dim mySplit As Variant

myFolder = Application.GetSaveAsFilename _
(InitialFileName:="FileNameWillBeIgnored!")

If myFolder = False Then
Exit Sub
End If

mySplit = Split97(CStr(myFolder), "\")
myFolder = Left(myFolder, Len(myFolder) - Len(mySplit(UBound(mySplit))))

MsgBox myFolder

End Sub

'from Tom Ogilvy
Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

=====
But I think that the user has to type in those UNC paths (or enough of a path to
be able to drill down to the requested folder.)



Ron de Bruin wrote:

Hi Dave

Thanks for the lesson Dave and for adding your wisdom to this tread.
You know that I am a youngster<vbg

Is there a other way without using Rob's example to use GetOpenFilename for
a folder on a other system in the network

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

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

"Ron de Bruin" wrote in message ...
Hi Dave

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

I see I forgot to add this in this part of the macro


And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I never see this problem when I test it Dave
Thanks for your example
I will try when I get home

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



"Dave Peterson" wrote in message ...
That ChDirNet API call seems to work nicely with mapped (or local) drives, too.

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

could be just
ChDirNet SaveDriveDir

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I think I'd do something like and avoid changing the drive/folder altogether.

Option Explicit
Sub ChangeOneCell_2A()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "\\mysharename\foldername"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
wkbk.Worksheets(1).Cells.Replace What:="Ron", _
Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

wkbk.Close savechanges:=True
Next fCtr
End If

End Sub






Ron de Bruin wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?

Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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

"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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













--

Dave Peterson



--

Dave Peterson

Hi Dave

Thanks for your reply

I think Rob's example is the easiest.
I will check out the code tomorrow(bed time now)

(Can you test for UNC paths?)
I have a home network

I post back tomorrow



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



"Dave Peterson" wrote in message ...
With xl2002+, you could use:
Application.FileDialog(msoFileDialogFolderPicker). Show

I don't have a network to test, but I think the BrowseForFolder stuff doesn't
show those UNC paths.

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

Or John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip29.htm

(Can you test for UNC paths?)

And you could use the application.getopenfilename and ignore the filename.

Option Explicit
Sub testme02()

Dim myFolder As Variant
Dim mySplit As Variant

myFolder = Application.GetSaveAsFilename _
(InitialFileName:="FileNameWillBeIgnored!")

If myFolder = False Then
Exit Sub
End If

mySplit = Split97(CStr(myFolder), "\")
myFolder = Left(myFolder, Len(myFolder) - Len(mySplit(UBound(mySplit))))

MsgBox myFolder

End Sub

'from Tom Ogilvy
Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

=====
But I think that the user has to type in those UNC paths (or enough of a path to
be able to drill down to the requested folder.)



Ron de Bruin wrote:

Hi Dave

Thanks for the lesson Dave and for adding your wisdom to this tread.
You know that I am a youngster<vbg

Is there a other way without using Rob's example to use GetOpenFilename for
a folder on a other system in the network

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

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

"Ron de Bruin" wrote in message ...
Hi Dave

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

I see I forgot to add this in this part of the macro


And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I never see this problem when I test it Dave
Thanks for your example
I will try when I get home

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



"Dave Peterson" wrote in message ...
That ChDirNet API call seems to work nicely with mapped (or local) drives, too.

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

could be just
ChDirNet SaveDriveDir

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I think I'd do something like and avoid changing the drive/folder altogether.

Option Explicit
Sub ChangeOneCell_2A()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "\\mysharename\foldername"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
wkbk.Worksheets(1).Cells.Replace What:="Ron", _
Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

wkbk.Close savechanges:=True
Next fCtr
End If

End Sub






Ron de Bruin wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?

Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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

"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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













--

Dave Peterson



--

Dave Peterson

I thought you wanted the user to be able to browse for that folder.

If you know the folder, Rob's API solution to change folders is very nice.

But you can do it all without changing the folders--just use the folder name.

This assumes that you're picking up all the *.xls (or you know which ones you
want).

But if you want the user to pick, I like Rob's function.


Ron de Bruin wrote:

Hi Dave

Thanks for your reply

I think Rob's example is the easiest.
I will check out the code tomorrow(bed time now)

(Can you test for UNC paths?)
I have a home network

I post back tomorrow

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

"Dave Peterson" wrote in message ...
With xl2002+, you could use:
Application.FileDialog(msoFileDialogFolderPicker). Show

I don't have a network to test, but I think the BrowseForFolder stuff doesn't
show those UNC paths.

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

Or John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip29.htm

(Can you test for UNC paths?)

And you could use the application.getopenfilename and ignore the filename.

Option Explicit
Sub testme02()

Dim myFolder As Variant
Dim mySplit As Variant

myFolder = Application.GetSaveAsFilename _
(InitialFileName:="FileNameWillBeIgnored!")

If myFolder = False Then
Exit Sub
End If

mySplit = Split97(CStr(myFolder), "\")
myFolder = Left(myFolder, Len(myFolder) - Len(mySplit(UBound(mySplit))))

MsgBox myFolder

End Sub

'from Tom Ogilvy
Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

=====
But I think that the user has to type in those UNC paths (or enough of a path to
be able to drill down to the requested folder.)



Ron de Bruin wrote:

Hi Dave

Thanks for the lesson Dave and for adding your wisdom to this tread.
You know that I am a youngster<vbg

Is there a other way without using Rob's example to use GetOpenFilename for
a folder on a other system in the network

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

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

"Ron de Bruin" wrote in message ...
Hi Dave

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

I see I forgot to add this in this part of the macro


And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I never see this problem when I test it Dave
Thanks for your example
I will try when I get home

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



"Dave Peterson" wrote in message ...
That ChDirNet API call seems to work nicely with mapped (or local) drives, too.

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

could be just
ChDirNet SaveDriveDir

And I think I wouldn't exit sub if no files are found. It may be nicer to
change the directory back.

And I think that sometimes when you update files in place, that windows/excel
could see the updated file as a new file. I think I would retrieve a list of
all the files, then loop through that list.

I think I'd do something like and avoid changing the drive/folder altogether.

Option Explicit
Sub ChangeOneCell_2A()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "\\mysharename\foldername"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
wkbk.Worksheets(1).Cells.Replace What:="Ron", _
Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

wkbk.Close savechanges:=True
Next fCtr
End If

End Sub






Ron de Bruin wrote:

Hi Andy

I hope you enjoy the bowling
Yes but 140-150 is not so good<g

Here is a example that use Replace to change "Ron" to "Piet" in all cells in the first worksheet
Do you want this?

Sub ChangeOneCell_2()
Dim mybook As Workbook
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
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).Cells.Replace What:="Ron", Replacement:="Piet", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

mybook.Close True
FNames = Dir()
Loop

If Left(SaveDriveDir, 1) < "\" Then
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

Application.ScreenUpdating = True
End Sub

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

"Andy T" wrote in message ...
Thanks Ron, I really appreciate this. I hope you enjoy the bowling.

Andy

"Ron de Bruin" wrote:

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













--

Dave Peterson



--

Dave Peterson

--

Dave Peterson