auto archiving functions

  • Thread starter Thread starter torpido
  • Start date Start date
T

torpido

hi all
just wanted to aks if there is any kinda of *_auto_*- fucntion that ca
archive a worksheet separatly wihout needing to save the acctual file.
like i have "book1" and i add an entry then print the sheet but i don
want to save it cuz its a template-like but i want to have a record o
it some where else with same date.
like "book1 05-10-2005", "book1 06-10-2005" and so on to be saved i
given path(folder).

i know i can do this by "save-as" but then i must always give the fil
name over and over and i must choose where to save everytime
 
Hi Torpido.

As far as I know there is not a function to do this. So you have to add
a macro to your document.

It could be something like this:
'------Start
Option Explicit

Sub SaveCopyOfSheet()
Dim strSaveAs As String
Dim strInitial As String
Dim strFilter As String

ActiveSheet.Copy
strInitial = "Book1_" & Format(Date, "yyyy_mm_dd") & ".xls"
strFilter = "Excel (*.xls), *.xls"
strSaveAs =
Application.GetSaveAsFilename(InitialFilename:=strInitial,
Filefilter:=strFilter)
If strSaveAs <> False Then
ActiveWorkbook.SaveAs Filename:= _
strSaveAs, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
End If
If MsgBox("Close this document", vbQuestion + vbYesNo) = vbYes Then
ThisWorkbook.Saved = True
ThisWorkbook.Close
End If
End Sub
'------------ END

HTH

Wouter Magré
 
thanks dude,
will this macro auto-archive or i should set a key like f12 so it will
save then.
and cuz am not tyhat much in scripting can u tell me which parts to be
replaced in this script.
thanks, uve been help full.
 
Hi Torpido,

There was a small typo in my code, sorry for that.

About how to use:
I suggest you copy it into the document from where you want to extract
single worksheets.
You either use [Alt]+[F8] to select this macro from the list and click
Run...
or you assign a hot-key to this macro.
Hit [Alt]+[F8], Select the name of this macro, click Options and Select
a key to use combined with [Ctrl] to start this macro.


Sub SaveCopyOfSheet()
Dim strSaveAs As String
Dim strInitial As String
Dim strFilter As String


ActiveSheet.Copy
strInitial = "Book1_" & Format(Date, "yyyy_mm_dd") & ".xls"
strFilter = "Excel (*.xls), *.xls"
strSaveAs = Application.GetSaveAsFilename( _
InitialFileName:=strInitial, _
Filefilter:=strFilter)
If strSaveAs <> "False" Then
ActiveWorkbook.SaveAs _
Filename:=strSaveAs, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End If
If MsgBox("Close this document, without saving", _
vbQuestion + vbYesNo) = vbYes Then
ThisWorkbook.Saved = True
ThisWorkbook.Close
End If
End Sub


I hoop that this will make you happy,

Wouter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top