Export Access Query to Different Sheets in Same Workbook

[koolwaters]

Hi.

I need to export the records of an Access query into different sheets of a workbook.

Let's say I have a query that pulls all of the transactions for a particular period. I would like to export this data to Excel but have a new worksheet for the different locations where the transactions were done and name the sheets based on the location.

So if for example my locations are North, South, East and West, I would like to export all of the West transactions to a sheet called West, all of the South to a sheet called South, etc.

I have been searching for a while but everything that I am seeing relates to exporting multiple queries. I do not want to go that route as this process has to be replicated for a number of workbooks that needs to be grouped by a particular field and I know that I will have to create at least 30 queries to accomplish this.

Thanks for any assistance.

 

[koolwaters]

Hi,

I have created a copy of my form and added a button called cmdTestImport where I can test your code and I have a few questions.

You set the OriginalWS to sheet 1 but sheet 1 of which workbook?
Where do I add the code that exports the query? I assume that is what is supposed to be OriginalWS?

 

[Coding4Fun]

Hi Norie,

I only have one query called qryIssuesBy. The criteria for the EmployeeCategory of this query is "Hardware" or "Software", which limits the results of the query to show all issues where the Employee Category is either Hardware or Software. I then need to use this query to create Excel workbooks to show:
Issues by Employee Category (the sheets in this workbook will be Hardware and Software)
Issues by Employee Name (there will be a sheet in this workbook for each Employee Name)
Issues by Issue Type (there will be a sheet in this workbook for each Issue Type)
Issues by Status (there will be a sheet in this workbook for each Status)
Issues by Resolution Category (there will be a sheet in this workbook for each Resolution Category)
Issues by Customer Category (there will be a sheet in this workbook for each Customer Category)

Just like you can group a report and have a different group for each employee category, I want an Excel workbook where my groups are the worksheets.

When I referred to other queries, I mean that I have to replicate these same steps for other queries to show Resolution Category by Month, Resolution Category by Customer, etc.

I hope this provides more clarity.

Ok so you would need to manually export the file from Access (for now), run your query and export it to excel. This export would be the Sheet1 in your question it does not need to be named Sheet 1 as it uses the sheets index rather than the actual name.

Once you have the export file insert this code into a macro and run it. This should take your export file and create 2 new sheets where it will separate the values from your export file Hardware to the Hardware sheet and Software to the software sheet.

Based on my understanding this would take care of you Issues by Employee Category.

Your overall request is not a simple task so want to make sure I am on the right track before investing too much time.

Hope this helps, let me know.

 

[koolwaters]

Hi Coding4Fun,

Just letting you know that I am out on sick leave and am due to return to the Office on Thursday.

I will try the code then.

 

[koolwaters]

Hi Coding4Fun,

I was away for a bit and resumed this project on Monday.

I have found a simpler solution, which is to export the day to Excel and then create the sheets using VBA in Excel.

Thanks for your help.

 

[Coding4Fun]

I am glad you found a solution that works for you