Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
How do I add another drive to search for when loading the workbooks
listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
I'll let you fill in the filenames, but this will check if a file exists in
F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 10:43, "steve" wrote:
I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() * * If Len(Dir("f:\test.xlsx")) 0 Then * * * * Workbooks.Open ("f:\test.xls") * * * * Else * * * * Workbooks.Open ("g:\test.xls") * * End If End Sub Regards * Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs *the pen might be recognised as a different drive letter on each one. I only need it look in say *"f" *and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Thanks - cant try it til tonight - thanks - will let you know how it goes Johnnyboy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 10:43, "steve" wrote:
I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() * * If Len(Dir("f:\test.xlsx")) 0 Then * * * * Workbooks.Open ("f:\test.xls") * * * * Else * * * * Workbooks.Open ("g:\test.xls") * * End If End Sub Regards * Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs *the pen might be recognised as a different drive letter on each one. I only need it look in say *"f" *and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
You only need to check if one of the files is on the F drive and it will
open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 12:41, "steve" wrote:
You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then * * Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") * * Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner..xls") Else * * Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") * * Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' * * Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") * * Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") * * Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards * Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, * is it .... ? Sub test() * * If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then * * * * Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") * * * * Else * * * * Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") * * If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then * * * * Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") * * Else * * * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT *ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Awesome ! thanks V V much - John. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 12:41, "steve" wrote:
You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then * * Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") * * Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner..xls") Else * * Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") * * Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' * * Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") * * Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") * * Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards * Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, * is it .... ? Sub test() * * If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then * * * * Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") * * * * Else * * * * Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") * * If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then * * * * Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") * * Else * * * * Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT *ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
Do you have the other code, so I could take a look.
Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 13:41, "steve" wrote:
Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message .... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. *sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. *Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
OK sorry missed that bit, so the macro you have already starts when you open
a file. That macro should be in the ThisWorkbook section of your main workbook. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 14:34, "steve" wrote:
OK sorry missed that bit, so the macro you have already starts when you open a file. That macro should be in the ThisWorkbook section of your main workbook. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards * Steve "Johnnyboy5" wrote in message ... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message .... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, *most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John Hi tested at home on my laptop / PC (different drives) and it worked with a bit or sorting of which drive should be searched for first. If you know of how to write a code to look for the workbooks in any location that would be just fantastic. I was sent the following code by Rick what I meant was "take the relevant part of the text and use it in your code". As an example... DriveLetter = Left(ThisWorkbook.Path, 1) Workbooks.Open (" & DriveLetter & _ ":\Direct Payments DOT\0.5 yearly payment planner.xls") -- Rick (MVP - Excel) but didnt have a clue how to use it. its all under a post called open several workbooks at the same time. johnnyboy |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 9 June, 14:34, "steve" wrote:
OK sorry missed that bit, so the macro you have already starts when you open a file. That macro should be in the ThisWorkbook section of your main workbook. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards * Steve "Johnnyboy5" wrote in message ... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message .... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, *most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John or instead how can i get it to work with three ++ drives example what do need to change Private Sub Workbook_Open() If Len(Dir("x:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("x:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("x:\Direct Payments DOT\0.5 yearly payment planner.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
Hi John,
Having looked through your posts I think it might be easier to try something new. Open windows "notepad" and copy and paste the lines below. Don't include the dotted lines. '---------- start \Direct Payments DOT\"1 payments cheryl.xls" start \Direct Payments DOT\"1 payments Sandra.xls" start \Direct Payments DOT\"1 payments Liv.xls" start \Direct Payments DOT\"1 payments Sally.xls" start \Direct Payments DOT\"0.5 yearly payment planner.xls" exit '---------- Then click on filesave as Give it the name "Start.bat" And save it to your pen drive. It may ask you if you are sure you want to change the file type but it is ok Now double click the file Start.bat on your pen drive, it doesn't matter if the drive letter is A or Z it will start all your files Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 14:34, "steve" wrote: OK sorry missed that bit, so the macro you have already starts when you open a file. That macro should be in the ThisWorkbook section of your main workbook. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John or instead how can i get it to work with three ++ drives example what do need to change Private Sub Workbook_Open() If Len(Dir("x:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("x:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("x:\Direct Payments DOT\0.5 yearly payment planner.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
Oops my mistake, quotation marks in the wrong place, Try
'---------- start \"Direct Payments DOT\1 payments cheryl.xls" start \"Direct Payments DOT\1 payments Sandra.xls" start \"Direct Payments DOT\1 payments Liv.xls" start \"Direct Payments DOT\1 payments Sally.xls" start \"Direct Payments DOT\0.5 yearly payment planner.xls" exit '---------- Regards Steve "steve" wrote in message ... Hi John, Having looked through your posts I think it might be easier to try something new. Open windows "notepad" and copy and paste the lines below. Don't include the dotted lines. '---------- start \Direct Payments DOT\"1 payments cheryl.xls" start \Direct Payments DOT\"1 payments Sandra.xls" start \Direct Payments DOT\"1 payments Liv.xls" start \Direct Payments DOT\"1 payments Sally.xls" start \Direct Payments DOT\"0.5 yearly payment planner.xls" exit '---------- Then click on filesave as Give it the name "Start.bat" And save it to your pen drive. It may ask you if you are sure you want to change the file type but it is ok Now double click the file Start.bat on your pen drive, it doesn't matter if the drive letter is A or Z it will start all your files Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 14:34, "steve" wrote: OK sorry missed that bit, so the macro you have already starts when you open a file. That macro should be in the ThisWorkbook section of your main workbook. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John or instead how can i get it to work with three ++ drives example what do need to change Private Sub Workbook_Open() If Len(Dir("x:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("x:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("x:\Direct Payments DOT\0.5 yearly payment planner.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
On 10 June, 10:33, "steve" wrote:
Oops my mistake, quotation marks in the wrong place, Try '---------- start \"Direct Payments DOT\1 payments cheryl.xls" start \"Direct Payments DOT\1 payments Sandra.xls" start \"Direct Payments DOT\1 payments Liv.xls" start \"Direct Payments DOT\1 payments Sally.xls" start \"Direct Payments DOT\0.5 yearly payment planner.xls" exit '---------- Regards * Steve"steve" wrote in message ... Hi John, *Having looked through your posts I think it might be easier to try something new. Open windows "notepad" and copy and paste the lines below. Don't include the dotted lines. '---------- start \Direct Payments DOT\"1 payments cheryl.xls" start \Direct Payments DOT\"1 payments Sandra.xls" start \Direct Payments DOT\"1 payments Liv.xls" start \Direct Payments DOT\"1 payments Sally.xls" start \Direct Payments DOT\"0.5 yearly payment planner.xls" exit '---------- Then click on filesave as Give it the name "Start.bat" And save it to your pen drive. It may ask you if you are sure you want to change the file type but it is ok Now double click the file Start.bat on your pen drive, it doesn't matter if the drive letter is A or Z it will start all your files Regards *Steve "Johnnyboy5" wrote in message ... On 9 June, 14:34, "steve" wrote: OK sorry missed that bit, so the macro you have already starts when you open a file. That macro should be in the ThisWorkbook section of your main workbook.. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message .... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message .... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John or instead how can i get it to work with three ++ drives example *what do need to change Private Sub Workbook_Open() If Len(Dir("x:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("x:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("x:\Direct Payments DOT\0.5 yearly payment planner.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks *John- Hide quoted text - - Show quoted text - THATS FANTASTIC - just tried it in work, will test it at home on the other PCs ect. your a star and very patient - thanks. John |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening other workbooks at the same time
Hi John,
I hope it works For you, It just seems easier that way, I use my pen drive all over and it is always a different letter. This way the drive letter is unimportant. Regards Steve "Johnnyboy5" wrote in message ... On 10 June, 10:33, "steve" wrote: Oops my mistake, quotation marks in the wrong place, Try '---------- start \"Direct Payments DOT\1 payments cheryl.xls" start \"Direct Payments DOT\1 payments Sandra.xls" start \"Direct Payments DOT\1 payments Liv.xls" start \"Direct Payments DOT\1 payments Sally.xls" start \"Direct Payments DOT\0.5 yearly payment planner.xls" exit '---------- Regards Steve"steve" wrote in message ... Hi John, Having looked through your posts I think it might be easier to try something new. Open windows "notepad" and copy and paste the lines below. Don't include the dotted lines. '---------- start \Direct Payments DOT\"1 payments cheryl.xls" start \Direct Payments DOT\"1 payments Sandra.xls" start \Direct Payments DOT\"1 payments Liv.xls" start \Direct Payments DOT\"1 payments Sally.xls" start \Direct Payments DOT\"0.5 yearly payment planner.xls" exit '---------- Then click on filesave as Give it the name "Start.bat" And save it to your pen drive. It may ask you if you are sure you want to change the file type but it is ok Now double click the file Start.bat on your pen drive, it doesn't matter if the drive letter is A or Z it will start all your files Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 14:34, "steve" wrote: OK sorry missed that bit, so the macro you have already starts when you open a file. That macro should be in the ThisWorkbook section of your main workbook. just replace that with Privat Sub Workbook_Open() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 13:41, "steve" wrote: Do you have the other code, so I could take a look. Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 12:41, "steve" wrote: You only need to check if one of the files is on the F drive and it will open them all. Otherwise it will open them all from the G drive. Sub test() If Len(Dir("f:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly paymentplanner.xls") Else Workbooks.Open ("g\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sandra.xls") ' Workbooks.Open ("g\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g\Direct Payments DOT\0.5 yearly paymentplanner.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... On 9 June, 10:43, "steve" wrote: I'll let you fill in the filenames, but this will check if a file exists in F drive. If it exists it will open it. If not will open it from G. There is no error checking so if you forget to plug the drive in or it has another drive letter it won't work. Sub test() If Len(Dir("f:\test.xlsx")) 0 Then Workbooks.Open ("f:\test.xls") Else Workbooks.Open ("g:\test.xls") End If End Sub Regards Steve "Johnnyboy5" wrote in message ... How do I add another drive to search for when loading the workbooks listed below. Problem is when I use these files on my pen drive at home / work and other PCs the pen might be recognised as a different drive letter on each one. I only need it look in say "f" and "g" and that will do the job. Many thanks John EXAMPLE Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls")- Hide quoted text - - Show quoted text - Hi Couldn't wait so tried to do it in work. not sure how i should be writing this code out, is it .... ? Sub test() If Len(Dir("g:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") If Len(Dir("g:\Direct Payments DOT\1 payments Sandra.xls")) 0 Then Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ECT ECT Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - Me again. sorry tried it it doesnt run when I open the main sheet 0.5 yearly payment planner. I think something about open_workbook () is missing - the other code i used will open the other workbooks when the "main" open is opened first. Hope this makes sense. John- Hide quoted text - - Show quoted text - sure thing - and thanks for your attention to my problem, most helpful to me Private Sub Workbook_Open() 'Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") ' Change as required 'Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") ' Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") ' Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") ' End Sub John or instead how can i get it to work with three ++ drives example what do need to change Private Sub Workbook_Open() If Len(Dir("x:\Direct Payments DOT\1 payments cheryl.xls")) 0 Then Workbooks.Open ("x:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("x:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("x:\Direct Payments DOT\0.5 yearly payment planner.xls") Else Workbooks.Open ("f:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("f:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("f:\Direct Payments DOT\0.5 yearly payment planner.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments cheryl.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sandra.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Liv.xls") Workbooks.Open ("g:\Direct Payments DOT\1 payments Sally.xls") Workbooks.Open ("g:\Direct Payments DOT\0.5 yearly payment planner.xls") End If End Sub thanks John- Hide quoted text - - Show quoted text - THATS FANTASTIC - just tried it in work, will test it at home on the other PCs ect. your a star and very patient - thanks. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening new workbooks | Excel Discussion (Misc queries) | |||
Opening Workbooks | Excel Programming | |||
Opening Workbooks | Excel Programming | |||
Opening workbooks | Excel Discussion (Misc queries) | |||
opening 2 excel workbooks at the same time | Excel Discussion (Misc queries) |