Create a form button to export a table

[DeZZar]

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 DB" 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!!

[DeZZar]

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 DB]![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?

[Keith Wilby]

"DeZZar" <de**************@gmail.comwrote in message
news:1b**********************************@a3g2000p rm.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.

[DeZZar]

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?

[Keith Wilby]

"DeZZar" <de**************@gmail.comwrote in message
news:a0**********************************@l33g2000 pri.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.

[Salad]

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 DB]![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.

[The Frog]

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

[DeZZar]

Hang tight :-)

>
Cheers
The Frog

Thanks Mr. Frog! Looking forward to it!

[DeZZar]

On Aug 27, 9:53*pm, "Keith Wilby" <[e-mail]:

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.

[The Frog]

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

[Keith Wilby]

"DeZZar" <de**************@gmail.comwrote in message
news:9e**********************************@s1g2000p ra.googlegroups.com...
On Aug 27, 9:53 pm, "Keith Wilby" <[e-mail]:

Ah, I see. Please accept my apologies, the "Johnny's" do like their
spreadsheets don't they? ;-)

Keith.

[The Frog]

It seems that the Johnies do indeed. I had a former boss who wanted
everything possible in Excel only. I believe that the main motivation
for this over endearment of Excel is simply one of the technologically
challenged. Excel seems to let the technically challenged (and
accountants) produce something that vaguely resembles work and gives
them a reason to update their laptops every six months.

The bit that always terrified me was that this former boss wanted the
excel table dump to be the only form of backup and dispose of the rest
- "Oh well there ya see, we can load the data straight back in. You'll
take care of that.". Lovely fellow. I really enjoyed watching him get
fired :-)

The Frog

[DeZZar]

Hi Mr.Frog,

Unfortunately I'm such a novice at this side of Access that the above
has rattled my head!

Basically - so I now copy and paste all of the above between where you
have marked ***Code start / Code end*** into the code builder of the
on click event proceedure for the desired button? and then just add
"ExcelTableDump "my tables name"" to the top of the code?

I've done basically the above - is there any part of the code that I
need to amend names of forms and tables in to match my database?

I'm getting the following error when I now click the button:
"The expression On Click you entered as the event property setting
produced the following error: Only comments may appear after End Sub,
End Function, or End Property.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceedure].
*There may have been an error evaluating the function, event, or macro

I went through and deleted everything that I thought was commentry
from the code and made sure text wrapping hadn't affected any of the
lines. But in alot of cases I'm not overly sure what I'm looking at to
ensure its not broken up.

Do I really paste the whole lot of the above or do I need to select
portions of the code to suit the situation.

Details:
Name of table to be exported: "Files"
Name of form with the button on it: "Archive Records DB"
Name of the button: "ExportBackup"
This error probably isn't terribly specific - but any help is much
appreciated!

Cheers
Dezzar

[DeZZar]

just in addition to this I ran the debugger on the code and it points
to the following area:

"Complie error:
Only comments may appear after End Sub, End Function, or End Property"

It then highlights the following arear of the above code:

After the first, Type, End Type section where the code begins:

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
this is what is highlighted.

[The Frog]

Hi DeZZar,

I see what you have done. You actually have it a little backwards. Its
much easier than you have made it :-)

Here's what you do:

1/ Go to the modules tab and create a new module - clear out the lines
at the top so that the whole thing is blank.
2/ Copy the code into the module (the stuff between ***code start ***
code end)
3/ Save the module with a name you like, I would suggest something
like modDump
4/ Go to the form where you have the button you want to use for
dumping the table, and open it in design view
5/ Go to the code view for the button and clear out all the code that
you have in there at the moment for the On_Click event
6/ Enter this one line of code into the On_Click event:

ExcelTableDump "YOUR TABEL NAME GOES HERE"

making sure that the name of the table / query you want to dump
appears inside the quote marks.

7/ Save the form
8/ Run the form to test it :-)
9/ Report back the results to this thread so we know if it worked for
you or not.

