Help - I Need Excel VBA Help With Sorting and New Worksheets

[Kim]

Greetings Folks,
I have a problem. I need to take a text delimited file from Access
and import it into Excel. Then parse out the information via the Owner
to separate worksheets and make the name of the sheet the Owners name
then email the sheet from Excel. Can anybody help?

Example of my Text File:

Company Owner
Phone Number
Quest Jim Smith
555-5555
Quest Jim Smith
555-5555
Pacific Jane Doe
555-5555
Pacific Jane Doe
555-5555
Eastern Joe Blow
555-5555
Eastern Joe Blow
555-5555

What I need is to sort by owner and make separate worksheets with just
that owners information. In this case I would need three worksheets one
for Jim, one for Jane, and one for Joe. Then at the bottom of the sheet
where it says "Sheet1" I want to have the Owner's name like Sheet 1 -
Jim Smith and Sheet 2 - Jane Doe and Sheet 3 - Joe Blow. Then email the
each worksheet to the owner. Could anybody help me with this?

 

[Glen]

Hello Kim.
Is the data in the database in seperate fields? If so, can you set up
a query to grab all of the pertinent information for your owners? If
you can, then you can use ADO library or the even Excel Query function
to import that data into your Excel sheet relatively easily.

From there, you should be able to set a macro for formatting the sheet

as necessary and can mail it with outlook using ADO. I found a pretty
good code for this under the group search string "VBA Code to send
email with attachments". Good luck

If you need help with the ADO or Excel query reply. I'll check.

 

[Kim]

Thanks so much Glen. Yes I will need help with the both the ADO for
Outlook and the Excel Query. And to answer your question yes they are
in separate fields.

Kim

 

[Glen]

That makes it great. Go ahead and set up a query to grab the data you
want for each owner using access first. Then, in excel, go to
Data>Import External Data>New Database Query. From there you can
select Access as your database type and then find your new query in the
drop down list it provides. Select all of the fields you want to
import to Excel. Click next through all of the pop up menus that
appear according to any filters you may want to apply and then have
Excel Query drop the data into your spreadsheet. It is just that easy.
If you want to create the code for it in Excel, you can have Excel
record the whole process and write the macro for you so you can call
that macro another time.

Are you going to generate these files from Access or Excel?

 

[Kim]

Cool I'm on my way to do that. Yes I will be generating the files in
Excel.

 

[Glen]

Alright. How familiar are you with VBA macros in excel? You can clean
them up a lot if you are recording them and I don't know if you need
help formatting the sheets. Also, it won't matter so much how you set
the Access query up, but the imported data to excel will enter the
cells in the order the data was selected so be mindful when selecting
the fields at >Get External Data>New Database Query. You can change
the order later, but it is cumbersome.

 

[Glen]

Kim,

I don't want to leave you hangin but I get off of work in 5 minutes so
i won;t be responding to any more posts until Monday morning. I am
sure if you need help there are great people at this site that will be
more than willing to assist you. They always help me. Otherwise, I
will check this post on Monday morning and help if I can. Have a great
weekend.