Create a form button to export a table

Hi all,

I need to regularly backup my database as an Excel file and have been
using the File > Export option.

Problem is I need anyone using the database to be able to do this
easily - nopt just me. For all users the database is hidden and only
a menu screen is visable.

Can I create a button on my menu screen that will export a table >
basically bring up the "save as" window.

My main menu for is called "Archive Records Database" and the table I want
to regularly export is "Customer List"

I'm not terribly good with code or my understanding of it so if anyone
can point me in the right direction it will be greatly appreciated!!

well I think I solved the problem mostly - and created another one.

I used the TransferSpreadsheet function in a new Macro and set it to
export my table as an Excel file.

With this function you have to specify the file name and save path
under the 'File Name' criteria in the Macro.

I would like to be able to control where the file is saved based on an
unbound field on the menu screen.

I tried typing [Forms]![Archive Records Database]![ExportDirectory] into the
Macro field 'File Name' but this doesn't work - the macro runs and
does nothing - don't even get an error message. Does anyone know how
to make this work?

"DeZZar" wrote in message
news:1b915e3b-7cef-455e-b66e-2787bf1d0375-at-a3g2000prm.googlegroups.com...
>
> I need to regularly backup my database as an Excel file

Well unless your database consists of one flat table then that just isn't
possible without storing redundant duplicates. Using Excel as a data backup
method isn't one I'd recommend.

Keith.

> Well unless your database consists of one flat table then that just isn't
> possible without storing redundant duplicates. *Using Excel as a data backup
> method isn't one I'd recommend.
>
> Keith.

Thanks Keith, but the actual export of my multi table database is
working just fine based on the macro in place.

Backup for this database as an excel file is just fine - its what the
doctor ordered.

I'm just now trying to control where my maco puts the saved file based
on an unbound form field. Similar to how you control query criteria
from an unbound form field.

anyone got any ideas?

"DeZZar" wrote in message
news:a0080b06-d374-441e-889a-aee13dce82ad-at-l33g2000pri.googlegroups.com...
>
> the actual export of my multi table database is
> working just fine based on the macro in place.
>

That's as maybe, but have you attempted a disaster recovery using this
method, assuming that's what this "backup" is for?

> Backup for this database as an excel file is just fine - its what the
> doctor ordered.

Doctor Who? I don't think I'd let him/her loose with an endoscope.

> I'm just now trying to control where my maco puts the saved file based
> on an unbound form field. Similar to how you control query criteria
> from an unbound form field.

> anyone got any ideas?

Copy your mdb file and keep it somewhere safe. Use it for disaster recovery
as and when needed. If just the tables are required to be backed up then
split the database first.

DeZZar wrote:

> well I think I solved the problem mostly - and created another one.
>
> I used the TransferSpreadsheet function in a new Macro and set it to
> export my table as an Excel file.
>
> With this function you have to specify the file name and save path
> under the 'File Name' criteria in the Macro.
>
> I would like to be able to control where the file is saved based on an
> unbound field on the menu screen.
>
> I tried typing [Forms]![Archive Records Database]![ExportDirectory] into the
> Macro field 'File Name' but this doesn't work - the macro runs and
> does nothing - don't even get an error message. Does anyone know how
> to make this work?

Do you HAVE to use a macro?

From the Property sheet select Event and press the ... to the right.
Then enter
Docmd.Transferspreadsheet
and then follow the arguments.

Hi DeZZar,

I have code to do this. I will dig it up for you tonight and post it
tomorrow morning (my time) for you. The code creates a number of
diffferent options to handling file opens and saves based on the
standard windows common dialog box. The code does this without any
external dependancies or references, so all you need to really do is
to place the code in module, and call the code / function / sub you
want from the command button.

Hang tight :-)

Cheers

The Frog

> Hang tight :-)
>
> Cheers
> The Frog

Thanks Mr. Frog! Looking forward to it!

On Aug 27, 9:53*pm, "Keith Wilby" wrote:
> That's as maybe, but have you attempted a disaster recovery using this
> method, assuming that's what this "backup" is for?

Hi Keith, I basically work for a large bank and exporting the data to
an excel spreadsheet daily is simply the business continuity plan.
Should I no longer work for the bank they may choose to not use my
database any longer and simply continue with the old method - and
excel spreadsheet. The MDB itself is backed up to another drive
regularly also. This excel export is simply to backup the raw data in
a format any old Johny can understand.

Hi Again deZZar,

Here is the code. Place it in a module (standard module not a class
module).

'CODE STARTS HERE ***********

'API: Call the standard Windows File Open/Save dialog box
'Paste the following code in a new module
'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed, except as part of an
application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function ahtCommonFileOpenSave( _
Optional ByRef Flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal FileName As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hwnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
' Give the dialog a caption title.
If IsMissing(InitialDir) Then InitialDir = CurDir
If IsMissing(Filter) Then Filter = ""
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(FileName) Then FileName = ""
If IsMissing(DialogTitle) Then DialogTitle = ""
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
..lStructSize = Len(OFN)
..hwndOwner = hwnd
..strFilter = Filter
..nFilterIndex = FilterIndex
..strFile = strFileName
..nMaxFile = Len(strFileName)
..strFileTitle = strFileTitle
..nMaxFileTitle = Len(strFileTitle)
..strTitle = DialogTitle
..Flags = Flags
..strDefExt = DefaultExt
..strInitialDir = InitialDir
' Didn't think most people would want to deal with
' these options.
..hInstance = 0
..strCustomFilter = ""
..nMaxCustFilter = 0
..lpfnHook = 0
'New for NT 4.0
..strCustomFilter = String(255, 0)
..nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If

' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(Flags) Then Flags = OFN.Flags
ahtCommonFileOpenSave = TrimNull(OFN.strFile)
Else
ahtCommonFileOpenSave = vbNullString
End If
End Function
Function ahtAddFilterItem(strFilter As String, _
strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

If IsMissing(varItem) Then varItem = "*.*"
ahtAddFilterItem = strFilter & _
strDescription & vbNullChar & _
varItem & vbNullChar
End Function
Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function

Function ChooseXLFile() As Variant

Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Don't change directories when done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.

lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR

varDirectory = ""

varTitleForDialog = "Please specify a file to save (*.xls)"

' Define the filter string and allocate space in the "c"
' string.

strFilter = ahtAddFilterItem(strFilter, "Excel (*.xls)", "*.xls")

' Now actually call to get the file name.

varFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
ChooseXLFile = varFileName
End Function

Sub ExcelTableDump(TableName as String)
FileName = ChooseXLFile
if FileName <> "" then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
TableName, FileName, True
end if
End Sub

'CODE ENDS HERE ***


On your form you said you wanted to make this happen from a button.
What you need to do is to look at the Properties of the button, choose
the Event tab, and select the OnClick event. The OnClick event needs
to be an Event Procedure. This selection can be chose from the small
button at the side of the events listing in the property window that
looks a bit like [...].

If it is set correctly to Event Procedure a code window will open. It
should have some code already there that looks like this:

Private Sub Command0_Click()

End Sub

This is the definition for what happens when someone clicks the button
on the form. We need to add only a single command to this between the
Private Sub line and the End Sub line, as follows:

Private Sub Command0_Click()
ExcelTableDump "YOUR TABEL NAME GOES HERE"
End Sub

This line of code will execute all the necessary things you need to
happen. Run the form and click the button, and you should see a
standard windows file save dialog box, enter the name of the file you
want to save, click save, and you are all done.

Be careful of word wrapping when copying and pasting the code into a
module, and dont forget to change the name of the table you want to
export (keep the quote marks).

Hope this helps

Cheers

The Frog