Excel VBA Passing Parameters Through Cell

[Diana]

Hello again,

I have a macro that I am running in Excel that queries a SQL Database
to return data. While recording the macro and trying to pass
parameters (a date range) through a specified cell, Excel crashes.
So, instead of recording while selecting a cell to grab the data, I
typed the date parameters (referencing cells B3 and B4 in Excel) into
the macro through VBA. Now my issue is that the macro is not returning
any data into my spreadsheet, it is only inserting a blank row at the
beginning where the data should start.
However, when opening the query through Microsoft Query (selecting
Data > Import External Data > Edit Query) and returning the data, the
data populates right where it should. I'm at my wit's end with this
one, and hope that someone could give me a direction to get this thing
working correctly!

Here is the query as shown in Excel VBA:

Sub Connect2()

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT PO.ORDER_DATE, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.CATEGORY, ITEMS.CUSTDATE1, " _
, _
"X_PO.QTY_REC, X_INVOIC.QTY_SHIP, X_STK_AREA.Q_STK,
X_INVOIC.ITEM_QTY, X_PO.ITEM_QTY, ITEMS.AVG_COST, ITEMS.AVG_SP " _
, _
"FROM EVEREST_VGI.dbo.INVOICES INVOICES, EVEREST_VGI.dbo.ITEMS
ITEMS, EVEREST_VGI.dbo.PO PO, " _
, _
"EVEREST_VGI.dbo.X_INVOIC X_INVOIC, EVEREST_VGI.dbo.X_PO X_PO,
EVEREST_VGI.dbo.X_STK_AREA X_STK_AREA " _
, _
"WHERE X_PO.ITEM_CODE = ITEMS.ITEMNO AND PO.ORDER_NO =
X_PO.ORDER_NO " _
, _
"AND X_INVOIC.ORDER_NO = INVOICES.ORDER_NO AND ITEMS.ITEMNO =
X_INVOIC.ITEM_CODE " _
, _
"AND ITEMS.ITEMNO = X_STK_AREA.ITEM_NO AND INVOICES.ORDER_DATE
= PO.ORDER_DATE " _
, _
"AND ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN')" _
, _
"AND (X_INVOIC.STATUS='8') AND (X_PO.STATUS In (2,3)) AND
(PO.ORDER_DATE BETWEEN " & Worksheets("Last 30
Days").Range("B3").Value & " AND " & Worksheets("Last 30
Days").Range("B4").Value & " ))")
.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub

 

[Robin Hammond]

Diana,

Try putting a single quote around the dates

e.g. where datevalue between '2007-04-01' and '2007-04-03'

 

[Diana]

Diana,

Try putting a single quote around the dates

e.g. where datevalue between '2007-04-01' and '2007-04-03'

--
Robin Hammondwww.enhanceddatasystems.com










- Show quoted text -

Hello Robin,

Thank you for your reply!

I am not able to put single quotes around the dates directly in the
cells B3 and B4, as they are using the formula =TODAY()-30 and
=TODAY() (these are my beginning and ending dates). Putting the single
quotes directly into the query in VBA (Worksheets("Last 30
Days").Range("B3").Value) throws me a Syntax Error.

Was there another way of doing the single quotes as you had
mentioned?

 

[Diana]

Hello Robin,

Thank you for your reply!

I am not able to put single quotes around the dates directly in the
cells B3 and B4, as they are using the formula =TODAY()-30 and
=TODAY() (these are my beginning and ending dates). Putting the single
quotes directly into the query in VBA (Worksheets("Last 30
Days").Range("B3").Value) throws me a Syntax Error.

Was there another way of doing the single quotes as you had
mentioned?- Hide quoted text -

- Show quoted text -

Aha, I was able to get the single quotes to work. I also reformatted
some of my query. Thanks a bunch!!

Final Query:
~~~~~~~~~~~~~~~

Sub Connect2()

Dim cellValue1 As String
Dim cellValue2 As String

cellValue1 = Range("B3").Value
cellValue2 = Range("B4").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT PO.ORDER_DATE, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.CATEGORY, ITEMS.CUSTDATE1, " _
, _
"X_PO.QTY_REC, X_INVOIC.QTY_SHIP, X_STK_AREA.Q_STK,
X_INVOIC.ITEM_QTY, X_PO.ITEM_QTY, ITEMS.AVG_COST, ITEMS.AVG_SP " _
, _
"FROM EVEREST_VGI.dbo.INVOICES INVOICES, EVEREST_VGI.dbo.ITEMS
ITEMS, EVEREST_VGI.dbo.PO PO, " _
, _
"EVEREST_VGI.dbo.X_INVOIC X_INVOIC, EVEREST_VGI.dbo.X_PO X_PO,
EVEREST_VGI.dbo.X_STK_AREA X_STK_AREA " _
, _
"WHERE X_PO.ITEM_CODE = ITEMS.ITEMNO AND PO.ORDER_NO =
X_PO.ORDER_NO " _
, _
"AND X_INVOIC.ORDER_NO = INVOICES.ORDER_NO AND ITEMS.ITEMNO =
X_INVOIC.ITEM_CODE " _
, _
"AND ITEMS.ITEMNO = X_STK_AREA.ITEM_NO AND INVOICES.ORDER_DATE
= PO.ORDER_DATE " _
, _
"AND ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN')" _
, _
"AND (X_INVOIC.STATUS='8') AND (X_PO.STATUS In (2,3)) AND
(PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' ))")
.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Robin Hammond]

Diana,

Glad it worked. A further thought to make this a little more bulletproof:

Date formats can vary according to international settings on different
machines. SQL is fussy. You might want to look at queries like this.

strSQL = "Select * from Table1 where DateField = '" &
format(sheets(1).cells(1,1).value,"yyyy-mm-dd") & "'"