By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

How to export filtered data from access table to Microsoft excel

elenaA
P: 38
Hello

In Ms Access I have one big table that collect all data. Now my user want to filter selected data from query and export only filtered data to Microsoft Excel. I try to do that but for now I get exported all the data. I only want to export to excel only the data that user filtered.

Somebody can suggest me what I have to do?

Thanks in advance
Mar 4 '17 #1

✓ answered by PhilOfWalton

I think, instead of using the Macro to export to excel, (I hate Macros), try this VBA

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, Me.RecordSource, "TestExcel"
  2.  
This should export the filtered data to a file called "TestExcel.xlxs"

Phil

Share this Question
Share on Google+
30 Replies


PhilOfWalton
Expert 100+
P: 1,430
How are you creating the filter?
How are you exporting the query?

Phil
Mar 5 '17 #2

elenaA
P: 38
Hello

I use a filter that is automaticly on in the query. you just click and you can filter the results.

For now for exporting the query with results I use a botton with macro.

maybe you know how can i make an export that will export only the data I filtered? for now is exporting all data moren than 1000 results.

thank you in advance
Mar 5 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Sorry, that does't help at all.

Please let me have the SQL for the query.

What does the Macro do?

Phil
Mar 5 '17 #4

elenaA
P: 38
ok

this is my query

SELECT tblUNIV.DatumAnalize, tblUNIV.DatumVnosa, tblUNIV.ImeUčinkovine, tblUNIV.ŠifraUčinkovine, tblUNIV.VrstaPumpice, tblUNIV.Višina, tblUNIV.KontrolnaŠtevilka, tblUNIV.ElogisOznaka, tblUNIV.Pršilka, tblUNIV.Aktuacija, tblUNIV.[Dv 10 Ám], tblUNIV.[Dv 50 Ám], tblUNIV.[Dv 90 Ám], tblUNIV.Span, tblUNIV.[pod 10 μm], tblUNIV.[DMax mm], tblUNIV.[DMin mm], tblUNIV.Ovality, tblUNIV.[Area mm2], tblUNIV.[Api v susp Dv 10 Ám], tblUNIV.[Api v susp Dv 50 Ám], tblUNIV.[Api v susp Dv 90 Ám], tblUNIV.[Premer nozzla Ám], tblUNIV.[Viskoznost mPas], tblUNIV.[PlumeAngle deg], tblUNIV.[PlumeWidth mm], tblUNIV.[Shot weight mg]
FROM tblUNIV, tblUNIV AS tblUNIV_1
GROUP BY tblUNIV.DatumAnalize, tblUNIV.DatumVnosa, tblUNIV.ImeUčinkovine, tblUNIV.ŠifraUčinkovine, tblUNIV.VrstaPumpice, tblUNIV.Višina, tblUNIV.KontrolnaŠtevilka, tblUNIV.ElogisOznaka, tblUNIV.Pršilka, tblUNIV.Aktuacija, tblUNIV.[Dv 10 Ám], tblUNIV.[Dv 50 Ám], tblUNIV.[Dv 90 Ám], tblUNIV.Span, tblUNIV.[pod 10 μm], tblUNIV.[DMax mm], tblUNIV.[DMin mm], tblUNIV.Ovality, tblUNIV.[Area mm2], tblUNIV.[Api v susp Dv 10 Ám], tblUNIV.[Api v susp Dv 50 Ám], tblUNIV.[Api v susp Dv 90 Ám], tblUNIV.[Premer nozzla Ám], tblUNIV.[Viskoznost mPas], tblUNIV.[PlumeAngle deg], tblUNIV.[PlumeWidth mm], tblUNIV.[Shot weight mg]
ORDER BY tblUNIV.DatumVnosa DESC , tblUNIV.KontrolnaŠtevilka, tblUNIV.Pršilka;



well macro export the entire query in excel file. this is more than 1000 results.

i want that when i filter the results lets say 15 results i want that when i export that i have only 15 reults and no 1000.

thank you
Mar 5 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
There is no filter in your query, so again I ask how are you applying the filter.

Again I ask for the details of the Macro.

Without this information, I can not offer any further help

Phil
Mar 5 '17 #6

elenaA
P: 38
hello

i have a search form. from search form I filter the field i want.

i have a button with macro. this button export the results in ms excel.

i want to send you picture of my form and macro. maybe this will help
Mar 5 '17 #7

elenaA
P: 38
here are screen shots
Attached Images
File Type: jpg Zajeta slika11111111111111.jpg (22.2 KB, 254 views)
File Type: jpg Zajeta slika222222222222.JPG (18.3 KB, 168 views)
Mar 5 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
OK, we are getting somewhere. Sorry, I don't speak Croatian or Slovenian, not quite sure where you come from, but have sailed along the Croation coast many times.

Your macro is exporting the tblUNIV instead of the filtered query, which is why you are getting all the records, so change tblUNIV to the name of your query.

Now I still need to know what happens when you chose one of the Combo boxes on your Find Results form. How exactly is the filter applied.

Phil
Mar 5 '17 #9

elenaA
P: 38
yes i am from slovenia small country and croatia is our neighbur. both coutries are very nice.

ok

i change the tbluUNIV to my query and is the same. the query export me all the results.

my combo box filter the fields that have the same name that are in my combo box, for me is very important the field name kontrolna Ütevilka. this is like batch number. i want to filtered results that have specific batch number.

now my combo box filter correct but then my macro export all the results and not only 15.


i post you a screen shot
thank you
Attached Images
File Type: jpg Zajeta slika3333333.jpg (86.3 KB, 152 views)
Mar 5 '17 #10

