ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 3448|回复: 7

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-11-27 11:11 | 显示全部楼层 |阅读模式
请问有哪位同志实现过在自建的EXCEL中实时读取金蝶KIS专业版11.0即时库存的案例?我现在都是定期导出之后用VLOOKUP的方法得到想要物料编码的库存再在EXCEL中实现一些需要的操作,时效性较差较麻烦,请有这方面专长的同志们帮助一下,谢谢!

TA的精华主题

TA的得分主题

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

TA的精华主题

TA的得分主题

发表于 2013-12-11 21:34 | 显示全部楼层
你可以找金碟公司要数据字典,找到存放数据的表,通过ODBC或ADO直接访问表就行了。当然喽,你还要有数据库的账号和密码。

TA的精华主题

TA的得分主题

发表于 2015-11-4 11:30 | 显示全部楼层
金蝶数据库被加密了,后缀名编程了AIS。不知道连接字符串该怎么写?请高手指教,谢谢!

TA的精华主题

TA的得分主题

发表于 2015-11-11 10:12 | 显示全部楼层
SQL 数据库,只要你就sa的权限就可以直接用ODBC去链接实时的库存表

TA的精华主题

TA的得分主题

发表于 2016-3-29 21:45 | 显示全部楼层
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

TA的精华主题

TA的得分主题

发表于 2016-3-29 21:46 | 显示全部楼层
出入库流水(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

TA的精华主题

TA的得分主题

发表于 2016-3-29 21:49 | 显示全部楼层
金蝶出入库流水(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
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-10-14 21:50 , Processed in 0.066418 second(s), 11 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表