Excel Query (2K)

Topic

New Reply

I would appreicate any advice on an Excel query – How to get started. I have 30+ files named by job number and a corresponding sales code (i.e. 123_pr.xls). 123 is the sale and pr is the code. All Excel files have exactly the same format with different information on each sale. How exactly do I use Excel to query or retrieve say, cell A1 in all ***_pr into a “Query” sheet. Please advise as I really, really don’t want to buy (and learn, ugh) Access!

Any help is greatly appreciated,

Drew

P.S. Excel 2K and yes, M$ Query is installed.
__________________
Please help me as I am attempting to (re)gain my (in)sanity.

Reply | Quote

Replies

Hi,

In this post, I posted code to go through a directory and find all files based on a filter. You can add some code to open each file that matches your filter, and take out the value at cell A1. The filter may be “*_pr.xls”, allowing you to change the * for all combinations of sales codes. By using the workbook.open method and putting the activesheet.range(“A1”).value in an array, you’ll end up with the values of all your workbooks in that array, allowing you to print, work with it, or whatever you want…

Reply | Quote

Am I glad you showed up! I have been wrestling with MSQuery for the last 4 months, since the meanie IS guy won’t give me access to the AS400 query library. I am wading chest deep in @#$!% trying to get the information I need in a timely manner. What I’ve found is there really isn’t a “book” about MSQuery. There are all kinds of programming books about IBM Client Access, but there ain’t no “Dummies” version out there for those of us who want a quick answer. I’ve forwarded Han’s reply to myself at work and intend to try it ASAP. Have you? Please let me know.

If you haven’t, and are still wandering around lost, here’s some stuff I’ve done:

Take an hour and just look through the tables that are available by clicking on them and seeing the fields. There’s a button that you can click on to “Preview Now”, and then you can scroll down through the table’s field. If you see anything you might like, run a query on an item/customer/ whatever you know thoroughly by filtering the query….I like to do screen shots of the file I think the table applies to, to compare once I put it into Query. Once you’ve explored it in the MSQuery mode…be sure to include EVERY field, you never know what will show up and you might think about later, put the query into Excel.

Then, do a “Ctrl +A”, “Ctrl+C” and open what wil be your bible…a workbook with all the tables you might possibly be interested in. It’s a simple workbook, one you have given a loving name because it will serve you well.

I like to use a different worksheet for every table I find curious. MAKE YOUR LIFE SIMPLE and use Paste Special to transpose the fields (it’s on the bottom right of the Paste Special options)_…that way everything goes down, down, down, rather that having to fight the across syndrome. Then, you simple look at what the geeks in IS have named the fields and match them up to what you see in your screen shot…add a column, put in your own references. You will be thought of as brilliant by everyone outside of group services, and a pain in the neck by those within because you will start to understand the process and question their oblique references to common items.

By the by, Access is a blast if you get the right teacher…it’s like being your own Porsche mechanic. First you start with changing the oil, then a tune up….personally, I’m getting ready to learn how to adjust the carburetor and I’m pumped!

Reply | Quote

I got it. In a round about way, but it works. Below is my code to:

1) Set counted results above links in order to delete row at the end
2) Run the Consolidate with the wild card for the workbooks and retrieve certain cell data (hence the need to “Count” and “Create Links”)
3)Select the cell,number (column Headings in 1) to run the consolidate in
4) Clear the annoying outline expand/collaspe fields (this example is only a fraction of mine, I have 42 fields and they take up 3/4 of the screen)
5) Delete all of the blank cells in the selected range and shift all other up because the “consolidate” even in seperate columns will “stagger” data in a step-like fashion.
6) Delete the counted results in row2
7) Set A1 active so “new” row2 is not highlighted.

WHEW!!! That was FUN!!! Let’s try another… (a tad bit of sarcasm escapes my lips)

If anyone has any ideas to shorten, make faster, re-reference, I’m all about it as this sheet will only grow for me and I’ll need to update my selected range for deletion of empty cells.

Thanks,

Drew

Sub Retrieve_Data()

With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With

Range(“A2″).Select
Selection.Consolidate Sources:=”‘[19*.xls]Sheet1’!R3C3”, Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True

Range(“B2″).Select
Selection.Consolidate Sources:=”‘[19*.xls]Sheet1’!R5C2”, Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True

Range(“C2″).Select
Selection.Consolidate Sources:=”‘[19*.xls]Sheet1’!R16C9”, Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Selection.ClearOutline

Range(“A2:C100”).Select
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)

Rows(“2:2”).Select
Selection.Delete Shift:=xlUp

Range(“A1”).Select

End Sub

Reply | Quote