PDA

View Full Version : Solved: File Save As



antonc
10-24-2008, 03:46 AM
:banghead:

Hi Guys,

I have done a search, but can't seem to find anything that helps me.

I've attached a file for ease of explanation.

I'm trying to compile a forecast budget master file for my branches.

I'm have a bit of trouble trying to get the file to save as another filename based on the branch that a user selects.

The idea is that when any user opens the master file (I have made this a readonly file), a userform will force them to select a branch that they will work on (along with other options). When the OK button is pressed, I want to automatically save the file as another filename, based on the branch that they select. I want to save this file based on a naming convention that we have at this company.

The code for the ok button is :

ActiveWorkbook.SaveAs FileName:=Range("S2").Value

This doesn't seem to have any effect on the filename.
Is it because S2 refers to a formula?

Please help.

Thanks in advance for you time and effort and any help will be greatly appreciated.

Bob Phillips
10-24-2008, 04:01 AM
The problem is that the SaveAsUI argument is only true if the Save as is done from a dialog, the UI part of the name, not if you issue a SaveAs command.

You will need some more logic to handle that situation through you BeforeSave code.

antonc
10-24-2008, 04:26 AM
Sorry, Now I'm confused - I'm not a VBA regular by any means and I have no idea what to do now... What type of logic are you refering to?

Bob Phillips
10-24-2008, 04:35 AM
Well who wrote the rest of the code that you have then?

antonc
10-24-2008, 04:39 AM
That was me, though it is hardly from a level of understanding though - just bits that i have picked up from the net. Copying and pasting and trying to adapt to my needs - that's all.

Bob Phillips
10-24-2008, 05:01 AM
See if this works.

Add this code into one of the standard code modules



Public SaveName As String


and change the form code OK procedure to




Private Sub cmdOK_Click()

frmSelect.Hide
SaveName = Range("S2").Value
ActiveWorkbook.Save

End Sub


and then change the CustomeSave procedure to



Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
ElseIf SaveName <> "" Then
ThisWorkbook.SaveAs SaveName
SaveName = ""
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

antonc
10-24-2008, 05:04 AM
Thanks LD - appreciated - will try now...

georgiboy
10-24-2008, 05:09 AM
Private Sub cmdOK_Click()

frmSelect.Hide
ActiveWorkbook.SaveCopyAs Range("S2").Value & ".xls"

End Sub

antonc
10-24-2008, 05:11 AM
XLD - sorry for being a pain, Where should I insert the customsave procedure? Into the form? or one of modules?

antonc
10-24-2008, 05:24 AM
Georgiboy,

How do i now activate this workbook directly?

georgiboy
10-24-2008, 05:26 AM
How do you mean?

georgiboy
10-24-2008, 05:57 AM
Do you mean like this. Once you have saved the copy you can open it, then activate it, then write to it from the master sheet macros:

Workbooks.Open (Range("S2") & ".xls")
Windows(Range("S1") & ".xls").Activate
ActiveWorkbook.Sheets("Instructions").Range("A1").Value = "Hello"

Hope this helps