Query Macro will Not Return a Query

Mike,

'pears to me that the problem could be here:

& "WHERE (`Sheet1$`.CIRCUIT Like SearchName)" &

This should read:

& "WHERE (`Sheet1$`.CIRCUIT Like " & SearchName & ")" &

In other words, as its presently written you are passing the variable SearchName to the string.

Also, not sure I'd put the Chr(13) stuff in the string; SQL doesn't have to be broken up in that way.

Try the first thing I suggested and if that works and you want to mess with it you can dink around with cleaning up the SQL.

good luck

oops, should read:

... you aren't passing ..."

Bariloche,

I tried that. Is there any way to find out what the value of SearchName is? I'm thinking that maybe my If...Then...Else statements to assign a value to SearchName are not getting the information from cell A1.

Thanks for the help though. Because of you the macro will now find the file I want it to. I took your example and applied it to the code and it works fine.

Thanks,
Mike

Mike,

There are a few ways to see the value of a variable. One of the easiest is to "Step-through" your code, then as a particular line is executed (e.g., one where a variable gets an assigned value), hover your mouse pointer over the variable and you'll get a little "pop-up" comment-type window that will show you the current value.

The other ways are to use the MsgBox function at strategic places or to use the Immediate Pane (using a Debug.Print command). I usually use the first method exclusively, but the others can have their place.

re: the SearchName. What kind of database are you querying? The reason I ask is that the wildcard for "match anything" in Access is an asterisk (*), not a percent sign (%). (Not sure if this is part of your problem or not.)

enjoy

Bariloche,

I am querying an Excel sheet with Comma Delimited Text files OLEd in it. There are a few ways to see the value of a variable. One of the easiest is to "Step-through" your code, then as a particular line is executed (e.g., one where a variable gets an assigned value), hover your mouse pointer over the variable and you'll get a little "pop-up" comment-type window that will show you the current value. The other ways are to use the MsgBox function at strategic places or to use the Immediate Pane (using a Debug.Print command). I usually use the first method exclusively, but the others can have their place. re: the SearchName. What kind of database are you querying? The reason I ask is that the wildcard for "match anything" in Access is an asterisk (*), not a percent sign (%). (Not sure if this is part of your problem or not.) enjoy

: Bariloche, : I tried that. Is there any way to find out what the value of SearchName is? I'm thinking that maybe my If...Then...Else statements to assign a value to SearchName are not getting the information from cell A1. : Thanks for the help though. Because of you the macro will now find the file I want it to. I took your example and applied it to the code and it works fine. : Thanks, : Mike

Here is the Working Query Macro

Sub NewQuery()
Dim MyName As String
Dim MyName2 As String
Dim connstring As String
Dim SearchName As String
MyName = ThisWorkbook.Path & "\Circuit.xls"
MyName2 = ThisWorkbook.Path & "\Circuit"
connstring = "DSN=Excel Files;DBQ=" & MyName
SearchName = Range("A2").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;" & connstring & ";DefaultDir=" & ThisWorkbook.Path & " &;DriverId=790;MaxBufferSize=204" _
), Array("8;PageTimeout=5;")), Destination:=Range("A41"))
.CommandText = Array( _
"SELECT `Sheet1$`.CIRCUIT, `Sheet1$`.ROOM, `Sheet1$`.WATTS, `Sheet1$`.`DIV#_CODE`" & Chr(13) & "" & Chr(10) & "FROM `" & MyName2 & "`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.CIRCUIT Like '" & SearchName & "-%')" & Chr(13) & "" & Chr(10) & "ORDER BY `Shee" _
, "t1$`.CIRCUIT")
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 60
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With
End Sub

Congratulations Mike!, well done :-) (NT)

Dim MyName2 As String MyName = ThisWorkbook.Path & "\Circuit.xls" MyName2 = ThisWorkbook.Path & "\Circuit" connstring = "DSN=Excel Files;DBQ=" & MyName SearchName = Range("A2").Value With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;" & connstring & ";DefaultDir=" & ThisWorkbook.Path & " &;DriverId=790;MaxBufferSize=204" _ ), Array("8;PageTimeout=5;")), Destination:=Range("A41")) "SELECT `Sheet1$`.CIRCUIT, `Sheet1$`.ROOM, `Sheet1$`.WATTS, `Sheet1$`.`DIV#_CODE`" & Chr(13) & "" & Chr(10) & "FROM `" & MyName2 & "`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.CIRCUIT Like '" & SearchName & "-%')" & Chr(13) & "" & Chr(10) & "ORDER BY `Shee" _ .SaveData = True .Refresh BackgroundQuery:=False