writing a macro that automatically opens another file

[J.Mart]

Hi,
How do I start to write a macro in excel that automatically opens the file
"BEA U.S Flows & Stock 1980-2007" when the file "Duignan_US-ASIA_EU Trade &
FDI Flows" is opened?

 

[Dave Peterson]

You could add a macro to the first workbook so that when it's opened, the second
workbook is opened:

Option Explicit
Sub Auto_Open()
dim myPath as string
dim myFileName as string
dim wkbk as workbook

myPath = "C:\yourpathtothefile\" '<-- include the trailing backslash!
myfilename = "BEA U.S Flows & Stock 1980-2007.xls"

set wkbk = nothing
on error resume next
set wkbk = workbooks(myfilename)
on error goto 0

if wkbk is nothing then
'open it!
set wkbk = workbooks.open(filename:=mypath & myfilename)
else
'do nothing, it's already open
end if

End sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[FSt1]

hi
use the workbook open event on the last file.
Private Sub Workbook_Open()
Workbooks.Open Filename:= _
"C:\Your\file\path\BEA U.S Flows & Stock 1980-2007.xls"
'if you want the first wb to be active then add this line
'windows("Duignan_US-ASIA_EU Trade & FDI Flows").activate
End Sub

regards
FSt1

 

[J.Mart]

Great. Thanks for the additional resources.

You could add a macro to the first workbook so that when it's opened, the second
workbook is opened:

Option Explicit
Sub Auto_Open()
dim myPath as string
dim myFileName as string
dim wkbk as workbook

myPath = "C:\yourpathtothefile\" '<-- include the trailing backslash!
myfilename = "BEA U.S Flows & Stock 1980-2007.xls"

set wkbk = nothing
on error resume next
set wkbk = workbooks(myfilename)
on error goto 0

if wkbk is nothing then
'open it!
set wkbk = workbooks.open(filename:=mypath & myfilename)
else
'do nothing, it's already open
end if

End sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)