12-02-08, 08:40 #1Registered User
- Join Date
- Oct 2005
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?
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 WendIT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....
12-02-08, 18:36 #2L33t Helpa Munky
- Join Date
- Nov 2007
- Adelaide, South Australia
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
12-03-08, 10:09 #3Registered User
- Join Date
- Sep 2005
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
hope this helps!
12-04-08, 09:49 #4Registered User
Provided Answers: 2
- Join Date
- Sep 2006
- Surrey, UK
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.
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.