Link to home
Start Free TrialLog in
Avatar of LenaWood
LenaWood

asked on

Export Contents of Table to Excel Spreadsheet

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
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

   DoCmd.TransferSpreadsheet acExport, 8, "YourTable", "", True, ""
J
Avatar of rockiroads
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
https://www.experts-exchange.com/questions/21865838/How-to-capture-Word-document-name-to-store-in-Access-table.html
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
Avatar of LenaWood
LenaWood

ASKER

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
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
SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rocki's on the right track and you're in good hands.
J
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for the compliment Jeff
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
No probs
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
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?
funny that, your the 2nd person to ask me that today

hmm, not sure if I can say ....
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


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
dont say that, I'll be walking around with a big head :)
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 :-)
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
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

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