PhilOfWalton
Expert 100+
P: 1,430
I appreciate that there is a problem with languages, but you are still not telling me what happens when choose "test1" from your combo box.
You must either run a macro or some VBA to apply the filter.

What exactly happens?

What exactly is the SQL for your filtered form?

Phil
Mar 5 '17 #11

elenaA
P: 38
my combo box work with vba code. i have vba codees for every combo box.

here is the code that is for my batch number combo box

Private Sub cboKontrolnaŠtevilka_AfterUpdate()
Dim myKontrolnaŠtevilka As String
myKontrolnaŠtevilka = "Select * from tblUNIV where ([KontrolnaŠtevilka] = '" & Me.cboKontrolnaŠtevilka & "')"
Me.test2.Form.RecordSource = myKontrolnaŠtevilka
Me.test2.Form.Requery
End Sub

i hope this help
Mar 5 '17 #12

elenaA
P: 38
ahaa maybe i know what should be the problem

do i have to change in my vba code also the tabel name to name of my query that is in the macro?
Mar 5 '17 #13

elenaA
P: 38
i try it but is the same. it exports me all the results.
Mar 5 '17 #14

PhilOfWalton
Expert 100+
P: 1,430
I think, instead of using the Macro to export to excel, (I hate Macros), try this VBA

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, Me.RecordSource, "TestExcel"
  2.  
This should export the filtered data to a file called "TestExcel.xlxs"

Phil
Mar 6 '17 #15

elenaA
P: 38
ok, thank you

I will try
Mar 6 '17 #16

elenaA
P: 38
Hello

I try that and is not working. it says me that some field is missing?

maybe you know what should be wrong?
Mar 6 '17 #17

elenaA
P: 38
I still get the same export of all the results.

i dont know what could be wrong
Mar 6 '17 #18

PhilOfWalton
Expert 100+
P: 1,430
Please confirm the form is giving the correct filtered results.

Please post your full code for what happens when you click the export button

Phil
Mar 6 '17 #19

elenaA
P: 38
when I search the batch number from the combo box the form gives me the correct data in my subform. the subform record source is from the the query.

now i make a button which should export me the filtered results from the subform to excel.

for now i get the export of all the results and not that I want.

here is my code for exporting the results

Private Sub Ukaz641_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "expexcel", "C:\Users\ELENA\ExportExcel", True

End Sub

i dont know what is worong
Mar 7 '17 #20

PhilOfWalton
Expert 100+
P: 1,430
I feel we are both wasting our time.

Have you tried the code I gave you earlier?

For the final time I will give it to you again
Expand|Select|Wrap|Line Numbers
  1. Private Sub Ukaz641_Click()
  2.  
  3. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, Me.RecordSource, "C:\Users\ELENA\ExportExcel", True
  4.  
  5. End Sub
  6.  
The essential bit is Me.RecordSource

Phil
Mar 7 '17 #21

elenaA
P: 38
hello

i try it. is not working. it says me missing table name.

where to put the table name?

what exatly mean recordsource?

i am not wasting my time. i want to learn. i learn a lot and i want to figure where is my problem.

but thank you for all you help. if you have other things to do i will undersand.

thank you for all
Mar 7 '17 #22

PhilOfWalton
Expert 100+
P: 1,430
Me.Recordsource is the another way of referring to the table or query that is the source of your form.

"Me" is a way of referring to the form or report you are working with.

When you type the word "Me" with a dot after it, you should get a huge list of things that can follow it.

I am assuming that you copied & pasted the code I gave you exactly into your module.

The only other thing I can think of is can you let me know which version of Office (Access) you are using.

Phil
Mar 7 '17 #23

elenaA
P: 38
hello

yes i did everytink you say me. i get the export. but again i get all the results and not the specific i want.

i think i have to make some criteria or something in query but i dont know where . i have search form for that so i dont know...

thank you
Mar 7 '17 #24

elenaA
P: 38
i am using ms access 2010
Mar 7 '17 #25

PhilOfWalton
Expert 100+
P: 1,430
I think I know the problem.

I think your filtered results are on a subform called test2 so try this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, Me.test2.Form.RecordSource, "C:\Users\ELENA\ExportExcel", True
  2.  
  3. End Sub
  4.  
Phil
Mar 7 '17 #26

elenaA
P: 38
yes

the results are in the filtered form. that was i want.

i still have problems. when i export i dont get the filtered results.

i will still trying and searching on the net

thank you
Mar 8 '17 #27

PhilOfWalton
Expert 100+
P: 1,430
The only thing I can suggest is zipping your database (after removing any sensitive information) and I will have a look.

You can send it as a private message.

Phil
Mar 8 '17 #28

elenaA
P: 38
i make now some other thing.

i make again search form. but now i made it in the way to use split form. my search form is working. and now i choose the way to export the results that is offering ms access. in the external tab is icon for exporting in excel. in that way is working.

now i have another problem. for my users i want to lock everyting. i dont want that they can use the ms acccess way to export.

now i want to make some button that will be like the ms access excel export.

do you know how to do that?
where i can find the code that they use ?

thank you
Mar 8 '17 #29

PhilOfWalton
Expert 100+
P: 1,430
I think that you should post this as a new topic so that others can see it.

Also your question is far from clear so may I suggest you try to ask it in a different way

Phil
Mar 9 '17 #30

elenaA
P: 38
yes i know. i will open new topic

thank you for all
Mar 9 '17 #31

Post your reply

Sign in to post your reply or Sign up for a free account.