Open a file(s)

[Airborne]

Hello can anyone please help me with the following problem. I have files called dd-mm-yyyy_A, dd-mm-yyyy_B, dd-mm-yyyy_C, dd-mm-yyyy_D, dd-mm-yyyy_D. The files are saved in that name at random in a map called e.g. Oct2004. I got a great calender routine to choose the date. The date is placed in the workbook that has to open the chosen files.

This routine I use to open files


Sub OpenFiles()
If Sheets("data").[A90].Value = "" Then Exit Sub
Dim fName As String
Dim mybook As Workbook
fName = Sheets("data").[A90].Value & ".xls" 'here it reads the chosen date on a sheet in the workbook.
On Error Resume Next
Set mybook = Workbooks(fName)
On Error GoTo 0
If mybook Is Nothing Then
With Application.FileSearch
.NewSearch
.LookIn = "\\MAP\SubMAP\ (file:///MAPSubMAP)" & sDirectory
.SearchSubFolders = True
.Filename = fName
If .Execute > 0 Then
Workbooks.Open (.FoundFiles(1))
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios _
:=False, Password:=""
Else
MsgBox fName & " not found!"
End If
End With
Else
MsgBox fName & " Is already open!"
End If
End Sub


My problem is to put in the line fName = Sheets("data").[A90].Value & ".xls" that it will also look for the A,B,C,D,E so it will open the right files.
It now opens e.g. 9-10-2004 but I want it to open 9-10-2004_A.
I have tried Id = ("_A_,B,_C,_D,_E") and then in the line fName = Sheets("data").[A90].Value & Id & ".xls". But it will look for 9-10-2004_A,_B etc.

I would realy appreciate the help, I have a great calender routine but can't use it for my routine. :bawl

[Jacob Hilderbrand]

You have to open them seperately. Try something like this:


Option Explicit

Sub OpenFiles(fName As String)
Dim mybook As Workbook
On Error Resume Next
Set mybook = Workbooks(fName)
On Error GoTo 0
If mybook Is Nothing Then
With Application.FileSearch
.NewSearch
.LookIn = "\\MAP\SubMAP\ (file:///MAPSubMAP)" & sDirectory
.SearchSubFolders = True
.Filename = fName
If .Execute > 0 Then
Workbooks.Open (.FoundFiles(1))
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios _
:=False, Password:=""
Else
MsgBox fName & " not found!"
End If
End With
Else
MsgBox fName & " Is already open!"
End If
End Sub

Sub GetAllFiles()
If Sheets("data").[A90].Value = "" Then Exit Sub
Call OpenFiles(Sheets("data").[A90].Value & "_A.xls")
Call OpenFiles(Sheets("data").[A90].Value & "_B.xls")
Call OpenFiles(Sheets("data").[A90].Value & "_C.xls")
Call OpenFiles(Sheets("data").[A90].Value & "_D.xls")
Call OpenFiles(Sheets("data").[A90].Value & "_E.xls")
End Sub

[Airborne]

Hello jacob,

I thought this was it. But I have 2 problems.

First the name of the files is Datadd-mm-yyyy_A. The date is put in cell A90 on Store sheet. So

Call OpenFiles(Sheets("Store").[A90].Value & "_A.xls")
But I need to put the name Data in front of it to get the right name. I've tried

Call OpenFiles(Sheets "Data" & ("Store").[A90].Value & "_A.xls")
But I get an error.

Second it's strange but your routine actualy starts looking. I started a search on 6-10-2004 by the input of the calender form that puts the right date in cel A90. It opens the file with date 6-10-2004_A (great!) but it also starts opening 10-8-2004_B and 10-6-2004_C the system gets overloaded and Excel hangs?

:dunno :bawl I hope you have more suggestions, thanks anyway.

[Airborne]

Hello Jacob, I've solved the second problem. I had to activate the workbook with the cell A90 every time it opens the file again.


Call OpenFiles(Sheets("data").[A90].Value & "_A.xls")
Windows("Workbook with macro").Activate
Call OpenFiles(Sheets("data").[A90].Value & "_B.xls")
Windows("Workbook with macro").Activate 'etc

[Airborne]

Jacob, all works fine but do you think if it's possible to speed up the opening of the files by adding/changing something in the routine?:eek:

[Jacob Hilderbrand]

Hello jacob,

I thought this was it. But I have 2 problems.

First the name of the files is Datadd-mm-yyyy_A. The date is put in cell A90 on Store sheet. So

Call OpenFiles(Sheets("Store").[A90].Value & "_A.xls")
But I need to put the name Data in front of it to get the right name. I've tried

Call OpenFiles(Sheets "Data" & ("Store").[A90].Value & "_A.xls")

But I get an error.

Second it's strange but your routine actualy starts looking. I started a search on 6-10-2004 by the input of the calender form that puts the right date in cel A90. It opens the file with date 6-10-2004_A (great!) but it also starts opening 10-8-2004_B and 10-6-2004_C the system gets overloaded and Excel hangs?

:dunno :bawl I hope you have more suggestions, thanks anyway.


To put Data in front try this:

Call OpenFiles( "Data" & Sheets("Store").[A90].Value & "_A.xls")

[Jacob Hilderbrand]

Hello Jacob, I've solved the second problem. I had to activate the workbook with the cell A90 every time it opens the file again.

Call OpenFiles(Sheets("data").[A90].Value & "_A.xls")
Windows("Workbook with macro").Activate
Call OpenFiles(Sheets("data").[A90].Value & "_B.xls")
Windows("Workbook with macro").Activate 'etc


To Activate the workbook again put

ThisWorkbook.Activate
Or you can get the same results without activating anything,

Call OpenFiles(ThisWorkbook.Sheets("data").[A90].Value & "_A.xls")

[Jacob Hilderbrand]

Jacob, all works fine but do you think if it's possible to speed up the opening of the files by adding/changing something in the routine?:eek:

Try this: Add the following code to the start and end of the macro:



Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Code
'Code
'Code
'Code

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

[Airborne]

Thanks, I've tried your speed up suggestion but it doesn't seem to speed up much more. I think it's slow because it has to get the files from the network. Thanks anyway. You have already helped me a lot. :super:

[Jacob Hilderbrand]

You're Welcome :)

Take Care