D
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
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