Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Question Unanswered: Export Access 2003 data to Excel via a form

    OK... I have a set of forms that I am using to access a table and some queries\reports. I need to be able to export selected columns in the table to Excel, or a format that Excel can use. Now I know that I could do a select query and make a table appear that oculd be copied and pasted, but the problem is that any changes made to the data in that query would be made to the database, and I can't let that happen.

    I need to continue to have the users restricted so that they can only make changes to the database through the form. So... any idea how to either make a table appear that can be copied and pasted into Excel, or simply generate a csv document or something from the form? Maybe by putting a button on it?

    Please advise, thank you.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Put a button on the form that will execute the TransferSpreadsheet command. I would suggest using a SELECT query to be the dataset to be exported to Excel. In the query you have complete control of which columns to export from a table, and if you need to select only certain rows (records), the query gives you that ability too. If you are not familar with TransferSpreadsheet, use Access help to study up on it. If you still need help after reading the Access help file, come back with some more questions.

  3. #3
    Join Date
    Sep 2007
    Posts
    4
    Unfortunately while I see information on how it works, sort of... I have no idea how to make a button trigger it or anything. When adding a button, TransferSpreadsheet doesn't appear to be one of the options to select, and I am not sure how else to do it.

    I do apologize for my blatant ignorance here, this is far outside my normal scope and I am not familiar with much of Access, as it relates to forms or VB. So far I've just had to use it for table operations, and that hasn't been so... complicated.

  4. #4
    Join Date
    Sep 2007
    Posts
    4
    OK, here is the script that I have working now...

    Code:
    Private Sub Export_to_Excel_Click()
    Dim Response As String
    DoCmd.TransferSpreadsheet acExport, 8, "HBO_Users_Query", "HBO_Users_Excel.xls", True, ""
    Kill ("HBO_Users_Excel.xls")
    DoCmd.TransferSpreadsheet acExport, 8, "HBO_Users_Query", "HBO_Users_Excel.xls", True, ""
    Response = MsgBox("Your file has been saved to your My Documents directory (usually H:\Data\HBO_Users_Excel.xls).", vbOKOnly, "File Saved")
    End Sub
    May not be the most elegant solution, but it works. I noticed that when I run the script multiple times it was appending the results if it was already there, and I couldn't kill the file if it wasn't there, so I just had it create/append the file, kill it, and create it again just so that it would only ever have 1 export worth of data. Since we are only talking about a maximum of 3,500 records at the moment with roughly 20 columns of data each, it doesn't take it very long at all to do it.

    I have only one questions now... when it exports the data to Excel, each cell starts with a single quote. So it would say '122345 or 'nameofuser, etc. Now, whether the field contains a number or text, there is absolutely no reason to have it insert a quote... why is it doing that? Can I make it so it doesn't? I don't want the quotes there...

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If you put some On Error code around your Kill statement, you can Kill the file, even if it is not there.
    Code:
    On Error Resume Next
    Kill ("HBO_Users_Excel.xls")
    On Error GoTo 0
    If you do this, you can remove your first TransferSpreadsheet statement.
    I would also suggest you point the TransferSpreadsheet operation to exactly where you want it to go, rather than leaving the path up to whereever the path is currently pointing. This will come back to hurt you. Both the Kill and the TransferSpreadsheet statements really do need a path.

  6. #6
    Join Date
    Sep 2007
    Posts
    4
    OK... and that makes sense. Any thoughts regarding TransferSpreadsheet inserting all of the single quotes at the beginning of everything though? I mean I know that Excel uses that to indicate that the text should appear exactly as it is typed in, but I really do not want them.

  7. #7
    Join Date
    Apr 2005
    Location
    leicester uk
    Posts
    5
    Save export as csv and not excel spreadsheet.
    Use excel to open the csv.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •