Export data from access to excel / format and sorted

[Leo_Polla_Psemata]

Solved - Exporting Data from Access to Excel in format

At first, i was in need to export from access a formatted excel report and i reached my goal. The thread is here. https://www.access-programmers.co.uk/forums/showthread.php?t=308329 In this address i found the code which i fixed to my needs...

www.access-programmers.co.uk

Hi
The above thread, in which i mastered the way of exporting formatted excel from access, has help a lot, i complete fast complicated reports.
Now i have a new report that i made last week, i cannot find the way of sorting the report based on a "field" which its data is displayed into colon B.
I do it manually but if there was a line of code that i could add in the vba, that would be great.

 

[The_Doc_Man]

The problem with sorting reports (as opposed to queries) is that the report wants impose its own sorting order because ... well, because it wants to. (Nobody knows why Microsoft does it this way.) So there is a "Sort and Group" option in the report building wizard and also in the "raw report builder" grid. Use THAT method to impose your sort order.

 

[Leo_Polla_Psemata]

The problem with sorting reports (as opposed to queries) is that the report wants impose its own sorting order because ... well, because it wants to. (Nobody knows why Microsoft does it this way.) So there is a "Sort and Group" option in the report building wizard and also in the "raw report builder" grid. Use THAT method to impose your sort order.

Hi, apologies for my bad English and as a casual access user, i am not using terminology very precisely.
When i say "reports", i don't mean the access reports. General, business reports which i send in excel.
Before, i was creating each report from scratch. Now i use access queries and export data in excel.
However, while we can sort the access query, when exported in excel, it doesn't keep same sorting.

 

[Minty]

How are you doing the export to excel?

Generally, the data will be exported and sorted in the underlying queries sort order?

 

[Leo_Polla_Psemata]

How are you doing the export to excel?

Generally, the data will be exported and sorted in the underlying queries sort order?

Hi, i use a code as per first post in this thread, a code i found last year and i adjust it.
No, the data is not sorted us per query sort order.
I don't know what is access doing.
If i start entering records, (say 10 records from id 50 to id 60) then stop, close form and continue after some time,
from id 61 to 70, then if i retrieve the report, the order will be 61 to 70 on the top of the list, after 70 i will see 1,2,3->60 and so on.

So, i don't know if there is any piece of code that could make dynamical sorting in the excel.

 

[Minty]

In the start of that code is a SQL query with an ORDER BY clause at the end.

SQL = "SELECT PartNo, PartName, Price, SalePrice, " & _
"(Price - SalePrice) / Price AS Discount " & _
"FROM Parts " & _
"ORDER BY PartNo "

Adjust that to suit whatever sort order you need.

 

[Leo_Polla_Psemata]

Hi , suppose we need a second excelsheet on the same work book (another affiliate report)
I understand that after the "end with" at the bottom, i should start a new "With xlsheet .... End with"
and update the lines in between.
However not sure what should i type after the green remark

 

[theDBguy]

View attachment 95819
Hi , suppose we need a second excelsheet on the same work book (another affiliate report)
I understand that after the "end with" at the bottom, i should start a new "With xlsheet .... End with"
and update the lines in between.
However not sure what should i type after the green remark

Maybe you'll need to use the Add method.

Sheets.Add method (Excel) | Microsoft Docs

 

[The_Doc_Man]

From the code clip in post #7, we cannot see the origin of "rsl" that is the right-hand side of that thing that is loading the cells in the loop, and that is used in the .CopyFromRecordset action shortly thereafter. If "rsl" is opened to a table, the behavior you describe would fit perfectly.

Oh, and no problem that you meant "business reports" rather than "Access reports" in the earlier discussion. That word IS ambiguous when dealing with Access. Even an English speaker could easily make that mistake.

 

[Pat Hartman]

How are you creating rs1? If you want it to be in a specific order, you MUST use a query and you MUST include an order by clause.

 

[Leo_Polla_Psemata]

How are you creating rs1? If you want it to be in a specific order, you MUST use a query and you MUST include an order by clause.

Hi, I create a query which convert to SQL view, then paste on VBA page. Now i add the below on SQL and problem is solved.

"ORDER BY PartNo "

 

[Pat Hartman]

Glad to help

Tables and queries are unordered sets. Think of marbles in a bag. They are retrieved by the database engine using algorithms that optimize the fetch process so you might see a,b,c one day but b,a,c the next/ This almost never happens with Access because Access is much less sophisticated in it's storage and retrieval than larger RDBMS' such as SQL Server. The other thing that tends to fool people working with Access is that whenever you compact a db, Access sorts the table data and writes it back in pk order.. So when you open the table, you almost always see the first bunch of records in PK order and so it fools you.

But, if you care to prove this to yourself you need a table with a few thousand large records. Make sure you don't have an order by specified for the table otherwise Access will always sort the table when you open it. Then update a few early record. Make sure to change the text values to make them longer. If a text field has 10 characters, replace it with 20. Close the table and reopen it. The records you changed should have disappeared. You should find them at the end of the recordset.

 

[Leo_Polla_Psemata]

Maybe you'll need to use the Add method.

Sheets.Add method (Excel) | Microsoft Docs

Hi DB guy
The goal is how to export one excel with two pages by using just one command.
So far, I can export several business reports from Access to excell with he perfect format,
then, manually i add one or more sheets to main excel document and I am done.

Where should i use this add method ? Within my code or outside when two excel documents are already saved in a file ?

The structure of code is as follow in parts

1
Pert one (Dim section)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
2
Part two Sql section
3
Part three Sea rs1 and if section
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Set xlApp = Excel.Application
4
Part four With section
With xlSheet
.Name = whatever
5
Part five Buil the table section
Set workseet/column/rows width etc, format etc //Build the table

6
Part six Do while section
Do While Not rs2.EOF

7
part seven Loop section

8
Part eight end with section
End with

9
Part nine
Sub exit
suberror

end sub

 

[Pat Hartman]

As long as you added the Order By to the query, there is no need to change the code. theDBguy was suggesting a different method to use to add the rows to the spreadsheet.