Query from Multiple Workbooks

Hello,

I have 12 files in a directory with a sheet name Sales containing
Sales data for each month having more than half a million rows in each
workbook sheets

The field names are all the same in all the files like date, item,
itemgroup, branch, unit, salesman, amount etc

I am looking for a way to get a query from all these workbook and get
a summarised report based on criteria like sales by salesman, by
month, by product, by product group, by date, by branch etc.

Is this will be possible through code?. Itry to use MS query but not
successful

Using Excel 2007 and Win XP

Thanks in advance for any help

Abdul
0
Abdul
12/30/2009 12:46:13 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
838 Views

Similar Articles

[PageSpeed] 18

That amount of data should really be in Access or SQL. Since you have Excel I 
will assume you have Access, create a new database and then go to File->Get 
external data-> Link tables and select all of the Excel sheets. This will 
make a table for each Excel sheet. Tie the sheets together through 
relationships (product number or salesman etc) and query it that way. With 
the tables linked, you wont have to update the Access DB if the Excel sheets 
change.


-- 
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what 
is helpful. 


"Abdul" wrote:

> Hello,
> 
> I have 12 files in a directory with a sheet name Sales containing
> Sales data for each month having more than half a million rows in each
> workbook sheets
> 
> The field names are all the same in all the files like date, item,
> itemgroup, branch, unit, salesman, amount etc
> 
> I am looking for a way to get a query from all these workbook and get
> a summarised report based on criteria like sales by salesman, by
> month, by product, by product group, by date, by branch etc.
> 
> Is this will be possible through code?. Itry to use MS query but not
> successful
> 
> Using Excel 2007 and Win XP
> 
> Thanks in advance for any help
> 
> Abdul
> .
> 
0
Utf
12/30/2009 1:39:01 PM
My first recommendation would be to import the large files into Access
which is much better at handling large files.  You can add a key for
month and have all the data into one file.  Then you can query the
Access database from excel and get the results you are looking for.  Or
perform you queries in Access and then export the query results to Excel
worksheet.

If you don't want to do this then yo can have a macro make a summary
worksheet by opening up each of the files.  You would probaly want to
setup a userform to select the options you want and then import the
data.  there are many way s of importing the data, a query is only one
method.

I don't want to recommend any code yet until you decide the methods you
want to use.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165874

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
joel
12/30/2009 1:43:50 PM
If you know how to Select Queries in Access, this will be easy.
My suggestion is to import each file into 1 Access database and create a 
select query.  Create a relationship using using a Junction table and then do 
a select query.
There is an Access/Queries group with an excellent collection of MVP's.

"Abdul" wrote:

> Hello,
> 
> I have 12 files in a directory with a sheet name Sales containing
> Sales data for each month having more than half a million rows in each
> workbook sheets
> 
> The field names are all the same in all the files like date, item,
> itemgroup, branch, unit, salesman, amount etc
> 
> I am looking for a way to get a query from all these workbook and get
> a summarised report based on criteria like sales by salesman, by
> month, by product, by product group, by date, by branch etc.
> 
> Is this will be possible through code?. Itry to use MS query but not
> successful
> 
> Using Excel 2007 and Win XP
> 
> Thanks in advance for any help
> 
> Abdul
> .
> 
0
Utf
12/30/2009 2:06:01 PM
Thanks for all the suggestions. Unfortunately I dont have access
installed in my notebook. I am looking for a workareound from within
excel or a way to create a database and tables without having access
installed.

Thanks
0
Abdul
1/2/2010 7:02:35 AM
I would think the Access libraries are still installed in you Notebook.
Can you try two things for me

1) VBA Menu - Tools - References - Microsoft Access 11.0 object
Library

Check if the Access library is one of the references

2) run this simple macro

Sub test()
Set MyApp = CreateObject("Access.application")
MyApp.Visible = True
End Sub


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165874

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
joel
1/2/2010 1:02:02 PM
For sure Access is not istalled and the object library also not seen

Thanks



