Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to look at all sheets not just specified sheet ?
I have been assisted with this code by some very helpful people, but i have
narrowed it down to these lines below that i need to modify slightly. How can i change the [Worksheets(4)] in (2)two instances below to refer to ALL(could be anywhere from 2-49) sheets in the workbooks instead ? If mybook.Worksheets(4).Range("B3").Value = input1 And mybook.Worksheets(4).Range("D3").Value = input2 Then mybook.Worksheets(4).Copy After:=basebook.Sheets(basebook.Sheets.Count) The 3rd instance of Worksheets(4)[Worksheets(4).Copy] needs to be the sheet that contains the 2 range values (B3" & "D3"), where the other 2 instances [worksheets(4)] need to refer to ALL sheets in the workbook. Regards Corey.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to look at all sheets not just specified sheet ?
Corey,
Add the following (wrapping around your code with 3 changes) and it should work: Dim i as Integer mybook.Activate For i = 2 to Sheets.Count ' Put the code you have below here, replacing the "Worksheets(4)" with "Sheets(i)" in all 3 instances Next i I don't know of a way to do all at once, only to step through each sheet one at a time... David How can i change the [Worksheets(4)] in (2)two instances below to refer to ALL(could be anywhere from 2-49) sheets in the workbooks instead ? If mybook.Worksheets(4).Range("B3").Value = input1 And mybook.Worksheets(4).Range("D3").Value = input2 Then mybook.Worksheets(4).Copy After:=basebook.Sheets(basebook.Sheets.Count) The 3rd instance of Worksheets(4)[Worksheets(4).Copy] needs to be the sheet that contains the 2 range values (B3" & "D3"), where the other 2 instances [worksheets(4)] need to refer to ALL sheets in the workbook. Regards Corey.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to look at all sheets not just specified sheet ?
Corey,
The only time that I mess with documents on a network folder is when I already have the network share mapped to a drive letter through the OS. If you map the drive through the OS, check the box to restore the connection upon reboot it will always have the same drive letter. Then, you can use the path of "X:\.....\my documents\folder name", eliminate the ChDrive line and the ChDir will work fine. (Changing the X:\... to the drive letter and any higher level folders before the "My Documents") I haven't messed with the ChDrive action before, but it could also be causing an error since you are giving it a path instead of a drive letter, and if it isn't already mapped, it could be looking for the username/password for the network share. David "Corey" wrote in message ... Thanks David for the reply. I can seem to get the code to work great if i set it to a local folder,(Did this to test and setup) But as i need it to look to a folder on a network as: MyPath = "\\Office2\my documents\folder name" ChDrive MyPath ChDir MyPath I get an error in the ChDrive My Path and ChDir My Path Any idea's whay that is ? And how to rectify it. Corey.... Regards Corey McConnell |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to look at all sheets not just specified sheet ?
David, I had the same thoughts, and actually went through MY Computer rather than My Network Places, using "Z:\Costing" instead of \\Office2\my documents\Costing, But i get the same error for some reason. In the set up of the code(from Ron De Bruin's site) it says it is suitable for Network applications.... Regards Corey.... "Dove" wrote in message ... Corey, The only time that I mess with documents on a network folder is when I already have the network share mapped to a drive letter through the OS. If you map the drive through the OS, check the box to restore the connection upon reboot it will always have the same drive letter. Then, you can use the path of "X:\.....\my documents\folder name", eliminate the ChDrive line and the ChDir will work fine. (Changing the X:\... to the drive letter and any higher level folders before the "My Documents") I haven't messed with the ChDrive action before, but it could also be causing an error since you are giving it a path instead of a drive letter, and if it isn't already mapped, it could be looking for the username/password for the network share. David "Corey" wrote in message ... Thanks David for the reply. I can seem to get the code to work great if i set it to a local folder,(Did this to test and setup) But as i need it to look to a folder on a network as: MyPath = "\\Office2\my documents\folder name" ChDrive MyPath ChDir MyPath I get an error in the ChDrive My Path and ChDir My Path Any idea's whay that is ? And how to rectify it. Corey.... Regards Corey McConnell |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to look at all sheets not just specified sheet ?
chdir doesn't work with the \\Servername format.
Previously posted by Rob Bovey: ' Placed at the top of a general module outside any procedures: 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 FindFile() ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp" fName = Application.GetOpenFileName End Sub -- Regards, Tom Ogilvy "Corey" wrote in message ... David, I had the same thoughts, and actually went through MY Computer rather than My Network Places, using "Z:\Costing" instead of \\Office2\my documents\Costing, But i get the same error for some reason. In the set up of the code(from Ron De Bruin's site) it says it is suitable for Network applications.... Regards Corey.... "Dove" wrote in message ... Corey, The only time that I mess with documents on a network folder is when I already have the network share mapped to a drive letter through the OS. If you map the drive through the OS, check the box to restore the connection upon reboot it will always have the same drive letter. Then, you can use the path of "X:\.....\my documents\folder name", eliminate the ChDrive line and the ChDir will work fine. (Changing the X:\... to the drive letter and any higher level folders before the "My Documents") I haven't messed with the ChDrive action before, but it could also be causing an error since you are giving it a path instead of a drive letter, and if it isn't already mapped, it could be looking for the username/password for the network share. David "Corey" wrote in message ... Thanks David for the reply. I can seem to get the code to work great if i set it to a local folder,(Did this to test and setup) But as i need it to look to a folder on a network as: MyPath = "\\Office2\my documents\folder name" ChDrive MyPath ChDir MyPath I get an error in the ChDrive My Path and ChDir My Path Any idea's whay that is ? And how to rectify it. Corey.... Regards Corey McConnell |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to look at all sheets not just specified sheet ?
Thanks Tom.
Got the whole project up and running now. Finally.... Thanks and thanks to ALL who assisted me with my constant posts over the past weeks. Corey.... "Tom Ogilvy" wrote in message ... chdir doesn't work with the \\Servername format. Previously posted by Rob Bovey: ' Placed at the top of a general module outside any procedures: 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 FindFile() ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp" fName = Application.GetOpenFileName End Sub -- Regards, Tom Ogilvy "Corey" wrote in message ... David, I had the same thoughts, and actually went through MY Computer rather than My Network Places, using "Z:\Costing" instead of \\Office2\my documents\Costing, But i get the same error for some reason. In the set up of the code(from Ron De Bruin's site) it says it is suitable for Network applications.... Regards Corey.... "Dove" wrote in message ... Corey, The only time that I mess with documents on a network folder is when I already have the network share mapped to a drive letter through the OS. If you map the drive through the OS, check the box to restore the connection upon reboot it will always have the same drive letter. Then, you can use the path of "X:\.....\my documents\folder name", eliminate the ChDrive line and the ChDir will work fine. (Changing the X:\... to the drive letter and any higher level folders before the "My Documents") I haven't messed with the ChDrive action before, but it could also be causing an error since you are giving it a path instead of a drive letter, and if it isn't already mapped, it could be looking for the username/password for the network share. David "Corey" wrote in message ... Thanks David for the reply. I can seem to get the code to work great if i set it to a local folder,(Did this to test and setup) But as i need it to look to a folder on a network as: MyPath = "\\Office2\my documents\folder name" ChDrive MyPath ChDir MyPath I get an error in the ChDrive My Path and ChDir My Path Any idea's whay that is ? And how to rectify it. Corey.... Regards Corey McConnell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to Add the same header from my first sheet into 20 sheets | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
copy and paste from different sheets into one sheet using a VB code | Excel Programming |