export access data to specific cells in worksheet

[waymond]

I have data in access tables lets say customer name. there are 20 customers I want to export that data to an excel worksheet and create a new worksheet for change of customer name. I need all the data to go to a specific cell on the spreadsheet

cust: custadd
microsoft 22 sty street
unissy

so I would need 2 worksheets for created in this example

please help

 

[SkipVought]

Hi,

You want a separate sheet for each customer? Not sure what you mean by "create a new worksheet for change of customer name"

Are you sure that you want to have a sheet for each customer? That's really not a very good design, at least it is not the way that a professional would tend toward.

What do you want to accomplish with this workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waymond]

yes here what happens I take the data from a main frame into Access then we have to type manually into separate excel worksheets for each customer because they all have different contracts. what I would like to do take the access database into a workbook that has like a 100 worksheets and at least fill in the customer name and address automatically for each customer so if 10 were downloaded there would be 10 different worksheets in the workbook with some fields already filled out

 

[SkipVought]

It would still be structurally and functionally better to have ALL your customers in one table, from which you could report or analyze. Having your data chopped up into different sheets may have some marginal value but the great loss is not being able to use Excel's powerful reporting and data analysis features.

But if you must...

Import your query from MS Access into a sheet, let's name it Query.

Make new sheets for each customer...

dim r as range, ws as worksheet, bNew as boolean
for each r in Sheets("Query").range(Sheets("Query").[A2], Sheets("Query").[A2].end(xldown))
   with r
     bNew = True
     for each ws in worksheets
       Select case ws.name 
          case "Query", .value
             bNew = false
       end select
     next
     if bNew then    'this customer does not have an existing sheet
        sheets.Add
        activesheet.name = .value
     end if
   end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}