Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Exporting from Access to Excel

Status
Not open for further replies.
Finedean

Finedean

MIS
May 4, 2006
80
US
Hi All,
I have the following table that I would like to export to Excel:

Town Name

Bellmore John
Freeport Paul
Bellmore Ismail
Merrick Bob
Merrick Alan
Freeport Mike
Merrick Susan
Bellmore Andria
Bellmore Paula
Freeport Sunita

I have no problem exporting the data to an excel sheet, but when the data is exported it's all in one tab. What I would help with is the following:

when the file is exported I would like to see the towns in seperate tabs. tab one for Bellmore and the list of name, tab 2 Freeport and the list of names...etc...
I hope this is clear.

Thanks in advance...
 
Sort by date Sort by votes
You will need to build multiple queries for this and use the TransferSpreadSheet function. Something like this
Code: 
Public Sub SendToExcel()
    Dim SQL As String
    Dim rs  As DAO.Recordset
    Dim qd  As DAO.QueryDef
    
    SQL = "Select DISTINCT Town From myTable Order By Town"
    Set rs = CurrentDb.OpenRecordset(SQL)
    Do Until rs.EOF
        On Error Resume Next
        CurrentDb.QueryDefs.Delete ("qryTowns")
        On Error GoTo 0
        Set qd = New DAO.QueryDef
        qd.Name = "qryTowns"
        qd.SQL = "Select * From myTable Where Town = '" & rs![Town] & "'"
        CurrentDb.QueryDefs.Append qd
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                                  "qryTowns", "Towns.xls"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    CurrentDb.QueryDefs.Delete ("qryTowns")
End Sub
The documentation for TransferSpreadsheet says (in part)
File Name
... Access copies the exported data to the next available new worksheet in the workbook. ...
Click to expand...
 
Upvote 0 Downvote
Finedean,
Are you running the export from a form?

The reason I ask is you can use [tt]TransferSpreadsheet()[/tt] to do this by specifying the [tt]Range[/tt] argument for the tab name and a simple paramter query that filters by Town. This is all well an good except you need a mechanism (like a Form field) that has a listing of all the Towns that you can refer to as the parameter for the query.

Here is an example.[ol]
[li]Created a new Form called [tt]frmTown[/tt][/li]
[li]Set it's RecoredSource to:
[tt]SELECT [Town] FROM YourTable GROUP BY [Town];[/tt][/li]
[li]Add the field [tt][Town][/tt] to the Detail section.[/li]
[li]Add a Command Button ([tt]Command1[/tt]) to the form with the following code:
Code: 
Private Sub Command1_Click()
Me.Recordset.MoveFirst
Do
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTown", "C:\Town.xls", True, Me.Town
  Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF
End Sub
[/li]
[li]Then created a new Query ([tt]qryTown[/tt]) with the following SQL:
[tt]SELECT YourTable.*
FROM YourTable
WHERE (((YourTable.Town)=[Forms]![frmTown]![Town]));[/tt][/li][/ol]

When I open the form and click the button I get a new Excel workbook (Town.xls) with a new tab for each town.

This should get you to the same place as Golom's solution, but with less code.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
Well CautionMP,

since it is the second thread you mention that range argument of TransferSpreadsheet Method for exporting, I tried that out of curiosity! I did check also the help and I can still read that : "This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail."
And the result was ... no error, exporting was ok and the range argument worked, naming the worksheet as defined in my export ... with a little underscore in front.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "c:\test.xls", True, "A5"

Worksheet name is _A5

So, should we call this a bug or a feature? Or am I missing something ?
 
Upvote 0 Downvote
JerryKlmns,
I don't know, sounds like a questions for Microsoft. I'm running Office 2k SR-1 on Windows XP and my help file says the same thing, yet it works.

If I had known this years ago I could have avoided writting a whole bunch of automation routines to move data from Access to Excel.

Since it works in our favor should we call it an ill-documented feature?

CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
I am so sorry guys. My internet went down the last 2 days. I will try your suggestions and I'll let you know.
Thanks
Dean

 
Upvote 0 Downvote
CautionMP, your solution worked perfectly. I love it.
Golom, I will try your solution to learn another way of doing it.
Again, thanks so much guys for your help.

Dean.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

OCM
  • Locked
  • Question
Populate access table from excel
Replies
6
Views
671
OCM
OCM
renigar
  • Question
Access VBA Syntax Error
Replies
5
Views
167
Andrzejek
Andrzejek
renigar
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
368
renigar
renigar
lmcate
  • Locked
  • Question
How to Open ACCESS (MDB) from an HTML Page 1
Replies
2
Views
573
lmcate
lmcate
TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
636
TheresAlwaysAWay
TheresAlwaysAWay

Part and Inventory Search

Sponsor

Back
Top