Clicky

Hi ,

I have been given a load of data in excel.
In simple terms the data consist of 3 fields

Salesman , CustomerName , SalesThisMonth
Joe, Acme , €90,000
Joe, IBM, €30,000
Tim, Ford, €44,000
...
...

I need to get this data from Access into Excel.

Complication:
I need to create ONE new sheet for each salesman - (i.e. Joe's sheet will be called "Joe's Sales")

I will NOT know the names of the salesman in advance and the names of the salesman may change each month.

Can someone give me pointers as to how I create about 50 sheets in one workbook.  Each sheet MUST have the same name as the salesman.  

asked 12/12/2011 10:44

21Dewsbury's gravatar image

21Dewsbury ♦♦


7 Answers:
dim rs as dao.recordset
dim shtName as string
set rs=currentdb.openrecordset("select distinct salesman from tableName")

do until rs.eof
   shtName= rs!salesman & " Sales"

   docmd.transferspreadsheet acexport,,"queryortableName","c:\folder\yrExcel.xls",true,shtName

rs.movenext
loop




link

answered

capricorn1's gravatar image

capricorn1

Give this a try (You need a reference set to Excel):

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
Public Function xport()
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim rsTabs As DAO.Recordset
    Dim I As Integer
    On Error GoTo eh
    I = 0
    'This rs contains the Number & Name for the WS Names/ Tab captions
    Set rsTabs = CurrentDb.OpenRecordset("SELECT DISTINCT Vendor AS TabName FROM tblVendor")
    Set xlApp = New Excel.Application

    Set xlWB = xlApp.Workbooks.Add
    Do Until rsTabs.EOF
        I = I + 1
        Set xlWS = xlWB.Worksheets.Add
        xlWS.Name = rsTabs!TabName    'Set the current worksheet's Name /Tab caption
        'Next worksheet/tab
        rsTabs.MoveNext
    Loop
    xlApp.Visible = True
ExitFN:
    'Clean things up
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing
    rsTabs.Close
    Set rsTabs = Nothing

    Exit Function
eh:
    MsgBox Err.Number & ": " & Err.Description
    GoTo ExitFN
    
End Function
link

answered 2011-12-12 at 18:59:15

mbizup's gravatar image

mbizup

That is an example I posted in a similar question some time ago.

You would have to modify this line to use your own table/field names:

<<rsTabs = CurrentDb.OpenRecordset("SELECT DISTINCT Vendor AS TabName FROM tblVendor")>>
link

answered 2011-12-12 at 19:04:21

mbizup's gravatar image

mbizup

first create a query
select * from tableName
save the query as
 "qrySales"


sub export2XL()
dim rs as dao.recordset, qd as dao.querydef, db as dao.database
dim shtName as string, sSql as string, oSql as string
set db=currentdb
set qd=db.querydefs("qrySales")
osql=replace(qd.sql,";","")

set rs=db.openrecordset("select distinct salesman from tableName")

do until rs.eof
    sSql=oSql & " where [Salesman]=" & chr(34) & rs!salesman & chr(34)
    qd.sql=sSql

   shtName= rs!salesman & " Sales"

   docmd.transferspreadsheet acexport,10,"qrySales","c:\folder\yrExcel.xls",true,shtName

   ssql=""
rs.movenext
loop


link

answered 2011-12-12 at 19:06:47

capricorn1's gravatar image

capricorn1

that code will not only create the excel file with the saleman tabs, but with the records associated with the salesman. oops forgot the end sub


dim rs as dao.recordset
dim shtName as string
set rs=currentdb.openrecordset("select distinct salesman from tableName")

do until rs.eof
   shtName= rs!salesman & " Sales"

   docmd.transferspreadsheet acexport,,"queryortableName","c:\folder\yrExcel.xls",true,shtName

rs.movenext
loop

rs.close
qd.sql=oSql



end sub
link

answered 2011-12-12 at 19:29:21

capricorn1's gravatar image

capricorn1

oops sorry posted the wrong codes

these code will not only create the excel file with the saleman tabs, but with the records associated with the salesman.

first create a query
select * from tableName
save the query as
 "qrySales"




1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
sub export2XL()
dim rs as dao.recordset, qd as dao.querydef, db as dao.database
dim shtName as string, sSql as string, oSql as string
set db=currentdb
set qd=db.querydefs("qrySales")
osql=replace(qd.sql,";","")

set rs=db.openrecordset("select distinct salesman from tableName")

do until rs.eof
    sSql=oSql & " where [Salesman]=" & chr(34) & rs!salesman & chr(34)
    qd.sql=sSql

   shtName= rs!salesman & " Sales"

   docmd.transferspreadsheet acexport,10,"qrySales","c:folderyrExcel.xls",true,shtName

   ssql=""
rs.movenext
loop


rs.close
qd.sql=oSql

end sub
link

answered 2011-12-12 at 19:38:53

capricorn1's gravatar image

capricorn1

Thanks folks, perfect!

I went with the capricorn1 solution
link

answered 2011-12-12 at 19:40:45

21Dewsbury's gravatar image

21Dewsbury

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/12/2011 10:44

Seen: 12734 times

Last updated: 12/17/2011 09:02