[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export Contents of Table to Excel Spreadsheet

Posted on 2006-05-31
21
Medium Priority
?
302 Views
Last Modified: 2010-08-05
I have a table (tblSiteVisitors) that I want to export to an Excel Document.  I want to give the user the option of where to save this document at.

How would I go about doing this?

Thanks!
Lena
0
Comment
Question by:LenaWood
  • 9
  • 5
  • 5
  • +1
21 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16801088
   DoCmd.TransferSpreadsheet acExport, 8, "YourTable", "", True, ""
J
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801115
u could use the filebrowse, which prompts what directory
then u can create the filename of your choice - i.e. report

look at my example code here
http://www.experts-exchange.com/Databases/MS_Access/Q_21865838.html
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16801139
Sorry Lena, that wouldn't prompt your user for a location to save.
How are you presenting this ability to your user....a form with a button?
J
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Author Comment

by:LenaWood
ID: 16801213
Yes Jeff, I have a form and a button.  Wanting to use the OnClick event of the button.

Rocki - trying to not get lost in the code example that you have given me to look at.  Be back with any questions that I may have.

Lena
0
 
LVL 10

Author Comment

by:LenaWood
ID: 16801279
Rocki - I am afraid I am lost with the code example that you gave me.  I don't understand how to use it to save a file from Access.  It seems like it is used to open a file (from what I understand of it that is).

I really don't want this to be so difficult haha.

Lena
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 16801360
Lena, its easy and someone of your calibre wont have any problems

ok, example 1. You prompt for what directory they want it dumped in

add this code into a module



Private Type BROWSEINFO
    hOwner          As Long         'handle to window opening dialog
    pidlRoot        As Long         'A pointer to an ITEMIDLIST structure (a.k.a. a PIDL) which identifies the root folder for the dialog box. The user's selection is limited to this folder and any subfolders under it
    pszDisplayName  As String       'Receives the null-terminated display name of the folder the user selects. This must be initialized to an empty string of at least 260 characters
    lpszTitle       As String       'The title of the dialog box, which will appear above the folder tree
    ulFlags         As Long         'See BIF flags above
    lpfn            As Long         'A pointer to the BrowseCallbackProc callback function used to process the dialog box's messages. To use the default behavior, set this to 0
    lParam          As Long         'An application-defined value to pass to the callback function, if needed
    iImage          As Long         'Receives the index of the system image associated with the user's selection
End Type

Private Declare Function SHBrowseForFolder Lib "shell32" Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pIdl As Long, ByVal pszPath As String) As Long


Public Function BrowseDirectory() As String

    Dim tBI As BROWSEINFO
    Dim pIdl As Long
    Dim sPath As String
   
   
    tBI.lpszTitle = "Browse XLS"
    tBI.ulFlags = &H4000
    pIdl = SHBrowseForFolder(tBI)
   
    'Check for cancel
    If pIdl = 0 Then Exit Function

    'Get selected path from the id list, will rtn False if the id list can't be converted
    sPath = String$(260, 0)
    SHGetPathFromIDList ByVal pIdl, ByVal sPath
   
    ' Display the path and the name of the selected folder
    BrowseDirectory = Left(sPath, InStr(sPath, vbNullChar) - 1)
   
End Function



0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16801367
Rocki's on the right track and you're in good hands.
J
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16801393
next we call this then export
e.g

public sub ExportTable

    dim sDir as String
    dim sFile as String


    sDir = BrowseDirectory
    if sDir <> "" then
         'We have the directory name, give it a name
         sFile = "ExportedContents.xls"

         'Now we export - the last parameter True means we start excel after export
         DoCmd.OutputTo acOutputTable, "tblSiteVisitors", acFormatXLS, sdir & "\" & sfile, True
    end if
end sub

         
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801395
thanks for the compliment Jeff
0
 
LVL 10

Author Comment

by:LenaWood
ID: 16801684
Jeff - thanks for trying to help me out.  I appreciate your time and effort.

Rocki - Thank you so much for your time and patience :-)

Lena
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801697
No probs
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16802091
I was NO help this time around! :o) But always willing to try.  This one is ALL Rocki. ..Say Rocki, how did you get a name like that? hehehe
0
 
LVL 10

Author Comment

by:LenaWood
ID: 16802186
Jeff - just knowing that you are willing to give time to those that need help is a BIG help.  Without such people this place wouldn't be...and who would I sponge knowledge from?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802511
funny that, your the 2nd person to ask me that today

hmm, not sure if I can say ....
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802540
Jeff, your pretty new to this place, so in case no one else has greeted you

WELCOME MATE TO EE

Your on your way to becoming an expert, as u certainly have the knowledge

I hope your stay here will be pleasant

Will be nice working with you

one word of advice though, Im sure you have noticed

Sometimes responses are quick so u got to be quick off the mark when your answering


0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16802861
thanks Rocki....and I saw where someone else had asked you about your name...thought it would be funny to repeat. EE has helped me along so much...when I have time I'm sure going to give back where I can. Between you and JimHorn, I feel in the presence of greatness. Everyone here is lucky to have you.
J
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802888
dont say that, I'll be walking around with a big head :)
0
 
LVL 10

Author Comment

by:LenaWood
ID: 16802925
You "Will be"?  I thought I had praised you enough in the past that you would have been already.  You deserve every word of praise given to you - and then some :-)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16805039
Compliments galore
Yes Lena, u have praised me also, but like with all good things, it never lasts and Im back to being a regular pleb

thanks to all
0
 

Expert Comment

by:Matt Olson
ID: 20431584
I have created the module using the coding listed in this discussion but I tried altering the code so I could export the table as an .dbf file instead of an .xls but I get the error that the path is invalid. The debugger highlights the docmd line and says "Run-Time error '3044'. H:\waiting\rachel\finish.dbf is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides." I am connected to the server and the path should be valid. Code is attached.

Please help. Thanks in advance
Public Sub ExportTable()
 
    Dim sDir As String
    Dim sFile As String
 
 
    sDir = BrowseDirectory
    If sDir <> "" Then
         'We have the directory name, give it a name
         sFile = "finish.dbf"
 
         DoCmd.TransferDatabase acExport, "dBase IV", sDir & "\" & sFile, acTable, "Finish", "Finish.dbf"
 
    End If
End Sub

Open in new window

0
 

Expert Comment

by:Matt Olson
ID: 20451177
I was able to trouble shoot the coding on my own, I though I would post the solution for the next person looking to export a .dbf from Access with a prompt of where to save the file.
Public Sub ExportTable()
 
    Dim sDir As String
    Dim sFile As String
 
 
    sDir = ExportDNC
    If sDir <> "" Then
 
         DoCmd.TransferDatabase acExport, "dBase IV", sDir, acTable, "DNCTABLE", "DNCTABLE.dbf"
 
    End If
End Function
    

Open in new window

0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question