Choosing default path/filename in Excel with VBA

[Fabian]

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

 

[Bob Phillips]

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]

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.xls",,,"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]

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]

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.