Solved: Active Workbook

[Carpiem]

Good Morning To You All,

I get confused with workbooks and changing directories using vba so tend to use "save as" into some folder and close the active workbook. Then reopen the original workbook and start all over again.

What I would like is to do the following using vba:

a) be prompted to "name" the workbook I have finished with.
b) have this newly named workbook saved to a predefined desktop folder.
c) keep on working with the original workbook that I started with.

Can this be done?

Thank you in advance for your response and help.

Carpiem

[Norie]

Carpiem

What exactly are you doing in code?

You could avoid confusion by creating references to the active workbook and any workbooks you open.

[Carpiem]

Hi Norie,

First off let me say that the code that follows is not mine.... I don't know enough to have put it together in the first place.

To begin with the working file or my template has 8 sheets. I run macros to populate a one of the sheets with data from 3 other sheets in the workbook.

So now I do not require the 3 sheets I lifted data from and the workbook as I save it only requires keeping 5 of the 8 original sheets.

The code I have prompts me for a name and saves the file to the folder on my desktop... no issue with this up to here.

However, I was hoping there was someway of keeping the original file open so that I could continue on with running another report.

Right now I need to go to the desktop and open the original file and start again. Of course it may well be that I can't do this?

[VBA]
Option Explicit
Const Fld = "C:\Documents and Settings\mcarlisle\Desktop\Okay\"
Sub SaveNameFile()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Dim fname As Variant
Dim Wb As Workbook
Dim strDate As String
Set Wb = ActiveWorkbook
ChDir Fld
On Error Resume Next

MsgBox prompt:="""Please Enter A Name For This File"""
'Date & Time Stamp File (date/Time string)
strDate = Format(Now, "yy-mm-dd h-mm-ss")
fname = Application.GetSaveAsFilename("", _
FileFilter:="Excel Files (*.xls), *.xls") & Chr(32) & Chr(40) & strDate & Chr(41)
On Error GoTo 0
If fname <> False Then
Wb.SaveAs fname
'MsgBox "No Files were selected"
'Wb.Close False
Wb.Close True
On Error Resume Next
Set Wb = Nothing
Else
Set Wb = Nothing
On Error GoTo 0
End If
'ThisWorkbook.Save
'Application.Quit
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
'Application.Quit
End Sub

[/VBA]

I had to fiddle with the above code to work for me... of course I can't remember now why or what was happening.

Thank you for the reply Norrie.

Carpiem

[mdmackillop]

How about just saving a copy?

From the Help file.
SaveCopyAs Method Example
This example saves a copy of the active workbook.
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"

[Carpiem]

Hello Malcolm,

Hope life is well in the North.

Made the change as suggested but of course I have got something wrong. Tested it on my desktop before sending it to one of the salesmen.

a) The file is saved with the xls extension but it looks (the icon) like some no-name file and when I check the properties it just classes it as a file... not an excel file.

b) The original file is not available for me to work with, because it is now saved it as the something else above.

Well now that I have completely turned the world upside down I'll move back into my padded room.

Malcolm leave me with this. I need to try a few more things before I throw in the towel.

Regards,

Carpiem (Brian W)

[mdmackillop]

Hi Brian,
I've corrected the code which looked corrupted. It should save a copy, leaving you in the original workbook. Works for me just now.
Regards
Malcolm

[Carpiem]

Hello Malcolm,

No tickey for me.... changed the code as shown & still the same weird icon. Also do not have access to the original file.

Just a thought, does the code in the Sub SaveNameFile() look okay to you? Naturally it's fine by me, but it might as well be Sanskrit too.

Regards,

Brian

[mdmackillop]

Hi Brian,
Your code stops on the wb.close command, and the file created was called
testing.xls (06-06-14 21-57-30), not seen as an Excel file.
You need to add error handling to reset your Application items in case the code fails.
I've adjusted your code to use the SaveCopyAs routine, with a check that the file exists.
Regards
Malcolm

[vba]
Option Explicit
Const Fld = "C:\Documents and Settings\mcarlisle\Desktop\Okay\"
Sub SaveNameFile()
Dim fname As String, Test As Boolean
Dim Wb As Workbook
Dim strDate As String
Dim Txt As String

ToggleStuff False

On Error GoTo Exits
Set Wb = ActiveWorkbook
Txt = InputBox("Please Enter A Name For This File", "Save a copy")
'Exit file if left blank
If Txt = "" Then GoTo Exits
'Date & Time Stamp File (date/Time string)
strDate = Format(Now, " yy-mm-dd h-mm-ss")
fname = Fld & Txt & strDate & ".xls"
Wb.SaveCopyAs fname
If Dir(fname) <> "" Then
MsgBox fname & " saved."
Test = True
End If
Exits:
If Test <> True Then MsgBox "File not saved"
Set Wb = Nothing
ToggleStuff True
End Sub

