[求助]用EXCEL的Query从用友数据库中取数

XDYDB

<b>[求助]用EXCEL从用友中取数
</b>
<P>以下是我用宏录制的用EXCEL的Query从用友数据库中取数的过程,太复杂了,看不懂,不过很好用.</P>
<P>可查询条件每次都要在代码中来改写,很麻烦.我想将条件赋给一个变量,变量值可直接在表中输入,从而简化查询.如:我在A1和A2单元格中输入时间"2004-11-1"和"2004-11-30",在B1和B2单元格中输入科目编码"65"和"69B",以下查询就会按我的要求查询.谢谢您的帮助,我很菜,请您说详细点.</P>
<P>Sub 宏3()
'
' 宏3 Macro
' dyang 记录的宏 2004-12-6
'</P>
<P>'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=uf;UID=sa;PWD=;APP=Microsoft? Query;WSID=FIN003;DATABASE=UFDATA_003_2004" _
        , Destination:=Range("B3"))
        .CommandText = Array( _
        "SELECT GL_accvouch.dbill_date, GL_accvouch.ccode, GL_accvouch.md, GL_accvouch.mc, GL_accvouch.cdept_id" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM UFDATA_003_2004.dbo.GL_accvouch GL_accvouch" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE (GL_accvouch.dbill_date&gt;={ts '2004-11-01" _
        , _
        " 00:00:00'} And GL_accvouch.dbill_date&lt;={ts '2004-11-30 00:00:00'}) AND (GL_accvouch.ccode&gt;='65' And GL_accvouch.ccode&lt;='69B')" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "ORDER BY GL_accvouch.ccode" _
        )
        .Name = "查询来自 uf"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    Range("B2").Select
End Sub</P>

lminz

<P>这是我的一个例子。能弹出对话框让你输入编码</P><P>Private Sub 库存查询_Click()
Dim snprice As Single
Dim sMessage As String
sMessage = "请输入你要查询的商品的商品编码: "
sngprice = Application.InputBox(sMessage, "商品编码", Type:=1)</P><P>     With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SRV;UID=POS;EngineName=SRV;AutoStop=YES;Integrated=No;EncryptedPassword=1adfgg081ad3915f;Debug=NO;Disable" _
        ), Array( _
        "MultiRowFetch=NO;CommLinks=TCPIP{host=192.168.2.1,port=2638},SharedMemory;Delphi=Yes" _
        )), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT a.GoodsCode, a.Name, a.SPrice,a.price, b.LastInPrice, b.StockPrice" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM dbo.goods a, dbo.stock b" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE a.GoodsID = b.GoodsID AND ((a.GoodsCode=" &amp; sngprice &amp; "))" _
        )
        .Name = "SRV"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub</P>