Open specific workbook

[Li Jianyong]

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.

 

[Bernie Deitrick]

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]

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]

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