Sub ToggleStuff(x As Boolean)
Application.ScreenUpdating = x
Application.DisplayAlerts = x
If x = False Then Application.Calculation = xlCalculationManual
If x = True Then Application.Calculation = xlCalculationAutomatic
End Sub

[/vba]

[Carpiem]

Evening Malcolm,

Just got in and checked for some response.... you always deliver so I knew something would be greeting me!

I will try the code at work tommorow....course it will work, tis but the right thing to do.

Regards,

Brian

[Carpiem]

Good Day Malcolm,

Reading my reply to you. Must do better this time around.

Actually dug up some code you gave me yonks ago and tried to add in the Error handling from above. Do you think you could oblige me... I keep getting tripped up by what is the active workbook, what is the copy.

Your code works a treat... but how does it do this?


[VBA]
Const Fld = "C:\Documents and Settings\mcarlisle\Desktop\Completed\"
Sub SaveYourCopy()
Dim NewFileName As String
Dim StartWkBk As Workbook
Dim strDate As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set StartWkBk = ActiveWorkbook
NewFileName = InputBox("Enter a new file name to save: ")
'Date & Time Stamp File (date/Time string)
strDate = Format(Now, " yy-mm-dd h-mm-ss")

With StartWkBk
.Sheets(Array("Sh1", "Sh2", "Sh3", "Sh4")).Copy
ActiveSheet.Select
'ActiveWorkbook.SaveAs Filename:=.Path & "\" & NewFileName & strDate & ".xls"
ActiveWorkbook.SaveAs Filename:=Fld & NewFileName & strDate & ".xls"
End With
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub
[/VBA]


Regards,

Brian

[mdmackillop]

Hi Brian,
If you use ActiveWorkbook.SaveAs, you create a new workbook with a new name which becomes the active workbook. Your original workbook is closed with whatever data was present when it was opened (or at the last save). Try this manually with File/SaveAs and you'll see what I mean.
SaveCopyAs, saves a copy of the active workbook, either as a new file, or overwriting a previous version. This saved copy is "closed" and your active workbook remains open in an unsaved state. If you were to close it, you would lose all the changes, but they would be available in the saved copy.
Does that make sense?

[Carpiem]

Evening Malcolm,

Well that was painless and quite logical. Why was I having trouble understanding this?

Oh well my "Hat" off to you Malcolm. Have a good night.

Regards,

Brian

[mdmackillop]

Here's your code with the Toggle error handling. I personally rarely change the Calculation type, unless its a big sheet with lots going on. You may need to incorporate other error routines, but you should ensure that unexpected errors lead to the resetting of the False values.
[VBA]
Const Fld = "C:\Documents and Settings\mcarlisle\Desktop\Completed\"
Sub SaveYourCopy()
Dim NewFileName As String
Dim StartWkBk As Workbook
Dim strDate As String

Call ToggleStuff(False)
On Error GoTo Exits
Set StartWkBk = ActiveWorkbook
NewFileName = InputBox("Enter a new file name to save: ")
'Date & Time Stamp File (date/Time string)
strDate = Format(Now, " yy-mm-dd h-mm-ss")

With StartWkBk
.Sheets(Array("Sh1", "Sh2", "Sh3", "Sh4")).Copy
ActiveSheet.Select
'ActiveWorkbook.SaveAs Filename:=.Path & "\" & NewFileName & strDate & ".xls"
ActiveWorkbook.SaveAs Filename:=Fld & NewFileName & strDate & ".xls"
End With
ActiveWorkbook.Close SaveChanges:=False
Exits:
ToggleStuff True
End Sub

Public Sub ToggleStuff(x As Boolean)
Application.ScreenUpdating = x
Application.DisplayAlerts = x
Application.EnableEvents = x
End Sub

[/VBA]

[Carpiem]

Hello Malcolm,

Very elegant.... very neat.

When you say "I rarely change the Calculation type, unless its a big sheet with lots going on...".

Does that mean I would generally be better served using "ToggleStuff" than placing Application.Calculation = xlCalculationManual at the start of and Application.Calculation = xlCalculationAutomatic at the end of my subs?

Thank you,

Brian

[mdmackillop]

I've a large workbook, 16MB into which a macro imports 30 rows x 200 columns. This sheet has linking formulae to other sheets, and in this case I find turning off Calculation makes a significant speed difference. Unless there is a gain, I don't use it. EnableEvents and ScreenUpdating are used most often, and others only as required.