Access-Excel

[endri81]

Which is the best practice for exporting report data from Access to Excel if this procedure is possible/

 

[boblarson]

Depends, but I find it easiest to use the same underlying query along with this code.

 

[endri81]

Not quite sure how to use it because I am a beginner.
Simply I need a button to make possible the export of data from my table1 to excel sheet

 

[boblarson]

Not quite sure how to use it because I am a beginner.
Simply I need a button to make possible the export of data from my table1 to excel sheet

you just copy the code from my website into a STANDARD MODULE (not a form, report or class module) and then save the module with a name like basExport.

Then you simply, from a command button, can call the function:

SendTQ2Excel "Table1"

And that's it.

 

[endri81]

Thx it works for me when I want to export a table to excel.Second task is to convert data from my reports to Excel.Is this possible?

 

[boblarson]

Thx it works for me when I want to export a table to excel.Second task is to convert data from my reports to Excel.Is this possible?

What query do you use for your report? Send the query to the function instead of a table.

 

[endri81]

Thx the function SendTQ2Excel works perfectly.
Anyway I got a new issue.
My reports are of form field1,field2,field3...total.
After export in Excel field total is the first and after that field1....
How to repair this problem and make the reports in Excel like the ones in Access?
Regards

 

[boblarson]

What does the query look like that you are exporting? (what is the SQL?)

 

[endri81]

TRANSFORM Count(DL_Kryesore.DL) AS CountOfDL
SELECT Rrethet.Rrethi, Count(Rrethet.Rrethi) AS Total
FROM DL_Kryesore INNER JOIN Rrethet ON DL_Kryesore.GruaRrethiID = Rrethet.ID
GROUP BY Rrethet.Rrethi
PIVOT DL_Kryesore.DL;

This is only one because this happens to all my reports.
Thank you in advance

 

[Lightwave]

As far as I know I only use up to Access 2003.

The export command in VBA doesn't export formatting just the raw data.

If you absolutely must recreate your access reports in excel then you will have to set up VBA in Excel to re-format the information after import.

This is likely to be a labourious and difficult thing to do and you will continually come across quite difficult problems in formatting in particular expandable fields.

The report writer in Access and databases in general have some really fundamental differences in the way it formats information.

If the users need information displayed in a particular way the best things to do are usually.
Give them a raw dump and let them play about with iit themselves - (Good for one off tasks)
Design a new specific report that tackles their needs - (takes longer but good if they ask the same questions all the time.)

 

[boblarson]

With PIVOTs, exports will have the totals before the other columns. I don't believe there's a way around this except to use Excel Automation Code which Lightwave has also suggested that it would need to be done.

And it doesn't have to be all that hard but it does require some work.

This is likely to be a labourious and difficult thing to do and you will continually come across quite difficult problems in formatting in particular expandable fields.

I've not found that to be the case but it really does depend on what the starting point is and the desired output. Depending on the output wanted, it can be rather easy or a bit difficult.