Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 20th, 2007, 07:22 PM
Authorized User
Points: 185, Level: 3
Points: 185, Level: 3 Points: 185, Level: 3 Points: 185, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 1 Time in 1 Post
Default Splitting queries on differents excel files

Hi, i need to upload this Excel file on msAccess Db. I know how I do that, but i need to export an Excel File under certain criterias.

For Example i need to run a query that pull out on 3 different excel files for this field criteria "RRC", "Techmex", "Azlan".

How can I export onto Excel in 3 differents Excel Files.
RRC.xls, Techmex.xls, Azlan.XLS
Any example would be very helpful for me.


Thank you.


Reply With Quote
  #2 (permalink)  
Old December 21st, 2007, 08:30 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure from your post exactly what it is you are trying to do? Are you trying to query 3 excel files, and take those results and save them to a fourth excel file?

If that is the case, the problem is that you can't export this kind of query. What I would do is to create a local Access table that has the fields you need from your query, then do this:

Delete all the data in the local Access table first every time you run this routine.
Query the excel files and append the results to the local Access table.
Then export the local Access table to Excel.

Did that help any?

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old December 28th, 2007, 02:00 PM
Authorized User
Points: 185, Level: 3
Points: 185, Level: 3 Points: 185, Level: 3 Points: 185, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 1 Time in 1 Post
Default

I would to explain better.
I hava a single XLS file that containt records which including countries, distribuitor, resallers and so.
E.G
ID Country Distribuitor QuoteNumber Date... so on
1 USA Technet 12 11-dec-07
2 USA Telmex 56 20-jan-07
3 CAN Ingram 33 11-aug-07
4 CAN Brightstar 44 23-jul-07
5 POLAND Techdata 56 15-dec-07
6 POLAND Fineinc 23 23-dec-07

There ihave to uplaod this file on a ms access for run the query that will delete records with 0 prices (easy) and change format date, etc
But the most important thing is when the filter have been applied I have to save on multiples workbooks on an specific folder for each country as distribuitors
Such as this example

USA-Technet.xls
USA-Telmex.xls
CAN-Ingram.xls
CAN-Brightsta.xls
POLAND-Techdata.xls
POLAND-Findinc.xls

And so for each country and dist.

I would appreciate any help to work with this.
Thanks
Reply With Quote
  #4 (permalink)  
Old December 29th, 2007, 05:47 AM
Registered User
 
Join Date: Oct 2006
Location: Leigh, Lancashire, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi gfranco

I am guessing from the way you have written your post that you are wanting to export out individual workbooks for each customer in their respective countries. If I am right and assuming this is going to be a one off exercise, I find the easiest way to do this is do as mmcdonnell suggested and bring the excel file into a local Access table. Using that table as the basis, create a single query, manually type in each country/supplier into the criteria section. Save the query and then go to file and export. This gives you the option of where to save and name the file, however if you are going to be doing this on a regular basis, then I would certainly look at automating this. I have a few databases that do this kind of operation and should you require it I can send you some VBA code to help.

Hope this is useful

Martin

Tell them they will hear,
Show them they will see,
Let them experience and they will learn
Reply With Quote
  #5 (permalink)  
Old December 29th, 2007, 01:12 PM
Authorized User
Points: 185, Level: 3
Points: 185, Level: 3 Points: 185, Level: 3 Points: 185, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thank you Martin, I would really appreciate any help that you might have. I have to do this automatically because I will have more than 50 files on excel.
My email is giovannyf@hotmail.com if you want me to send some code or let me know where can I download.
Thanks.

Quote:
quote:Originally posted by martinrhague
 Hi gfranco

I am guessing from the way you have written your post that you are wanting to export out individual workbooks for each customer in their respective countries. If I am right and assuming this is going to be a one off exercise, I find the easiest way to do this is do as mmcdonnell suggested and bring the excel file into a local Access table. Using that table as the basis, create a single query, manually type in each country/supplier into the criteria section. Save the query and then go to file and export. This gives you the option of where to save and name the file, however if you are going to be doing this on a regular basis, then I would certainly look at automating this. I have a few databases that do this kind of operation and should you require it I can send you some VBA code to help.

Hope this is useful

Martin

Tell them they will hear,
Show them they will see,
Let them experience and they will learn
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Report from two tables in two differents databases cphspain Crystal Reports 1 November 21st, 2006 05:13 PM
Queries export to excel ymeyaw Access VBA 0 April 21st, 2006 01:17 AM
Splitting Excel file into Multiple Worksheets tvschalapathirao Dreamweaver (all versions) 3 May 16th, 2005 02:16 AM
Access and Excel Queries in VB Mihai B Access VBA 1 January 24th, 2005 08:40 AM
generating xml files from sql queries Thebravehearth XML 0 November 1st, 2004 11:41 PM



All times are GMT -4. The time now is 04:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.