473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to export filtered data from access table to Microsoft excel

elenaA
42
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

30 19064
PhilOfWalton
1,430 Expert 1GB
How are you creating the filter?
How are you exporting the query?

Phil
Mar 5 '17 #2
elenaA
42
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
1,430 Expert 1GB
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
42
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
1,430 Expert 1GB
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
42
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
42
here are screen shots
Attached Images
File Type: jpg Zajeta slika11111111111111.jpg (22.2 KB, 584 views)
File Type: jpg Zajeta slika222222222222.JPG (18.3 KB, 379 views)
Mar 5 '17 #8
PhilOfWalton
1,430 Expert 1GB
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
42
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, 423 views)
Mar 5 '17 #10
PhilOfWalton
1,430 Expert 1GB
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
42
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
42
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
42
i try it but is the same. it exports me all the results.
Mar 5 '17 #14
PhilOfWalton
1,430 Expert 1GB
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
42
ok, thank you

I will try
Mar 6 '17 #16
elenaA
42
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
42
I still get the same export of all the results.

i dont know what could be wrong
Mar 6 '17 #18
PhilOfWalton
1,430 Expert 1GB
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
42
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
1,430 Expert 1GB
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
42
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
1,430 Expert 1GB
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
42
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
42
i am using ms access 2010
Mar 7 '17 #25
PhilOfWalton
1,430 Expert 1GB
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
42
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
1,430 Expert 1GB
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
42
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
1,430 Expert 1GB
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
42
yes i know. i will open new topic

thank you for all
Mar 9 '17 #31

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

Similar topics

2
by: martinwaller | last post by:
If anyone is interested in getting DB2 data directly into Microsoft Excel then take a look at www.synola.com it does just what you'd expect...
4
by: Martin | last post by:
There is an Access table on the network. 15 users who do not have Access are connected to the network. Is there a way for each user to be able to enter one or more rows containing 3 or 4 columns to...
7
by: Keon | last post by:
Hoi, I'm using a database with alot of records in 1 table (more than 3000). If i want to export this table to excel i only get it till record 2385. Do someone know how i can solve this...
3
by: bwhite | last post by:
I have a temp table with one row of data that I need to export into Excel. I created the export to create the xls file as follows ... Dim FileName FileName = !! DoCmd.SetWarnings False...
5
by: daicakien | last post by:
hi everybody, im developing access db. my user want to export some column of the table( in access) to Excel( he may choose from list box of form). do you have any idea about it? thanks
2
by: thangsan | last post by:
Hi Friends, In my project i need to Export the Data from Sql server to Excel Sheet. It should be auto schedulder certain intervals(months). i want to store the Excel sheet in .CSV ...
3
by: vbdude | last post by:
Hi, I want help with my problem. How can i export datagrid data that comes from a ms sql server 2005 and will be going to ms excel.. Vbdude... P.S. Please Help me!!!
1
by: anilknayak | last post by:
i have data in ArrayList and i want to export those data to pdf and excel format on click respectivily . and after those data has writen in pdf or excel then a save window panel will open to choose...
3
by: sarah2855 | last post by:
Hello All, I'm looking for the vb code that export an access table to specific worksheet in Excel. I tried to search see if this question was answered before here, but didn't find anything that...
2
by: Johnny Wong | last post by:
Hi, I am new to visual basic. In my vb system, there is a dynamic datagrid to represent different reocrds in our database. Today I am gonna export the data to Excel file. I found most examples code...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.