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.

 

[Coding4Fun]

What about the idea to run the query and then run a VBA Script to parse the data to new sheets accordingly.

If you provide the following I can help

1. number of columns that your query will use
2. Criteria to use for sorting or what field it will be in

I could loop through each row and check

does the value in row x column y = WEST - move to the West Sheet
does the value in row x column y = EAST - move to the East Sheet etc.

I am sure there is a what to do this directly from Access as well but I am not sure how to approach that angle

 

[Coding4Fun]

Another way would be to create a list with your criteria then you could loop through and check the values in the query export against your list of filter values.

Still would need to know where you would store the list of filter values and would need to know what column the filter value is found in the query along with how many columns exist

 

[koolwaters]

To provide a bit more detail, I have tables in the database that are used as lookup tables. There are nine such tables that I will need to group the various queries by.

Query 1- I need to be able to create an Excel workbook to show all issues that were referred to a particular team member. There is a lookup table that stores all members of staff but not all work on issues. I have a field in this table that is used to determine the employee category. There are four categories of employees, but I only need two out of the four for this particular query - Hardware and Software.

There are twenty fields in this query, including the employee category field. So essentially, for the first query, I need a new Excel workbook created and the sheet names will be Hardware and Software. The employee category is in column six of the query.

Query 2- Using the exact same query above, I need a new Excel workbook with a new sheet for each employee. I need the employee's name as the sheet name. The employee's name is also in the query. The employee name is in column five of the query.

I already have criteria in the query to show only rows where the employee category is either hardware or software.

There are seven other queries that I need to perform this on but I should be able to follow once I understand how to do it with one.

Thanks!

 

[Norie]

It sounds to me as though you do have multiple queries though the are basically the same with only the criteria changing.

If that's right then you should be able to use the code you've already found.

Query1 - sounds like 2 separate queries, one where the criteria is 'Hardware' and one where the criteria is 'Software'.

Query2 - sounds like multiple queries, one for each employee, the criteria being the employee name.

For Query2 I would suggest creating a query to return all the unique employee names and then loop through that table taking each employee name as the criteria for the main query.

 

[koolwaters]

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.

 

[Norie]

Sounds to me like you have a bunch of different queries based on the qryIssuesBy.

These would be the first 2 queries.

SELECT * FROM qryIssuesBy WHERE EmployeeCategory = 'Hardware'

SELECT * FROM qryIssuesBy WHERE EmployeeCategory = 'Software'

You would then have a query like this for each employee.

SELECT * FROM qryIssuesBy WHERE [Employee Name] = employeename

There would be similar querys for each Issue Type, each Status etc.

 

[koolwaters]

Hi Norie,

That is what I am trying to avoid, having a separate query for each item. If I need to add or delete a column in the future, it means that I have all of these queries to change. There are thirty-five employees alone who deals with the hardware and software issues. If I create queries for the employee categories, issue types, status, resolution categories and customer categories, I am looking at eighty-one queries, which I believe will be an inefficient fix.

I was trying to find a better method using VBA with one query.

Each of the workbooks I have to create will have the same fields. The only difference will be the sheet names in the workbooks. Essentially, it is the same data grouped in different ways.

 

[Norie]

You wouldn't actually create these queries in the database, you would run them in the code and export the results to Excel.

The first 2 queries would be pretty straightforward but for the rest you would need other queries to get unique lists Employee Names, Issues etc.

 

[Coding4Fun]

This is a start which will resolve problem 1
Create a new workbook named Hardware and Software and parse the query results into these 2 new workbooks.

The next step would be to take these new workbooks and break them into new tabs by employee name.

Let me know if I am on the right track and I will complete the 2nd half of this.

Sub Test()Dim OriginalWS As Worksheet
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet




Dim FileName1 As String
Dim FileName2 As String
Dim Filepath As String


Set OriginalWS = Sheets(1)


Sheets.Add.Name = "Hardware"
Sheets.Add.Name = "Software"


Set Sht1 = Sheets("Hardware")
Set Sht2 = Sheets("Software")


OriginalWS.Activate


LR = Cells(Rows.Count, "A").End(xlUp).Row


For i = 2 To LR


If Range("F" & i) = "Hardware" Then


Range("A" & i & ":T" & i).Copy
Sht1.Activate
LR2 = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & LR2 + 1).PasteSpecial xlPasteValuesAndNumberFormats
OriginalWS.Activate


ElseIf Range("F" & i) = "Software" Then


Range("A" & i & ":T" & i).Copy
Sht2.Activate
LR3 = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & LR3 + 1).PasteSpecial xlPasteValuesAndNumberFormats
OriginalWS.Activate


End If


Next i


FileName1 = "Software.xlsx"
FileName2 = "Hardware2.xlsx"


Filepath = "C:\Users\UserName\Desktop\"
Application.DisplayAlerts = False


Sht1.Copy
ActiveWorkbook.SaveAs (Filepath & FileName1)


Sht2.Copy
ActiveWorkbook.SaveAs (Filepath & FileName2)


Application.DisplayAlerts = True


End Sub