I have a formula that will export a report and save it to a html file in a folder I ahve selected. Waht I need to do is have it auto save into a new folder named by teh current date everyday upon opening of the access database.
Here is the formula I use right now to auto save it to the folder,
Code:
Sub Autosave()
DoCmd.OutputTo acOutputReport, "InventoryValue", acFormatHTML, "C:\Documents and Settings\Jill2\Desktop\Inventory Report Backup\date1.html", True
End Sub
I need the name to auto save as the current date and everytime I open teh database on this machine.
Certain characters in file names are not shined upon very brightly and therefore consideration must be given to this fact. The forward slash (/) in a Short Date for example is not acceptable in a file name as are a few other characters (\ / ? : " * < > |). With this in mind you need to decide the type of date format you would like within the File Name. The standard Short Date (01/01/2008) is out of the question but, you can still use a short date if you change the Forward Slash (/) to the minus symbols (01-01-2008). This is still easily legible as a date. To convert a date to this format you need to use...the Format function, like this:
MyDateString = Format(MyDate, "mm-dd-yyyy")
Other alternatives would be to use the standard Medium Date format (01-Jan-08) or the standard Long Date format (January 01, 2008). These two formats are acceptable within File Names. I personally like the Medium Date format (01-Jan-08) since it's short and is easy to read as a date.
To have your Sub-Procedure place the Current Date as the File Name, you would need to create the file name string before applying it to the MS-Access OutputTo function, like this:
Code:
Public Sub Autosave()
Dim FilePathStrg As String
Dim FileNameStrg As String
FilePathStrg = "C:\Documents and Settings\Jill2\Desktop\Inventory Report Backup\"
FileNameStrg = Format(Now, "Medium Date") & ".html"
DoCmd.OutputTo acOutputReport, "InventoryValue", acFormatHTML,FilePathStrg & FileNameStrg , True
End Sub
Notice how the FileNameStrg string variable is filled. The VBA Format() function is used in conjunction with the VBA Now() function which returns the Current System Date and Time. The Format function is set to format the Current System Date/Time to Medium Date format.
To Initiate the AutoSave Sub-Procedure automatically when the database is opened, all you need to do is place a call the the AutoSave procedure when the Database Startup Form (whichever it is) is opened. You can do this from within the Forms' code module under the OnOpen event, like this:
Code:
Private Sub Form_Open(Cancel As Integer)
Call AutoSave
End Sub
When Saving files to disk on a regular basis, there is something else you also need to consider and handle should it arise....What if the file already exists?
Are you going to Overwrite it? Change the File Name or its' Path to something else? Or will you just want to cancel the AutoSave procedure altogether? Perhaps you would like to allow the User to view the file before these sorts of decisions are considered. Whatever the case may be, you need to determine whether or not the file you are about to save already exists within the Disk location. To do this you can apply this simple function to your Database (copy and paste into a Database Code Module). The DoesFileExist function:
Code:
Public Function DoesFileExist (PathStrg As String) As Boolean
On Error Resume Next
If Len(Dir(PathStrg, 14)) > 0 Then DoesFileExist = True
If Err <> 0 Then Err.Clear
End Function
Here is a sample of how to use the DoesFileExist function:
To apply this now to your AutoSave procedure it would all look something like this:
Code:
Public Sub Autosave()
Dim FilePathStrg As String
Dim FileNameStrg As String
FilePathStrg = "C:\Documents and Settings\Jill2\Desktop\Inventory Report Backup\"
FileNameStrg = Format(Now, "Medium Date") & ".html"
If DoesFileExist(FilePathStrg & FileNameStrg) = True Then
If MsgBox("The file name '" & FileNameStrg & "' already exists." & vbCr & _
"Do you want To Overwrite it?", vbExclamation + vbYesNo, _
"File Already Exists...") = vbYes Then
Kill FilePathStrg & FileNameStrg
Else
Exit Sub
End If
End If
DoCmd.OutputTo acOutputReport, "InventoryValue", acFormatHTML, FilePathStrg & FileNameStrg, True
End Sub
You may also want to consider setting the Read-Only attribute for the file you are about to save so that it is somewhat protected (can't be overwritten unless the attribute is removed). There are a lot of things you can do at this point.
Thanks for the icredible reply I am trying to insert the "call autosave" event and am having a hard time finding where to put it. is there a specific place I should be looking?
When you start you Access Database, what is the very first Form that is displayed on screen?
Place that Form into Design View, open the Properties Window, select the Events tab on the top of the Properties Window the scroll down until you see On Open. Double-click in its' property window, Event Procedure should be automatically placed there.
Now move the mouse pointer to the far right of the On Open property box and a button will appear with three dots in it....left-click on it. The VBA Editor will open and the editing caret will be within the Form_Open sub-procedure. place within this procedure:
