在自建的EXCEL中实时读取金蝶KIS专业版11.0即时库存的案例可否实现？方法是什么？

三十七万

请问有哪位同志实现过在自建的EXCEL中实时读取金蝶KIS专业版11.0即时库存的案例？我现在都是定期导出之后用VLOOKUP的方法得到想要物料编码的库存再在EXCEL中实现一些需要的操作，时效性较差较麻烦，请有这方面专长的同志们帮助一下，谢谢！

gdw831001

哈哈，这个我不会，但据我了解，你想要实时库存，哪你首先要有二次开发的权限，然后你用编程来获取实时库存，这个权限正常只有管理员才能有权限的,所以还是自己导出库存然后在做了，如果每次操作都是相同的步骤，可以考虑用录制宏的方法来解决。

sq12345

你可以找金碟公司要数据字典，找到存放数据的表，通过ODBC或ADO直接访问表就行了。当然喽，你还要有数据库的账号和密码。

weiyu_zh

金蝶数据库被加密了，后缀名编程了AIS。不知道连接字符串该怎么写？请高手指教，谢谢！

rainless

SQL 数据库，只要你就sa的权限就可以直接用ODBC去链接实时的库存表

昨夜星辰昨夜风

select
B.FBrNo,
B.FInterID as 单据内码,
B.FPosted as 记账,
B.FSupplyID as 供应商内码,
B.FDate as 日期,
B.FBillNo as 单号,
B.FTranType as 单据类别内码,
T.FName as 单据类别,
B.FDeptID as 部门内码,
E.FEntryID as 分录号,
E.FDCStockID as 入仓内码,
SD.FNumber as 入仓代码,
SD.FName as 入仓名称,
E.FSCStockID as 出仓内码,
SC.FNumber as 出仓代码,
SC.FName as 出仓名称,
E.FItemID as 物料内码,
I.FNumber as 物料代码,
I.FName as 名称,
E.FPrice as 单价,
E.FUnitID as 单位内码,
U.FName as 单位,
E.FQty as 数量,
E.FAmount as 金额
from icstockbill as B,ICStockBillEntry as E,t_Item as I,t_Item as SD,t_Item as SC,t_Item as U,ICTransType as T
where
B.FBrNo=E.FBrNo and B.FInterID=E.FInterID
and E.FItemID=I.FItemID and E.FDCStockID=SD.FItemID
and E.FSCStockID=SC.FItemID and E.FUnitID=U.FItemID
and E.FBrNo=T.FBrNo and B.FTranType=T.FID
order by B.FBrNo,B.FInterID,E.FEntryID

昨夜星辰昨夜风

出入库流水(SQL)
select
B.FBrNo,
B.FInterID as 单据内码,
B.FPosted as 记账,
B.FSupplyID as 供应商内码,
B.FDate as 日期,
B.FBillNo as 单号,
B.FTranType as 单据类别内码,
T.FName as 单据类别,
B.FDeptID as 部门内码,
E.FEntryID as 分录号,
E.FDCStockID as 入仓内码,
SD.FNumber as 入仓代码,
SD.FName as 入仓名称,
E.FSCStockID as 出仓内码,
SC.FNumber as 出仓代码,
SC.FName as 出仓名称,
E.FItemID as 物料内码,
I.FNumber as 物料代码,
I.FName as 名称,
E.FPrice as 单价,
E.FUnitID as 单位内码,
U.FName as 单位,
E.FQty as 数量,
E.FAmount as 金额
from icstockbill as B,ICStockBillEntry as E,t_Item as I,t_Item as SD,t_Item as SC,t_Item as U,ICTransType as T
where
B.FBrNo=E.FBrNo and B.FInterID=E.FInterID
and E.FItemID=I.FItemID and E.FDCStockID=SD.FItemID
and E.FSCStockID=SC.FItemID and E.FUnitID=U.FItemID
and E.FBrNo=T.FBrNo and B.FTranType=T.FID
order by B.FBrNo,B.FInterID,E.FEntryID