Sorry if the original instructions werent 100% clear. It was a heavy
week and the coffee machine wasnt working. Let us know how you go.

Cheers

The Frog

[DeZZar]

ah now that works much much better! Thank you sir!

I do make the following observations:
- it doesn't prompt you if you would like to overwrite an existing
file when you save the export
- at work I'm running 2003 I think....here at home I have 2007. The
standard code above uses Excel3 which created an error. I changed
this to 8 and it works fine so anyone else using the code check the
strong at the end: "DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel3, TableName, FileName, True" Also make sure
you put this into one line as its text wraped when copying from above.

I do wonder if:
- you could setup a default directory somewhere in the code?
- you could setup the save as with a default file name?

In any case this has worked beautifully!! thank you very much!!

[The Frog]

Hi DeZZar,

Glad that it is working for you. Its a neat little cobbled together
piece of code :-)

If you want to make the backup 'automatic' so to speak, it is possible
to designate a folder as the place to dump the files, it is also
possible to hard code (or maybe use a setting stored somewhere in the
database or in the registry or from a settings file) to get the
location to store the files, and at the same time add a sequence
number or timestamp to the filename - all the user would have to do is
to click the button and the rest would be done 'automatically'.

With that in mind, it would also be possible to build it into the
application even without the need for a button, by simply adding the
needed steps to the exit procedure of the app or whatever place is
appropriate for it to work. Maybe keep the button for a 'user
specified' dump of the table as well as having an application run
'auto-dump' to a pre-set location.

If you want to add in the 'auto-dump' all you would really need to do
is to make a new module (same as before for the other code). In this
module you will need to create a new Sub procedure with the details of
what you want it to do. Something like this would work as a suitable
chunk of code:

'***CODE STARTS HERE

Sub AutoDump()
TableName = "NAME OF TABLE GOES HERE"
Directory = "C:\DirectoryName"
FileName = "\" & TableName & " " & CStr(Now())
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
TableName, FileName, True
End Sub

'***CODE END HERE

Now this is a very basic chunk of code. If you want to test the idea
it is fine but I would want to introduce some error handling for
things like: does the directory actually exist? What to do if it
doesnt? Are long filenames supported? How to make a suitable name if
they are not? How many 'old' versions should be kept? Should this
process be the one that also removes outdated backups? Should there
also be a matching 'restore' procedure? And I am sure that I can think
of many more. How far you want to go is a matter of choice here. Full
integrated bespoke backup and restore? Incremental? Rollback support?
Maybe just a simple table dump is enough? Only you can decide that. I
am happy to help where I can.

Cheers

The Frog

[DeZZar]

the options are endless!!!

Becuase this file backs up to a shared drive - some people may not map
the drive as the same drive letter. What is X:\Files\Backup on my
machine might be Y:\Files\Backup on someone elses - which of course
complicates the automatic idea.

I've discovered that this code will simply 'ADD' to an existing
spreadsheet. Ie: if you create a backup - then open the file its of
course un formatted as far as column width etc goes. Well if I then
edit this excel file to have formatted column width, maybe make the
column title text bold and save it - the next time I hit my current
backup button and then select to 'overwrite' this file as such - the
backup code will not replace the file - it will simply refresh the
data it contains!

This works wonderfully for me. I could set the auto backup code to
simply freresh a "master backup' file each time the database is closed
and then the deliberate backup can be saved as dated files.

If I wanted the auto code you listed above to overwrite a file called
say "Master DB Backup.xls" would I modify it to look like this:
(might as well make it the real example)

******
Sub AutoDump()
TableName = "Files"
Directory = "X:\VIC\IronMountainBackup
FileName = "\" & "Master DB Backup"(Now())
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
TableName, FileName, True
End Sub
*******

So I would just create a nother module exactly the same as before -
paste in the code and then add the above say at the beginning? or
replace some of the original code??

Where can I find the event proceedure for closing the database? I
presume I just put the same code in here as the previous event
proceedure or do I change it slightly to be for the "AutoDump" module
code??

Thank you so much for your help so far!!!