Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Exporting (Access 97\SR2)

    Is it possible to create a button that works similar to the "Publish with MS Excel" button, so I can set the data source instead of it just picking it up from whatever is onscreen? I know how to export in to a folder previously specified - but I don't know how to export and have the standard "save as" box come up where you select your destination and filename.
    Any help would be much appreciated.

  2. #2
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    Phil

    Have you tried the TransferSpreadsheet action in Macros ??
    Transfer Type ----------------- Export
    Spreadsheet Type ------------ Microsoft Excel
    etc....

    Si

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    Thanks for you tip Si, however:
    I don't think I explained the problem very well, as I used the "publish with Excel" button as an example despite the fact that it doesn't do what I want it to do.
    I'd like an export function that lets the user specify the filename and location of the spreadsheet in the same was as regular save functions in all MS applications - however I think this maybe entering into the world of VB not VBA!

  4. #4
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    Phil

    This is the VBA for Exporting the spreadsheet.
    DoCmd.TransferSpreadsheet acExport, 8, "has", "c:datahas.xls", True, ""

    "CDatahas.xls" could be replaced with a string linked to a text box.... Otherwise you would be looking at VB.
    Si

    P.S. I only live round the corner from you on the Esplanade !!!!
    Si_hill@yahoo.co.uk

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    Wow, small world isn't it!

    Thats the VBA that I was using before the save box was requested, but thanks anyway.
    For the record, I have been given the code for the save module - it's about 4 pages long!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Exporting (Access 97\SR2)

    Hi Phil,
    Depending on what exactly you're trying to export, you almost certainly don't need 4 pages of code! Can you give a bit more detail as to what you're doing? I would guess that some fairly simple automation code ought to do what you need.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    I have a form displaying results from a search function. The data for the form comes from an SQL statement in the search form which is set as the datasource using .Form.RecordSource = stringsql.

    I now need to have the option to save the search results which are displayed on the form. The only methods I know for exporting have pre set names and paths, which I know I can get from unbound text boxes - however, some of the users of this database may not be overly computer literate and I'd like a save box like the standard MS save box where they can select the destination from the contents of their hard drive and give the file a name.

    Hope that makes in a bit clearer.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Exporting (Access 97\SR2)

    OK. Something like this should work:
    <pre>Function ExportXLData(strSQL As String)
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim appXL As excel.Application, wbkNew As excel.Workbook
    Dim strFileName, lngColumn As Long
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    Set appXL = New excel.Application
    strFileName = appXL.GetSaveAsFilename(fileFilter:="Excel Workbooks (*.xls), *.xls")
    If strFileName <> False Then
    Set wbkNew = appXL.Workbooks.Add
    With wbkNew
    With Sheets(1)
    For lngColumn = 1 To rst.Fields.Count
    strFldName = rst.Fields(lngColumn - 1).Name
    .Cells(1, lngColumn).Formula = strFldName
    Next lngColumn
    .Range("A2").CopyFromRecordset rst
    End With
    .SaveAs strFileName
    .Close
    End With
    Set wbkNew = Nothing
    Else
    MsgBox "Operation cancelled."
    End If
    appXL.Quit
    Set appXL = Nothing
    End Function
    </pre>

    You can paste that into a normal module and then in your form module you would simply put:
    Call ExportXLData(stringsql)
    You will also need to set a reference to the Excel object library (in any module, choose Tools-References and check the Excel 8.0 object library).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    Cool, thanks for that Rory, however:
    I got an error to start with, which was strfldname wasn't defined, so I defined it as string.
    After that it seemed to run fine, and it saved the spreadsheet, however there was no data in it. I double checked the SQL and ran it in a query & it did return data. I have also checked the Excel box in references as suggested.
    If you haven't got time, don't worry too much about solving this one as the monster code does work, but obviously the least amount of code used usually means for a better application!

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Exporting (Access 97\SR2)

    Sorry, Phil - looking at what I posted I think I can see what's wrong: there's a full stop missing before Sheets(1).
    So it should read:
    With .Sheets(1)
    not
    With Sheets(1)
    Does that fix it?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Exporting (Access 97\SR2)

    Excellent, works a treat. Thanks for that Rory.

    I've been making databases for about a year now, but they've always been for inhouse use - this ones for rep use and it must be said, a lot more thought has to go into it when you can no longer walk accross the office to fix a problem or provide instruction!!!!

Posting Permissions

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