WorkSheet Query 2

[IknowMe]

Background,

For simplicity in the explanaition let's say I have a Workbook (C:/ThisWorkbook.xls)

This workbook has three worksheets (LocationOne, LocationTwo, LocationThree)

Each worksheet is standardized as follows

ID1 ID2 ContactName
12 05 Barney Fife
12 17 Opie Taylor

ect ect.

The contact names at each location (worksheet) for a unique ID combination will be different. thus far I am looping through each row of the appropriate worksheet to lookup a contact via the unique ID cobination. Is there any way I can avoid the loop through each row of the worksheet and query the whole sheet via a formula or sql type query?


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[SkipVought]

Hi,

Yes, you could use MS Query to access each table (sheet).

However, I'd strongly recommend redesigning your workbook to have ALL your data in one table, with Location as a field. It will greatly reduce maintanance and greatly increase productivity and the application of Excel's data analysis and reporting functionality.

Skip,

_[red][/red]

 

[IknowMe]

Skip, Thanks for the prompt reply as usual.

While I agree with your recomendation, I don't forsee it happening anytime soon. I had to pull teeth just to get the worksheets standardized. The information is supplied from each site, changes fairly frequently and is input via our lowest level of clerk.

Short of maintaining the information myself (no time nor desire) I'll have to stick with what I've got.

If not to time consuming, could you give me a sample of the syntax used to MS Query a worksheet? Setting the objects/what the query string should look like. It's definately not my strong suit (if any). In the meantime you've given me a direction to research and I appreciate your help. Have a star.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[SkipVought]

Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[IknowMe]

Okay here's the short version of what I'm attempting so far.

Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=C:\test.xls;" &_
        "Extended Properties=Excel 8.0;"
        .Open
    End With
    
    
    strQuery = "[COLOR=red]SELECT * FROM[/color] [Sheet1$[COLOR=red]A1:B10[/color]]"

I've read up and understand the Record Set object, but still working on the query, specifically the parts in red. I'm not sure how to change the range A1:B10 to include the whole sheet (still researching but any helps appreciated). I have multiple sheets with different amounts of rows althought the columns are always A-N. I think I can figure this part out on my own with a little more browsing.

This second part is the one I'm strugling a bit harder with:

I'm new to Queries and am trying to get the correct syntax for a conditional query as described in the OP.

Each worksheet is standardized as follows

ID1 ID2 ContactName
12 05 Barney Fife
12 17 Opie Taylor

So I need something the likes of

Select ContactName Where ID1 = 12 and ID2 = 05

But I'm not entirely sure how to write it.

Side note, Congrats PHV I saw your name in lights on the TT doormat on the way in. [small]Yes, that's a shameless attempt at provoking your help .[/small]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

Doesn't this suffice ?
strQuery = "SELECT * FROM [Sheet1$]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

That was easy , and the conditions?

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

Where are the 12 and 05 coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

strQuery = "SELECT ContactName FROM [Sheet1$A1:B10] Where Where ID1 = 12 and ID2 = 05"


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

WorkSheet formated as such

ID1 ID2 ContactName
12 05 Barney Fife
12 17 Opie Taylor

So the 12 an the five are the values under the field item names.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

And the problem is ?

 

[IknowMe]

I had no Idea that was good syntax. Two problems which I think I can resolve.

"SELECT ContactName FROM [Sheet1] Where Where ID1 = 12 and ID2 = 05" is giving me an data type mismatch error

strQuery = "SELECT EGC FROM [101$] WHERE PBC = "101" And SBC = "87700"" gives a compile error 'expected end of if'.

I can work with these though thanks for your help and have another star.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

Perhaps this ?

strQuery = "SELECT EGC FROM [101$] WHERE PBC = '101' And SBC = '87700'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

Ended up working with the following

strQuery = "Select * from [SheetName$] Where [aColumnTitle] = " & "12" & " And [AnotherColumnTitle] = " & "05"

Phv and Skip thanks for all your help!

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[SkipVought]

The key point is related to the type of data the the column. If the column is TEXT, then you need a set of tics.

Skip,

_[red][/red]