Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing default path/filename in Excel with VBA
Hi all there,
I am pretty new at VBA. I do not know how to do something that should be easy, IMHO. I use Office 2003 with WinXP SP2. I open an excel template in VBA. but I also want Excel to show the proposed filename in the titlebar not as "Book1" but as "C:\MyPath\Myfile.xls". In other words, I want to be able to choose the default path and the filename for the opened and active excel file (what you see in the titlebar). Can anyone help me with this? Thanks. Fabian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing default path/filename in Excel with VBA
Public WithEvents App As Application
Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) Wn.Caption = Wb.FullName End Sub Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) App_WindowActivate Wb, Windows(Wb.Name) End Sub Private Sub Workbook_Open() Set App = Application End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Fabian" wrote in message oups.com... Hi all there, I am pretty new at VBA. I do not know how to do something that should be easy, IMHO. I use Office 2003 with WinXP SP2. I open an excel template in VBA. but I also want Excel to show the proposed filename in the titlebar not as "Book1" but as "C:\MyPath\Myfile.xls". In other words, I want to be able to choose the default path and the filename for the opened and active excel file (what you see in the titlebar). Can anyone help me with this? Thanks. Fabian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing default path/filename in Excel with VBA
Hi Bob,
Thanks for your reply. However, I am afraid I have not made myself understood. I normally programmatically open a excel template in this way: Set MyBook = xlApp.Workbooks.Open("MyTemplate.xlt") In this case, The proposed filename for the opened workbook (you can see it in the tilebar) is "MyTemplate1.xls" and the proposed destination directory is the directory where Mytemplate.xlt is located. but I want to assign a different proposed-filename-and-path by Excel. Workbooks has read-only properties Name, FullName and Path, and they are read-only: I cannot change them, unfortunately. So, I thought there could be a way to Open the excel template in another way. Something that looks like the following: Set MyBook = xlApp.Workbooks.Open("C:\PathIwant\FilenameIWant.x ls",,,"MyTemplate.xlt") or Set MyBook = xlApp.workbooks MyBook.Template = "MyTemplate.xlt" MyBook.FileName = "C:\NameIwant.xls" MyBook.Open() but I have not found anything like this in the collections. Whoever can help... Thanks Fabian Bob Phillips wrote: Public WithEvents App As Application |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing default path/filename in Excel with VBA
You don't name the file when you open it but when you save it. So just open
the template file, and when you save it, specify the new file name. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Fabian" wrote in message ups.com... Hi Bob, Thanks for your reply. However, I am afraid I have not made myself understood. I normally programmatically open a excel template in this way: Set MyBook = xlApp.Workbooks.Open("MyTemplate.xlt") In this case, The proposed filename for the opened workbook (you can see it in the tilebar) is "MyTemplate1.xls" and the proposed destination directory is the directory where Mytemplate.xlt is located. but I want to assign a different proposed-filename-and-path by Excel. Workbooks has read-only properties Name, FullName and Path, and they are read-only: I cannot change them, unfortunately. So, I thought there could be a way to Open the excel template in another way. Something that looks like the following: Set MyBook = xlApp.Workbooks.Open("C:\PathIwant\FilenameIWant.x ls",,,"MyTemplate.xlt") or Set MyBook = xlApp.workbooks MyBook.Template = "MyTemplate.xlt" MyBook.FileName = "C:\NameIwant.xls" MyBook.Open() but I have not found anything like this in the collections. Whoever can help... Thanks Fabian Bob Phillips wrote: Public WithEvents App As Application |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing default path/filename in Excel with VBA
Bob Phillips wrote: You don't name the file when you open it but when you save it. So just open the template file, and when you save it, specify the new file name. Of course I name it when I save it, but I would like to avoid having to change directory and filename everytime the user saves the generated excel file. To make a long story short, the macro is lauched from MsAccess and generates a preformatted Excel template with a list of costs and totals in it. This process can be repeated many times. Either if requirements change or if the user makes a mistake and needs to regenerate it. Also, the user can work with several customers at - almost - the same time and it is easy to wrong the name of the file (something like Ord1234567.xls) with the wrong order number, if you know what I mean. If I can make things easier for everyone, wouldn't it be better? Thanks Fab. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
how to insert the path and filename into cell in excel 2002 | Excel Discussion (Misc queries) | |||
Path and filename displayed in Excel | Excel Discussion (Misc queries) | |||
Are there template footers for "Filename and Path" in Excel. How? | Excel Discussion (Misc queries) | |||
Excel 2K: Putting Filename & path in footer | Excel Discussion (Misc queries) |