Macro to open another workbook

[howard]

I have a workbook called "Sales by Dept "and when I open this , I would like another workbook called "Intercompany Sales.xls" in C:\my documents to open automatically



See my code below which I have saved into "This Workbok" module but the file "Intercompany Sales.xls" does not automatically open

Private Sub Workbook_Open()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\My Documents\" 'Change folder location
MyFile = Dir(MyFolder & "\*intercompany*.xls")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
MyFile = Dir
Loop
End Sub

It would be appreciated if someone could amen my code accordingly

 

[Michael M]

UNTESTED

Private Sub Workbook_Open()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\My Documents" 'Change folder location
MyFile = "Intercompany Sales.xls"
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
MyFile = Dir
Loop
End Sub

 

[howard]

Thnks for the reply

When opening the file , I get invalid procedure or call rguments and the code below is highlighted

 MyFile = Dir

Kindly advise how to correct this

 

[Michael M]

Comment out that line

 

[howard]

I did coment it out earlier, but then file goes into a loop and I have to shut it down

 

[Michael M]

.xls is not part of the actual file name

Private Sub Workbook_Open()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\My Documents" 'Change folder location
MyFile = "Intercompany Sales"
Workbooks.Open Filename:=MyFolder & "\" & MyFile
End Sub

 

[howard]

Thanks very much for your help

 

[Michael M]