Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Transferspreadsheet, while..wend range

    Here's hoping a handy coder is out there..

    Take a look at the below code, and my objective might seem quite clear to you.

    What I want to do, is use the transferspreadsheet function to export a specific table to Excel, naming the sheet in the process using the Range parameter.

    Is this possible? Or would I have to look into OA?

    Cheers, Trin

    Code:
        Dim SQL As String
        Dim sortering As QueryDef
        Dim udvalg As QueryDef
        Dim fd As FileDialog
        Dim ark as String (this is the string variable used for the range!)
        Dim orkestrers As Recordset
        Set orkestrers = CurrentDb.OpenRecordset("ork_vw_medlemsnumre")
           
        MsgBox "Vælg hvor Excel-arket skal gemmes og angiv et navn", vbOKOnly
    
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        fd.InitialFileName = "F:\"
        fd.show
        filen = fd.SelectedItems(1) & ".xls"
       
        While orkestrers.EOF <> True
        
            DoCmd.SetWarnings False
            DoCmd.DeleteObject acQuery, "enkeltudtrek"
            DoCmd.SetWarnings True
            
            SQLudvalg = "SELECT ork_tbl_afspilninger.Medlemsnummer, ork_tbl_afspilninger.Søgenavn, ork_tbl_afspilninger.[Gramex-ID], ork_tbl_afspilninger.Side, ork_tbl_afspilninger.Tracknummer, ork_tbl_afspilninger.stations, Sum(ork_tbl_afspilninger.[Afspillet beskyttede minutter]) AS [SumOfAfspillet beskyttede minutter] " & _
                  "FROM ork_tbl_afspilninger WHERE (((DatePart('yyyy', [Rapporteringsdato])) = " & box_aar.Value & ") and Medlemsnummer ='" & orkestrers!medlemsnummer & "') " & _
                  "GROUP BY ork_tbl_afspilninger.Medlemsnummer, ork_tbl_afspilninger.Søgenavn, ork_tbl_afspilninger.[Gramex-ID], ork_tbl_afspilninger.Side, ork_tbl_afspilninger.Tracknummer, ork_tbl_afspilninger.stations;"
        
            With CurrentDb
                Set udvalg = .CreateQueryDef("enkeltudtrek", SQLudvalg)
            End With
            
            ark = "" & Trim(Str(orkestrers!medlemsnummer)) & "!"
            
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ork_vw_summermedinfo_eksport_udtrek", filen, True, ark
    
            orkestrers.MoveNext
        
        Wend
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm sure it's possible. However, I don't do a lot of work with Excel, but there are some that cruise past here that do and will be able to point you in the right direction.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2005
    Posts
    19

    Bad news, good news

    The bad news is that the range option to the transferspreadsheet command is only valid for importing from Excel, not exporting which you want to do.

    The good news is that you can use the Excel copyfromrecordset command to accomplish what you want. Within access, you'll have to create an excel object for the spreadsheet that is to receive the data and create a recordset for the data. The format is something like

    myxcelobj.range(myrange).copyfromrecordset myaccessrs

    hope this helps!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    If you want to rename a sheet within a workbook, you'll need to use Access to control Excel. Once you've opened the file that you need, you can rename the sheet using standard Excel VBA commands.
    I have a database that exports four queries to an Excel wowrkbook, then opens the book, renames each of the sheets within it and then creates a new sheet and adds some values, formatting and formulae. If this sounds like it would be useful, let me know and I'll sort out the code for you.
    EDIT
    Forgot to say that it has a tendency to fall over the first time around, and then work properly. I've been meaning to post a qustion about it here, but then get confused as to whether it should be in the Access or Excel forum.

Posting Permissions

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