Multiple files

[tintin1012000]

HI all,

Using VBA

I want to create an excel sheet where a supervisor can monitor 4 excel files,
The 4 files to monitor are in 4 different folders
C:\Documents and Settings\dave01\My Documents\Line 1
C:\Documents and Settings\dave01\My Documents\Line 2
C:\Documents and Settings\dave01\My Documents\Line 3
C:\Documents and Settings\dave01\My Documents\Line 4

In the line folders there are workbooks called wk18, wk19 wk 20 etc depending on the week !!

Ideally the excel document would ask the user for the week number and in turn it would open a specific location within the workbook,

Can anyone help

 

[Domski]

Something like this:

Sub test()

Dim strWeekNum As String

strWeekNum = InputBox("Enter week number")

If strWeekNum = "" Then Exit Sub

Workbooks.Open "C:\Documents and Settings\dave01\My Documents\Line 1\wk" & strWeekNum & ".xls"
Workbooks.Open "C:\Documents and Settings\dave01\My Documents\Line 2\wk" & strWeekNum & ".xls"
Workbooks.Open "C:\Documents and Settings\dave01\My Documents\Line 3\wk" & strWeekNum & ".xls"
Workbooks.Open "C:\Documents and Settings\dave01\My Documents\Line 4\wk" & strWeekNum & ".xls"

End Sub

Dom

 

[tintin1012000]

Thanks for the quick reply Dom,

It works almost perfect, The file will open using your code but I want to go to a specific location within the sheet depending on the shift eg
Monday Days i want to go to sheet called Graphs A1
Monday Nights ----- Graphs A139
Tuesday Days ------ Graphs A277
Tuesday NIght ------Graphs A415

If there was a drop down menu with all the shifts it would be ideal

Rich

 

[Domski]

To have a dropdown you would need to create a userform to display as a normal inputbox doesn't have that functionality.

Dom