| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
| sending data from one cell to another (not using copy & paste) | Excel Worksheet Functions | |||
| copy a cell value not its function | Excel Discussion (Misc queries) | |||
| copy paste cell character limit | Excel Discussion (Misc queries) | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
