Copy a cell to all workbooks within a folder.
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 |
All times are GMT +1. The time now is 05:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com