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