By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

EXPORT AND DELETE from access to excel

P: 32
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
May 12 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
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

Expand|Select|Wrap|Line Numbers
  1. Dim strFolderName As String
  2. strFolderName = BrowseFolder("What Folder you want to select?")
  3. Me.mytextbox = strFolderName & "\"
  4.  
Paste this entire code into a CODE module. Name the module anything except the function name.

Expand|Select|Wrap|Line Numbers
  1. '************** Code Start **************
  2. 'This code was originally written by Terry Kreft.
  3. 'It is not to be altered or distributed,
  4. 'except as part of an application.
  5. 'You are free to use it in any application,
  6. 'provided the copyright notice is left unchanged.
  7. '
  8. 'Code courtesy of
  9. 'Terry Kreft
  10.  
  11. Private Type BROWSEINFO
  12.   hOwner As Long
  13.   pidlRoot As Long
  14.   pszDisplayName As String
  15.   lpszTitle As String
  16.   ulFlags As Long
  17.   lpfn As Long
  18.   lParam As Long
  19.   iImage As Long
  20. End Type
  21.  
  22. Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
  23.             "SHGetPathFromIDListA" (ByVal pidl As Long, _
  24.             ByVal pszPath As String) As Long
  25.  
  26. Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
  27.             "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
  28.             As Long
  29.  
  30. Private Const BIF_RETURNONLYFSDIRS = &H1
  31. Public Function BrowseFolder(szDialogTitle As String) As String
  32.   Dim X As Long, bi As BROWSEINFO, dwIList As Long
  33.   Dim szPath As String, wPos As Integer
  34.  
  35.     With bi
  36.         .hOwner = hWndAccessApp
  37.         .lpszTitle = szDialogTitle
  38.         .ulFlags = BIF_RETURNONLYFSDIRS
  39.     End With
  40.  
  41.     dwIList = SHBrowseForFolder(bi)
  42.     szPath = Space$(512)
  43.     X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
  44.  
  45.     If X Then
  46.         wPos = InStr(szPath, Chr(0))
  47.         BrowseFolder = Left$(szPath, wPos - 1)
  48.     Else
  49.         BrowseFolder = vbNullString
  50.     End If
  51. End Function
  52. '*********** Code End *****************
  53.  
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

Expand|Select|Wrap|Line Numbers
  1. Dim myFileName As String
  2. myFileName = Me.mystartdate & "to" & Me.myenddate
  3. DoCmd.OutputTo acOutputTable, "yourtablename", acFormatXLS, myFileName, False
  4.  
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
May 12 '07 #2

JConsulting
Expert 100+
P: 603
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
one slight change...

this would go into the last code segment I sent replacing the line

Expand|Select|Wrap|Line Numbers
  1. myFileName = Me.mypathtextbox & Me.mystartdate & "to" & Me.myenddate
  2.  
Had to include the path that your user selected.
J
May 12 '07 #3

P: 32
Brilliant. Where would i add that last piece of code?
May 13 '07 #4

P: 32
sorry, can you explain where all the code should be put as it doesnt state
May 13 '07 #5

JConsulting
Expert 100+
P: 603
sorry, can you explain where all the code should be put as it doesnt state

Do you mean the last post? OR all of it?
May 13 '07 #6

P: 32
just the bit after the large module code
May 14 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.