昨夜星辰昨夜风

金蝶出入库流水（VBA，需要引入KIS控件）：
Sub 出入库序时账簿()
'
'
'
Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim STR_SQL, F_SQL, W_SQL As String
Dim i  As Integer

'连接数据库--根据全局变量STR_CNN传递数据库连接字符串
If CNNLogin.STR_CNN = "" Then  '如果连接字符串为空，说明未曾登录过
   连接金蝶数据库     '如果未登录，则调用LOGIN连接过程（重新赋值给STR_CNN）
End If
CNN.Open CNNLogin.STR_CNN

If CNN.State <> adStateOpen Then
   ActiveCell = "数据库未连接"
   Exit Sub
End If
'查询语句
F_SQL = "select "
F_SQL = F_SQL + "B.FDate as 日期,"
F_SQL = F_SQL + "T.FName as 单据类别,"
F_SQL = F_SQL + "B.FPosted as 记账,"
F_SQL = F_SQL + "B.FBillNo as 单号,"
F_SQL = F_SQL + "SD.FNumber as 入仓代码,"
F_SQL = F_SQL + "SD.FName as 入仓名称,"
F_SQL = F_SQL + "SC.FNumber as 出仓代码,"
F_SQL = F_SQL + "SC.FName as 出仓名称,"
F_SQL = F_SQL + "S.FNumber as 供应商代码,"
F_SQL = F_SQL + "S.FName as 供应商,"
F_SQL = F_SQL + "I.FNumber as 物料代码,"
F_SQL = F_SQL + "I.FName as 名称,"
F_SQL = F_SQL + "U.FName as 单位,"
F_SQL = F_SQL + "E.FPrice as 单价,"
F_SQL = F_SQL + "E.FQty as 数量,"
F_SQL = F_SQL + "E.FAmount as 金额 "
'来源表
F_SQL = F_SQL + "from "
F_SQL = F_SQL + "icstockbill as B,"          '单据头
F_SQL = F_SQL + "ICStockBillEntry as E,"     '单据内容
F_SQL = F_SQL + "t_Item as I,"               '物料表
F_SQL = F_SQL + "t_Item as SD,"              '（入库）仓库表
F_SQL = F_SQL + "t_Item as SC,"              '（出库）仓库表
F_SQL = F_SQL + "t_Item as U,"               '单位表
F_SQL = F_SQL + "t_Supplier as S,"           '供应商表
F_SQL = F_SQL + "ICTransType as T "          '单据类别表
'查询条件
W_SQL = "where B.FBrNo=E.FBrNo "
W_SQL = W_SQL + "and B.FInterID=E.FInterID "
W_SQL = W_SQL + "and E.FItemID=I.FItemID "
W_SQL = W_SQL + "and E.FDCStockID=SD.FItemID "
W_SQL = W_SQL + "and E.FSCStockID=SC.FItemID "
W_SQL = W_SQL + "and E.FUnitID=U.FItemID "
W_SQL = W_SQL + "and B.FSupplyID=S.FItemID "
W_SQL = W_SQL + "and E.FBrNo=T.FBrNo "
'排序
W_SQL = W_SQL + "order by B.FBrNo,B.FInterID,E.FEntryID"
'合成查询语句
STR_SQL = F_SQL + W_SQL


'执行查询，得到RST数据集
On Error Resume Next
  RST.Open STR_SQL, CNN
On Error GoTo 0

'遍历设置字段名 (标题行)
Dim R, C As Integer
R = ActiveCell.Row
C = ActiveCell.Column
For i = 0 To RST.Fields.Count - 1
    Cells(R, C + i) = RST.Fields(i).Name
Next
Cells(R + 1, C).CopyFromRecordset RST   '将RST数据集复制导出。注意，不带字段名（标题行）
'清除内存占用
Set RST = Nothing
Set CNN = Nothing
End Sub