Excel variable usage??

[hd65]

Here's what I have..

SearchText = searchBoxtext.Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DefaultDir=C:\newsystest;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBuffe" _
), Array( _
"rSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("E4&quot)
.Sql = Array( _
"SELECT Widgets.`Part Number`, Widgets.Description, Widgets.`Qty on Hand`" & Chr(13) & "" & Chr(10) & "FROM Widgets.csv Widgets" & Chr(13) & "" & Chr(10) & "WHERE (Widgets.`Part Number` Like [SearchText]'%')" _
)
....and more

the "Like" part is what is not working. When I set the criteria value in Excel to "1" the string looks like this
Like '1%'. All I want to do is replace the "1" with the value from a textbox.....
I'm sure this is something simple but....
thanks for any help
paul

 

[Dreamboat]

Not a VBAer, but my first instinct is to say that percent isn't a value in Excel, it's a format/view. How 'bout trying .01 for 1% instead? In other words, get rid of the % and just type in .01 for 1% or .1 for 10%. See if that gets you heading in the right direction. At least till one of our great VBAer's come along.

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[SkipVought]

Hi,
Try asterisk rather than percent. Make sure that the resultant string is similar to...

WHERE (Widgets.`Part Number` Like  'xxx*')

where xxx is your search value. Skip,
metzgsk@voughtaircraft.com

 

[hd65]

SkipVought,
using the * causes no errors but does not display any data??
here's another look at what I’m trying.
maybe this will help...

Private Sub query()
SearchText = "'" + searchBoxtext.Value + "%'"
If ActiveSheet.QueryTables.Count = "0" Then
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DefaultDir=C:\newsystest;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBuffe" _
), Array( _
"rSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("E4&quot)
.Sql = Array( _
"SELECT Widgets.`Part Number`, Widgets.Description, Widgets.`Qty on Hand`" & Chr(13) & "" & Chr(10) & "FROM Widgets.csv Widgets" & Chr(13) & "" & Chr(10) & "WHERE (Widgets.`Part Number` Like SearchText)" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
Else
Range("E4&quot.Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DefaultDir=C:\newsystest;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBuffe" _
), Array( _
"rSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
))
.Sql = Array( _
"SELECT Widgets.`Part Number`, Widgets.Description, Widgets.`Qty on Hand`" & Chr(13) & "" & Chr(10) & "FROM Widgets.csv Widgets" & Chr(13) & "" & Chr(10) & "WHERE (Widgets.`Part Number` SearchText)" _
)
.Refresh True
End With
End If
End Sub

When this sub is run I get this error message from Excel
There is an error in the macro you were running. The specified method can't be used on the specified object for one of the following reasons.
. An argument contains a value that is not valid. A common cause of this problem is trying to gain access to an object that does not exist; for example, Workbooks(5) when only three workbooks are open.
· The method can't be used in the applied context. For example, some Range object methods require that the range contain data. If the range does not contain data, the method fails.
· An external error occurred, such as a failure to read or write from a file.

For more information about how to use the method, search for the method name in Visual Basic Help.

But I’m not sure what Help to look for.
This line is highlighted in the VB editor???
.Refresh BackgroundQuery:=False
Still very new to all this VBA stuff
thanks
paul

 

[SkipVought]

I am not sure what is happening. You seem to have some properties and methods that I cannot find in the object library. For instance, there is no SQL p/m for the QueryTable object.
The other thing is that this string in your SQL:

"WHERE (Widgets.`Part Number` Like SearchText)"

will not give you the result that you are looking for. What you need to do is:

"WHERE (Widgets.`Part Number` Like " + SearchText + ")"

Skip,
metzgsk@voughtaircraft.com

 

[hd65]

I have no idea why but by asigning the complete .sql array string to the variable I can do whaterver I want so here is the solution...I can up with...

sqlString = "SELECT " + datacat + ".`Part Number`, " + datacat + ".Description, " + datacat + ".`Qty on Hand`" & Chr(13) & "" & Chr(10) & "FROM " + datacat + ".csv " + datacat + ""

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DefaultDir=C:\newsystest;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBuffe" _
), Array( _
"rSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("E4&quot)
.Sql = Array( _
sqlString)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With