[原创]本人整理后的凭证导入程序

zangma

<p>供大家共享，我用这个做凭证只用了原来手工输入时间的1/10。&nbsp;</p> maXdeuAJ.rar (91.1 KB, 下载次数: 1553)

2007-5-10 16:45 上传

点击文件名下载附件
[原创]本人整理后的凭证导入程序

<br/>

徒然客

<p align="center" style="MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: 20pt; TEXT-ALIGN: center; mso-line-height-rule: exactly;"><b><span style="FONT-SIZE: 16pt; mso-bidi-font-size: 12.0pt;">关于<span lang="EN-US">U8</span>引入外部凭证的专题业务解决方案<span lang="EN-US"><p></p></span></span></b></p><p align="center" style="MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: 15pt; TEXT-ALIGN: center; mso-line-height-rule: exactly;"><b><span lang="EN-US"><p>&nbsp;</p></span></b></p><p align="center" style="MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: 15pt; TEXT-ALIGN: center; mso-line-height-rule: exactly;"><b><span lang="EN-US"><p>&nbsp;</p></span></b></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly;">在<span lang="EN-US">U8</span>实施过程中，经常碰到这样的情况：客户通常自行开发有工资、固定资产等软件，一般不会购买<span lang="EN-US">U8</span>相关的工资、固定资产模块，另外，出于客户计算机应用程度及管理水平等因素一般也不会购买成本管理模块。如果不购买配套产品，如工资分配，需要将其工资和计提的福利费等核算到各车间、处室中去，这样在总账中录入凭证的工作量较大，且容易出错。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly;">如何解决上述问题呢？可以在总账中自定义转账来解决，但录入数据时不是很直观且不能利用自编软件的数据文件直接生成。有一种更好的办法，就是利用总账工具引入外部系统输出的凭证文件。其总体思路是：在<span lang="EN-US">EXCEL</span>表格中事先定义好科目等相关信息，然后利用<span lang="EN-US">VBA</span>编程输出到硬盘上某文件夹下形成<span lang="EN-US">TXT</span>凭证文件，最后利用总账工具将该<span lang="EN-US">TXT</span>文件引入到总账中即完成凭证的填制工作。其具体操作步骤是：</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly;"><span lang="EN-US"><p>&nbsp;</p></span></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level1 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">1、<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp; </span></span></span>首先在<span lang="EN-US">D</span>盘下新建一个文件夹名字为<span lang="EN-US">my</span>，<span lang="EN-US"><span style="mso-tab-count: 1;">&nbsp; </span></span></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level1 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">2、<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp; </span></span></span>将数据区与凭证区中数据建立关系。（具体看模板）</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">①<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>在“<span lang="EN-US">A2</span>”单元格输入制单日期，也可通过在其他方便操作的单元格录入后取数到该单元格，如该单元格为空则取当前计算机系统日期。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">②<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>在“<span lang="EN-US">B2</span>”单元格输入凭证类别编码。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="COLOR: windowtext; mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">③<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span><span style="COLOR: windowtext;">在“<span lang="EN-US">C2</span>”单元格输入凭证号，可固定设置为<span lang="EN-US">999</span>，由凭证引入时系统自动编号。<span lang="EN-US"><p></p></span></span></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">④<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>在“<span lang="EN-US">E2</span>”单元格输入凭证各分录的默认摘要。若要定义各分录的摘要，可在<span lang="EN-US">E</span>列对应行中输入。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">⑤<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>在“<span lang="EN-US">B1</span>”单元格中输入凭证总分录数，即定义的最大行数。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level1 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">3、<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp; </span></span></span>生成<span lang="EN-US">TXT</span>凭证文件并引入凭证。</p><p style="MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; tab-stops: 45.0pt;"><span lang="EN-US"><span style="mso-tab-count: 1;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span>打开<span lang="EN-US">EXCEL</span>文件，启用宏，就会发现在<span lang="EN-US">EXCEL</span>帮助菜单后新增了“用友转账”和“查看凭证”两个按钮，我们就可据此输出凭证文件后再引入至<span lang="EN-US">U8</span>系统中了。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">①<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>每次将自编程序中的工资分配表的数据进行选择性粘贴覆盖到数据区后，即可按“用友转账”按钮，在弹出窗口（见图<span lang="EN-US">4</span>）中点击确定即可输出凭证文件到“<span lang="EN-US">D</span>：<span lang="EN-US">\My \yy.txt”</span>中 。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; tab-stops: 45.0pt; mso-char-indent-count: 2.0;"><span lang="EN-US"><shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" opreferrelative="t" ospt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path oconnecttype="rect" gradientshapeok="t" oextrusionok="f"></path><lock aspectratio="t" vext="edit"></lock></shapetype><shape id="_x0000_i1025" type="#_x0000_t75" style="WIDTH: 138.75pt; HEIGHT: 84.75pt;"><imagedata otitle="" src="file:///C:\DOCUME~1\yinle\LOCALS~1\Temp\msohtml1\01\clip_image001.png"></imagedata></shape><span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><shape id="_x0000_i1026" type="#_x0000_t75" style="WIDTH: 138.75pt; HEIGHT: 87.75pt;"><imagedata otitle="" src="file:///C:\DOCUME~1\yinle\LOCALS~1\Temp\msohtml1\01\clip_image003.png"></imagedata></shape></span></p><p style="TEXT-JUSTIFY: inter-ideograph; MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 72pt; LINE-HEIGHT: 15pt; TEXT-ALIGN: justify; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 6.0;">（图<span lang="EN-US">4</span>）<span lang="EN-US"><span style="mso-tab-count: 3;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span>（图<span lang="EN-US">5</span>）</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">②<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>系统根据定义的凭证自动检查借贷方金额是否平衡并弹出提示窗（如图<span lang="EN-US">5</span>），如果金额不平，则不能引入。点击确定后即弹出总账工具窗口。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 45pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; mso-list: l0 level2 lfo1; tab-stops: 45.0pt;"><span lang="EN-US" style="mso-bidi-font-family: 宋体;"><span style="mso-list: Ignore;">③<span style="FONT: 7pt &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp; </span></span></span>在总账工具窗的<span lang="EN-US">[</span>数据源<span lang="EN-US">-</span>文件数据<span lang="EN-US">]</span>中参照出“<span lang="EN-US">D:\My \yy.txt”</span>文件，然后在<span lang="EN-US">[</span>目的数据<span lang="EN-US">]</span>中登录需引入账套后（见图<span lang="EN-US">6</span>），双击“凭证引入”按钮即可实现。</p><p align="center" style="MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: 15pt; TEXT-ALIGN: center; mso-line-height-rule: exactly; tab-stops: 45.0pt;"><wrapblock><shape id="_x0000_s1026" type="#_x0000_t75" style="MARGIN-TOP: 0.35pt; Z-INDEX: 1; LEFT: 0px; MARGIN-LEFT: -0.15pt; WIDTH: 415.5pt; POSITION: absolute; HEIGHT: 286.5pt; TEXT-ALIGN: left;"><imagedata otitle="" src="file:///C:\DOCUME~1\yinle\LOCALS~1\Temp\msohtml1\01\clip_image005.png"></imagedata><wrap type="topAndBottom"></wrap></shape></wrapblock><br clear="all" style="mso-ignore: vglayout;"/>（图<span lang="EN-US">6</span>）</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 2.0;">以上所举工资分配表只是一个实例，现实中诸如材料领用分配、折旧分配、购买月票等有一定规律性，能够用固定表格形式表示出来的数据均可用此方法来引入，举一翻三，相信大家能够大大地提高准确率与工作效率的。</p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 2.0;"><span lang="EN-US"><p>&nbsp;</p></span></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 2.0;"><span lang="EN-US"><p>&nbsp;</p></span></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 2.0;"><span lang="EN-US"><p>&nbsp;</p></span></p><p style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 24pt; LINE-HEIGHT: 15pt; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 2.0;"><span lang="EN-US"><p>&nbsp;</p></span></p><p align="center" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 153pt; LINE-HEIGHT: 15pt; TEXT-ALIGN: center; mso-line-height-rule: exactly; tab-stops: 45.0pt; mso-char-indent-count: 12.75;"><span lang="EN-US"><p>&nbsp;</p></span></p>

zangma

我原来下载的这里免的导入程序产生的数据格式不符合用友的格式，之后我对宏程序进行的相应的修改后，导入凭证很顺利的。大大节省的我的时间。只需要电子表格的之间的复制粘贴就行了。可以完全摆脱以前的大量手工输入了。

abcd321abcd

学习了

447448

<p><strong>请问zangma朋友，你是用的什么程序导入用友软件的？能帮助一下吗？</strong></p><p><strong><a href="mailto:447448@163.com">447448@163.com</a></strong></p>

447448

<strong>请问zangma朋友，有导入EXCEL格式的凭证到用友的程序吗？<p><strong><a href="mailto:447448@163.com" target="_blank"><font color="#000000">447448@163.com</font></a></strong></p></strong>

zangma

<p>你下载了后打开表格，菜单上回自动增加一个菜单项，你就可以用了</p>

罗朝阳

帮做一个用友通系列的，谢谢先。

yiqiu_007

学习下，看看情况

uthgavin

我也有KIS的导入工具哦
需要的话，可以分享