Dear Sirs,

I want to program an addin to my excel to open a workbook named
"Prod_01.xlsx". and I will share this addin with my colleagues,they have same
file in their computer,but kept in different path. so I thin my macro should
always find the workbooks("Prod_01.xlsx") firstly, then open it.Anybody can
provide these code?

thanks advancely.

Li,

I would use code in the workbook open event that asks for the path to that file the first time that
the add-in is opened, along the lines of

Public MyFName As String

Private Sub Workbook_Open()

If Sheets(1).Range("A1").Value = "" Then
MsgBox "Please browse to and select your Prod_01.xlsx file in the next dialog box"
Sheets(1).Range("A1").Value = Application.GetOpenFilename
End If
MyFName = Sheets(1).Range("A1").Value
End Sub

Then anytime your add-in needs that file, use

Workbooks.Open MyFName


HTH,
Bernie
MS Excel MVP


"Li Jianyong" wrote in message
...
Dear Sirs,

I want to program an addin to my excel to open a workbook named
"Prod_01.xlsx". and I will share this addin with my colleagues,they have same
file in their computer,but kept in different path. so I thin my macro should
always find the workbooks("Prod_01.xlsx") firstly, then open it.Anybody can
provide these code?

thanks advancely.

Dear Bernie,

thanks for your code. But if the user changed the path of the workbook
"prod_01.xlsx", what will happen?

Best regards
Li Jianyong

"Bernie Deitrick" wrote:

Li,

I would use code in the workbook open event that asks for the path to that file the first time that
the add-in is opened, along the lines of

Public MyFName As String

Private Sub Workbook_Open()

If Sheets(1).Range("A1").Value = "" Then
MsgBox "Please browse to and select your Prod_01.xlsx file in the next dialog box"
Sheets(1).Range("A1").Value = Application.GetOpenFilename
End If
MyFName = Sheets(1).Range("A1").Value
End Sub

Then anytime your add-in needs that file, use

Workbooks.Open MyFName


HTH,
Bernie
MS Excel MVP


"Li Jianyong" wrote in message
...
Dear Sirs,

I want to program an addin to my excel to open a workbook named
"Prod_01.xlsx". and I will share this addin with my colleagues,they have same
file in their computer,but kept in different path. so I thin my macro should
always find the workbooks("Prod_01.xlsx") firstly, then open it.Anybody can
provide these code?

thanks advancely.

Li,

Then the file won't open, but you could capture the error from the Workbooks.Open line to request
that the user select the file in its new folder, and over-write the path and filename data in cell
A1.

HTH,
Bernie
MS Excel MVP


"Li Jianyong" wrote in message
...
Dear Bernie,

thanks for your code. But if the user changed the path of the workbook
"prod_01.xlsx", what will happen?

Best regards
Li Jianyong

"Bernie Deitrick" wrote:

Li,

I would use code in the workbook open event that asks for the path to that file the first time
that
the add-in is opened, along the lines of

Public MyFName As String

Private Sub Workbook_Open()

If Sheets(1).Range("A1").Value = "" Then
MsgBox "Please browse to and select your Prod_01.xlsx file in the next dialog box"
Sheets(1).Range("A1").Value = Application.GetOpenFilename
End If
MyFName = Sheets(1).Range("A1").Value
End Sub

Then anytime your add-in needs that file, use

Workbooks.Open MyFName


HTH,
Bernie
MS Excel MVP


"Li Jianyong" wrote in message
...
Dear Sirs,

I want to program an addin to my excel to open a workbook named
"Prod_01.xlsx". and I will share this addin with my colleagues,they have same
file in their computer,but kept in different path. so I thin my macro should
always find the workbooks("Prod_01.xlsx") firstly, then open it.Anybody can
provide these code?

thanks advancely.