ODBC data input in macro

[addiktd]

*** I'm using VBA for excel 2010 ***

 

[PHV]

Like this ?
& "WHERE (INVTRY.VendorCode='[!]" & InputBox("Vendor") & "[/!]')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[addiktd]

Exactly and its even more then what i wanted!!! Thanks!!

 

[addiktd]

Ok last question. Now what i want to do is that if no anwser is given in the box then the macro will skip to the other region.

 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER={CONNX32};UID=odbc20;PWD=meow;NODE=;APPLICATION=;DD=C:\CONNX32\UTILS\UAPLEGEND61.cdd;DESCRIPTION=;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT INVTRY.Branch, INVTRY.VendorCorePart, INVTRY.VendorCode, INVTRY.PartNo, INVTRY.PriceClass, INVTRY.ProfitCenter, INVTRY.Location, INVTRY.RecordLocked, INVTRY.AlternateKey, INVTRY.PurchaseFactor," _
        , _
        " INVTRY.Code1, INVTRY.Code2, INVTRY.Code3, INVTRY.Code4, INVTRY.Code5, INVTRY.Substitution, INVTRY.Season, INVTRY.FormCode, INVTRY.Kit, INVTRY.Serial, INVTRY.PurchaseMethod, INVTRY.Freeze, INVTRY.Frei" _
        , _
        "ghtMultiplier, INVTRY.AlternateSource, INVTRY.DateAdded, INVTRY.LastPurchase, INVTRY.LastReceived, INVTRY.LastOut, INVTRY.LastCycleCount, INVTRY.TransnetProductCode, INVTRY.TransnetInstructCode, INVTR" _
        , _
        "Y.InventoryClass, INVTRY.VMRS, INVTRY.Description, INVTRY.CoreClass, INVTRY.UnitOfMeasure, INVTRY.AlternateVendorCode, INVTRY.ProductCode, INVTRY.DaysOutOfStock, INVTRY.SafetyStockPercet, INVTRY.Avera" _
        , _
        "geCostQuantity, INVTRY.QuantityOnHand, INVTRY.CommittedQuantity, INVTRY.CustomerBOQuantity, INVTRY.QuantityOnOrder, INVTRY.OrderPoint, INVTRY.LinePoint, INVTRY.MinimumStock, INVTRY.EOQ, INVTRY.Fill, I" _
        , _
        "NVTRY.MonthSales1, INVTRY.MonthSales2, INVTRY.MonthSales3, INVTRY.MonthSales4, INVTRY.MonthSales5, INVTRY.MonthSales6, INVTRY.MonthSales7, INVTRY.MonthSales8, INVTRY.MonthSales9, INVTRY.MonthSales10, " _
        , _
        "INVTRY.MonthSales11, INVTRY.MonthSales12, INVTRY.MonthSales13, INVTRY.MonthFreezeFlag1, INVTRY.MonthFreezeFlag2, INVTRY.MonthFreezeFlag3, INVTRY.MonthFreezeFlag4, INVTRY.MonthFreezeFlag5, INVTRY.Month" _
        , _
        "FreezeFlag6, INVTRY.MonthFreezeFlag7, INVTRY.MonthFreezeFlag8, INVTRY.MonthFreezeFlag9, INVTRY.MonthFreezeFlag10, INVTRY.MonthFreezeFlag11, INVTRY.MonthFreezeFlag12, INVTRY.PriorYearSales, INVTRY.Pric" _
        , _
        "eGroup, INVTRY.BuyTime, INVTRY.CurrentLeadTime, INVTRY.PreviousLeadTime, INVTRY.BuyPackage, INVTRY.SellPackage, INVTRY.Weight, INVTRY.ListPrice, INVTRY.Price1, INVTRY.Price2, INVTRY.Price3, INVTRY.Cos" _
        , _
        "t, INVTRY.AverageCost, INVTRY.LastPurchaseCost, INVTRY.FYPurchaseCost, INVTRY.FYAverageCost, INVTRY.FYQuantity, INVTRY.PFYPurchaseCost, INVTRY.PFYAverageCost, INVTRY.PFYQuantity, INVTRY.TransnetPartPr" _
        , _
        "efix, INVTRY.Special, INVTRY.Picks1, INVTRY.Picks2, INVTRY.Picks3, INVTRY.Picks4, INVTRY.Picks5, INVTRY.Picks6, INVTRY.Picks7, INVTRY.Picks8, INVTRY.Picks9, INVTRY.Picks10, INVTRY.Picks11, INVTRY.Pick" _
        , _
        "s12, INVTRY.GMRimState, INVTRY.LastSoldDate, INVTRY.Fill_02" & Chr(13) & "" & Chr(10) & "FROM INVTRY INVTRY" & Chr(13) & "" & Chr(10) & "WHERE (INVTRY.VendorCode='" & InputBox("Vendor for EAST") & "')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tableau_Lancer_la_requête_à_partir_de_20"
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Centre").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER={CONNX32};UID=odbc40;PWD=meow;NODE=;APPLICATION=;DD=C:\CONNX32\UTILS\UAPLEGEND61.cdd;DESCRIPTION=;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT INVTRY.Branch, INVTRY.VendorCorePart, INVTRY.VendorCode, INVTRY.PartNo, INVTRY.PriceClass, INVTRY.ProfitCenter, INVTRY.Location, INVTRY.RecordLocked, INVTRY.AlternateKey, INVTRY.PurchaseFactor," _
        , _
        " INVTRY.Code1, INVTRY.Code2, INVTRY.Code3, INVTRY.Code4, INVTRY.Code5, INVTRY.Substitution, INVTRY.Season, INVTRY.FormCode, INVTRY.Kit, INVTRY.Serial, INVTRY.PurchaseMethod, INVTRY.Freeze, INVTRY.Frei" _
        , _
        "ghtMultiplier, INVTRY.AlternateSource, INVTRY.DateAdded, INVTRY.LastPurchase, INVTRY.LastReceived, INVTRY.LastOut, INVTRY.LastCycleCount, INVTRY.TransnetProductCode, INVTRY.TransnetInstructCode, INVTR" _
        , _
        "Y.InventoryClass, INVTRY.VMRS, INVTRY.Description, INVTRY.CoreClass, INVTRY.UnitOfMeasure, INVTRY.AlternateVendorCode, INVTRY.ProductCode, INVTRY.DaysOutOfStock, INVTRY.SafetyStockPercet, INVTRY.Avera" _
        , _
        "geCostQuantity, INVTRY.QuantityOnHand, INVTRY.CommittedQuantity, INVTRY.CustomerBOQuantity, INVTRY.QuantityOnOrder, INVTRY.OrderPoint, INVTRY.LinePoint, INVTRY.MinimumStock, INVTRY.EOQ, INVTRY.Fill, I" _
        , _
        "NVTRY.MonthSales1, INVTRY.MonthSales2, INVTRY.MonthSales3, INVTRY.MonthSales4, INVTRY.MonthSales5, INVTRY.MonthSales6, INVTRY.MonthSales7, INVTRY.MonthSales8, INVTRY.MonthSales9, INVTRY.MonthSales10, " _
        , _
        "INVTRY.MonthSales11, INVTRY.MonthSales12, INVTRY.MonthSales13, INVTRY.MonthFreezeFlag1, INVTRY.MonthFreezeFlag2, INVTRY.MonthFreezeFlag3, INVTRY.MonthFreezeFlag4, INVTRY.MonthFreezeFlag5, INVTRY.Month" _
        , _
        "FreezeFlag6, INVTRY.MonthFreezeFlag7, INVTRY.MonthFreezeFlag8, INVTRY.MonthFreezeFlag9, INVTRY.MonthFreezeFlag10, INVTRY.MonthFreezeFlag11, INVTRY.MonthFreezeFlag12, INVTRY.PriorYearSales, INVTRY.Pric" _
        , _
        "eGroup, INVTRY.BuyTime, INVTRY.CurrentLeadTime, INVTRY.PreviousLeadTime, INVTRY.BuyPackage, INVTRY.SellPackage, INVTRY.Weight, INVTRY.ListPrice, INVTRY.Price1, INVTRY.Price2, INVTRY.Price3, INVTRY.Cos" _
        , _
        "t, INVTRY.AverageCost, INVTRY.LastPurchaseCost, INVTRY.FYPurchaseCost, INVTRY.FYAverageCost, INVTRY.FYQuantity, INVTRY.PFYPurchaseCost, INVTRY.PFYAverageCost, INVTRY.PFYQuantity, INVTRY.TransnetPartPr" _
        , _
        "efix, INVTRY.Special, INVTRY.Picks1, INVTRY.Picks2, INVTRY.Picks3, INVTRY.Picks4, INVTRY.Picks5, INVTRY.Picks6, INVTRY.Picks7, INVTRY.Picks8, INVTRY.Picks9, INVTRY.Picks10, INVTRY.Picks11, INVTRY.Pick" _
        , _
        "s12, INVTRY.GMRimState, INVTRY.LastSoldDate, INVTRY.Fill_02" & Chr(13) & "" & Chr(10) & "FROM INVTRY INVTRY" & Chr(13) & "" & Chr(10) & "WHERE (INVTRY.VendorCode='" & InputBox("Vendor for CENTRE") & "')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tableau_Lancer_la_requête_à_partir_de_40"
        .Refresh BackgroundQuery:=False
    End With

Any ideas ? Again thank you in advance!

 

[SkipVought]

Hi,

Each time you run this, it ADDS a ListObject to you sheet. That is not a good idea. You COULD delete the listobject before adding again, but WHY?

I do this nearly every day. In fact, this is a procedure that I am currently working on. The thing is that ONE TIME and ONE TIME ONLY, you add the ListObject/QueryTable, and I do that manually. because that is when I MANUALLY build my query. ONE TIME!

Sub Completions()
    Dim sSQL As String, sYR As String
    
    sYR = Format(Date, "yyyy")
    
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  RQ.PARTNO_201 PN"
    sSQL = sSQL & ", RQ.RQ.RQDATE8_275 DTE"
    sSQL = sSQL & ", RQ.RQQTY_275*(-1) QTY"
    sSQL = sSQL & ", 'RQ' TYP"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FRH_MRP.PSK02275 RQ"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE SUBSTR(RQ.RQDATE8_275,1,4)='" & sYR & "'"
    sSQL = sSQL & "  AND RQ.PARTNO_201 IN (" & MakeList(wsPartList.[tPartList[Part List]]) & ")"
[b]     
    With wsComp.ListObjects(1).QueryTable
        .CommandText = sSQL
        .Refresh False
    End With[/b]
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}