Hi all,
I wish to find out how to export data to excel from access as follows:
Select date range (which i understand how to do)
The data from an entire table is sent to excel
the filename of that export is the date range i selected
I am asked where i wish to save the file
On success, the table data is deleted
1) create a form with a textbox and a button.
the textbox will hold the path returned by the code that gets executed when the button is pressed.
this the button code
-
Dim strFolderName As String
-
strFolderName = BrowseFolder("What Folder you want to select?")
-
Me.mytextbox = strFolderName & "\"
-
Paste this entire code into a CODE module. Name the module anything except the function name.
-
'************** Code Start **************
-
'This code was originally written by Terry Kreft.
-
'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
-
'Terry Kreft
-
-
Private Type BROWSEINFO
-
hOwner As Long
-
pidlRoot As Long
-
pszDisplayName As String
-
lpszTitle As String
-
ulFlags As Long
-
lpfn As Long
-
lParam As Long
-
iImage As Long
-
End Type
-
-
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
-
"SHGetPathFromIDListA" (ByVal pidl As Long, _
-
ByVal pszPath As String) As Long
-
-
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
-
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
-
As Long
-
-
Private Const BIF_RETURNONLYFSDIRS = &H1
-
Public Function BrowseFolder(szDialogTitle As String) As String
-
Dim X As Long, bi As BROWSEINFO, dwIList As Long
-
Dim szPath As String, wPos As Integer
-
-
With bi
-
.hOwner = hWndAccessApp
-
.lpszTitle = szDialogTitle
-
.ulFlags = BIF_RETURNONLYFSDIRS
-
End With
-
-
dwIList = SHBrowseForFolder(bi)
-
szPath = Space$(512)
-
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
-
-
If X Then
-
wPos = InStr(szPath, Chr(0))
-
BrowseFolder = Left$(szPath, wPos - 1)
-
Else
-
BrowseFolder = vbNullString
-
End If
-
End Function
-
'*********** Code End *****************
-
Now when you press the button, a directory browser will open allowing your user to select the location they want for your file.
2)
Exporting an entire table
-
Dim myFileName As String
-
myFileName = Me.mystartdate & "to" & Me.myenddate
-
DoCmd.OutputTo acOutputTable, "yourtablename", acFormatXLS, myFileName, False
-
That should about do it. Given that this all done on the same form. The last bit of code assumes that you have two textboxes on your form for start date and end date. the filename would look like 1/1/2007to1/31/2007
Enjoy!
J
32
