Open specific workbook
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. |
Open specific workbook
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. |
Open specific workbook
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. |
Open specific workbook
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. |
All times are GMT +1. The time now is 01:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com