Saving a workbook... this should be SO easy

**V8burble** · 10-28-2009, 09:10 AM

Damn! Just realised I'm still in the Access forum, not the Excel forum. I wish I knew how to move it!!!

I do hope so, and that some kind person will help me out here.

I've got a VBA sub-routine that gathers up a number of worksheets in the current workbook, opens a new workbook and chucks those worksheets into it, and I'm happy up to this point... so I now have an open workbook (called book1 by Excel until told otherwise) I want to save this new workbook under a filename made up of the contents of two cells in the original workbook and then pop up a SaveAs browse window with that new filename appearing as the filename allowing me to browse to where I'd like to place this nice newly named workbook.

Now I have this:

sNewfilenameFirst = Worksheets("LinkOut").Range("B1")
sNewfilenameSecond = Worksheets("LinkOut").Range("B3")
.
.
.
.

sNewWorkbook = Application.DefaultFilePath & "\" & sNewfilenameFirst & "-" & sNewfilenameSecond & ".xls"
ThisWorkbook.SaveCopyAs Filename:=sNewWorkbook

But I've decidied I don't want to use the default pathname now, I'd like to leave that decision to the user. My problem is, I don't know how to bring up a browse window with the filename filled in LOL.

Help

Thanks

**JeanRage** · 10-28-2009, 09:18 AM

Hi,

With a simple message box : MsgBox ...

HTH

**V8burble** · 10-28-2009, 09:24 AM

But that wouldn't allow me to browse to the required pathname, only to enter it... and I might not actually know it, hence the need to browse to it

**JeanRage** · 10-28-2009, 09:40 AM

You would then need an Activex control such as a Folder Browser Control ...

**V8burble** · 10-28-2009, 09:42 AM

Oh dear... that sounds WAY over my head LOL.

**V8burble** · 10-28-2009, 10:22 AM

Ok, think I've got it!

MS Office help is only useful if you know exactly what you are looking for isn't it... anyway looks like the 'GetSaveAsFilename' method is what I need, it has an initial filename property which is just the ticket. Will have a try after lunch.

EDIT:
Yep that worked a treat.... case closed LOL.