On Jan 2, 4:02=A0pm, joel <joel.445...@thecodecage.com> wrote:
> I would think the Access libraries are still installed in you Notebook.
> Can you try two things for me
>
> 1) VBA Menu - Tools - References - Microsoft Access 11.0 object
> Library
>
> Check if the Access library is one of the references
>
> 2) run this simple macro
>
> Sub test()
> Set MyApp =3D CreateObject("Access.application")
> MyApp.Visible =3D True
> End Sub
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/showthread.php?t=3D165=
874
>
> [url=3D&quot;http://www.thecodecage.com"]Microsoft Office Help[/url]

0
Abdul
1/6/2010 6:50:29 AM
Reply:

Similar Artilces:

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Change links to hyperlinks in a workbook?
I have 5 Income Statement sheets in a workbook. The first sheet is an Income Statement summing four departments (listed by columns). Column B has Dept 1 data, column C has Dept 2 data, etc. ALL data on this sheet is linked (referenced) to the other four sheets which are the individual departments. I know I can set hyperlinks one at a time, but is there a way for me to have each of those links (every cell containing the data from the other four sheets) become hyperlinks? If I have to add another sheet that mirrors the summary sheet but has hyperlinks, that would be fine. I am just ...

my workbook is too big
i have a workbook with several sheets of data on it, not much like 15 columns and 20 rows full of text, columns are a little wider than normal, but not one over twice its original size. my main data sheet is a table. i copied the format of the whole sheet, along with the 1st row of the sheet to 12 other sheets, so i have 13 identical sheets, but only one has data on it right now. when i saved it, the workbook was like almost 400k. no formulas are copied any further down than the 20th row of the main sheet. i have many macros running on the one main sheet but thats all, when i a...

Multiple PST Files #2
I am running Outlook 2003 and would like to have Outlook use two different .pst files (one for my work stuff and one for home) on the same computer. Is it possible to do this? If so, can I can I configure one of the pst files to get e-mail from one account, while the other pst file is configured to get mail from the other account? Thanks! -D Yes, you can create multiple PSTs. I keep mine stored in the same file location but with different names. Depending on which version of Outlook you run, you can creat a new pst from file/new/Outlook Data File (I run Outlook 2003 but had multiple P...

catching enter key for multiple editboxes
I know that to catch the enter key I need to subclass the editbox control, but I was wondering if there is a way to do this for multiple editboxes other than to subclass each control? I suppose I could do one and then create the others dynamically, but can it be done during design as well? Roger ...

Sending a workbook as a mail attachment
Hey Folks, Is there an easy way to send a workbook via email based on the date an time? Our company uses excel for some of our reporting and rather then having the reps remember to send the workbook i'd like to have it send itself automatically - say on Saturdays or Sundays. Ideas? Nel post news:1165167749.298713.321880@l12g2000cwl.googlegroups.com *Gord* ha scritto: > Hey Folks, > > Is there an easy way to send a workbook via email based on the date an > time? Our company uses excel for some of our reporting and rather > then having the reps remember to send the wor...

Multiple Bar Codes Per Item
I'm looking to use Microsoft RMS for a medium sized game store with one terminal. One critical feature is the ability to have multiple bar codes assigned to the same item. Can RMS do this? Gary Gary, If you are talking about multiple part numbers, yes, use the alias function. Remember what a barcode is. Its a graphical representation of a partnumber that is machine readable. It doesn't describe the item, it is only a number to reference that item. Just like your phone number, it means nothing, but everytime someone dials that number, it gets to you. -- * "gareman" ...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

Open Workbook
I have a query that pulls 2 fields from a table. One field (ReportName) is selected from a form combo box. The second field (ReportLocation) in the query is the full path of the ReportName selected. I want to open the workbook using the ReportLocation, when the ReportName in the form changes. So I need to the code to enter in "On Change" for my form, so whatever report is selected, it opens up. Please help. Thank you in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1 here is my current code that doesn'...

multiple look ups
A B C D E F G H 1 PO TRANS $ $ AMT $ AMT QTY QTY # TYPE AMT RECV VCHR RECV VCHR 2 4227 RECV 668 668 0 26 0 3 4227 RECV 2,415.80 2,415.80 0 94 0 4 4227 VCHR 722 722 0 26 5 TOTAL 3,084 722 120 26 2362.34 For column F and G, I need each QTY RECV to have a matching QTY VCH (***See note at bottom) whereby ultimately, the total RECV QTY equal the total VCHR QTY w...

collating information from multiple sources
Hi Apologies if this has been asked before, but I'm a bit of a newbie when it comes to mucking around in excel, I've had a search but I don't really know what to call what I want to do, so finding the answer is tricky... Anyway. I have a dozen or so workbooks that are all of the same format, 1 work sheet with a basic list of test scenarios in each, with a unique reference for each row. What I'd like to do is pull information from all those workbooks into a separate workbook by the use of the unique reference. i.e If I type in the unique reference in the master workb...

selecting multiple choices from a drop down list
I have a drop down list in a cell and I want to be able to selec multiple entries from that list. Does anyone know how I go this -- Message posted from http://www.ExcelForum.com If this is a data validation list, you could use code to compile a list of selected items. For example, if the data validation is in column C, the following code will store the selected items in the same row in column D: '============================= Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False If Target.Count > 1 Then Exit Sub On Error Resume N...

Immediate Multiple Send Receive
Sometimes I get multiple send receive status and it repeats automatically and could not receive any msgs.I have to close the outlook 2002 and open it again.. then it works fine for few hours then again the same problem. What could be the reason.. I am using Outlook 2002 with SP3.. This problem happend after installation of SP3..Pls help. Thanks in advance On Thu, 15 Jul 2004 01:12:39 -0700, "Parag" <anonymous@discussions.microsoft.com> wrote: >Sometimes I get multiple send receive status and it >repeats automatically and could not receive any msgs.I >have to ...

Crosstab Query 04-06-07
I have a crosstab query that shows the products i sale with the number i have sold for each day. instead of showing the sales for each day i would like to show sales dor each months. How do i show it my month Thanks In query design view, enter this into a fresh column in the Field row: TheYear: Year([SaleDate]) Replace SaleDate with your actual field name. In the next column: TheMonth: Month([SaleDate]) You can now group on these fields instead of on each date. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html ...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Multiple Item Forms
Hi. I am new to your form and am struggling with the Multiple Item Forms. I want a combo box on the top that will select an id number from a table called 'SurveyA'. The multiple item form should then only display the records in a table called 'ActTravDiary' with that id number. As the ID number is changed a new group of records should be displayed in the form. I know how to initiate a change in one combo box based on another combo box (i.e. Row Source), but I cannot find a similar property for the multiple item forms. Please help. On Fri, 12 Feb 2010 21:00:2...

Compare 2 different workbooks with the result in a 3rd
I have two workbooks (2005 Sales, 2004 Sales), which track daily results in half hour intervals. I want to be able to show the increase in 2005 in a 3rd workbooks. The first two workbooks are identically formatted. How can I do this? Many thanks to all in the forum who have helped in the past. If the data is in exactly the same position in the two worksheets you could copy/paste one's data to a new worksheet and then copy the second's, doing an Edit, Paste Special, Subtract on top of the first's data. This is admittedly crude but it is easy to do. -- Jim Rech Excel MVP &q...

prompting to save workbook every time (even when no changes made)
i have 3 workbooks all very similar and every single time i open any o them, even when not changing anything, i am prompted to save th thing. its so bad that i can literally open the workbook then click the clos button straight away and it STILL prompts me to save it because i seems to think something has changed. as far as i can see i dont have any macros or anything that will ru every time it starts and change something (i did have a = today in on cell which kept the date as today but taking this out made n difference -- Message posted from http://www.ExcelForum.com Hi Neowok! You prob...

Macro on a protected worksheet in a shared workbook.
Hi, I have a macro in protected worksheets that can't run once the Workbook is Shared. I have wrapped the Macro code so as the worksheet is unprotected for the time the Macro runs, and unable the Autofilter. The code is as follow at the moment: Sub Newaction() Sheets("Critical Path").Unprotect ("") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheets("Critical Path").EnableAutoFilter = True Sheets("Critical Path").Protect contents:=True, userInter...

protecting shared workbook
Hi , I need to protect a shared workbook - other users within the office need to be able to neter data however do not seem to be able to do this withough reformatting column widths etc (we have some weird and wonderful people here) I have tried tried what i though would work - tools - Protect Sheet - then deselecting format cells, format columns, format rows however this also prevent users from enetering data. Any help appreciated. Try unlocking just the cells that are used for data entry: Select just the cells that you use for data entry. 'Format' menu --> Cells --> Pro...

Split single cell data into multiple colums
i have a huge data, converted from image to Excel. After converting the image to Excel all the data are stored in one cell( A1). But i want that to be split into different colums Example A1 September 15,2006 Name Email ID Father's Name Address City State Pincode Phone1 Phone2 Contact Time Area Code City Code State Code Amount Discount Total Occupation Emp no Remarks The above headers are the data in one single cell, but have too many spaces between each word. Help me get this resolved -- Thanks and...

multiple disclaimers with microsoft antigen 9
hello, i need to make multiple disclaimers with antigen. the html is not the problem, but i am not able to make a nice plain text one. how can i format the plain text disclaimer to have line breaks????? i tried many things but it displays always in one line... the code looks like this: ---cut--- <group number="1"> <disclaimers encoding="iso-8859-1"> <disclaimer type="html"><br/>HTML<br/><br/> Company<br/> Street<br/> Legal<br/><br/> CAUTION - bla bla bla</disclaime...

Propose a meeting with multiple dates in Outlook
How do you propose a meeting with multiple dates? Recurrence button S wrote: > How do you propose a meeting with multiple dates? > "S" <S@discussions.microsoft.com> wrote in message news:1448A718-D76A-4874-9FC0-03AFD4367566@microsoft.com... > How do you propose a meeting with multiple dates? See this: http://www.slipstick.com/calendar/pickmeeting.htm -- Brian Tillman [MVP-Outlook] On 3/11/2010 7:16 AM, S wrote: > How do you propose a meeting with multiple dates? > A third-party solution called Tungle works nicely for this. ht...

how to set up a query
I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreing key with table 2) Date_To_Work (date type) I want to list all the Departments (Dept_Name) that do not have anyone scheduled to work on a particular date (ie '1/20/2010' ) Any help would be appriciated. Thanks in advance, RABMissouri2010 Try this: SELECT dept_name FROM Table1 AS D WHERE NOT EXISTS(SELECT * ...