 上传我的文档
 上传文档
 下载
 收藏
粉丝量：38
所发布文档来源于互联网和个人收集，仅用于分享交流使用，版权为原作者所有。
 下载此文档
用VBA提取Word表格数据到Excel
下载积分：100
内容提示：用VBA提取Word表格数据到Excel
文档格式：DOC|
浏览次数：1275|
上传日期： 09:29:14|
文档星级：
全文阅读已结束，如果下载本文需要使用
 100 积分
下载此文档
该用户还上传了这些文档
用VBA提取Word表格数据到Excel
关注微信公众号用Excel VBA解决数据提取问题
我的图书馆
用Excel VBA解决数据提取问题
他有1个工作簿，里面有100个工作表。他希望把每个工作表里的第6行的b6:i6数据复制到一起，也就是说他要把工作表里的第6行，汇总在一起。比如这样：他说如果你没办法，那他只能手动来复制100个工作表，提取这数据了。我想了下说，我用VBA编程试试。差不多10分钟折腾，我把代码搞定了。Public Sub tiqu()'指定行遍历工作表提取Application.ScreenUpdating = FalseDim LastRow As IntegerDim sh As WorksheetSet sh = Worksheets.Add '新建工作表Set sh = ActiveSheet'为当前工作表sh.Name = 'sheet1'Sheets(3).Range('b5:i5').Copy Destination:=Sheets('sheet1').Range('b1') '表头Sheets(3).Range('a6:i6').Copy Destination:=Sheets('sheet1').Range('a2') '复制For i = 4 To Sheets.Count '遍历工作表LastRow = Sheets('sheet1').Cells(Rows.Count, 1).End(xlUp).Row '判定最后非空行Sheets(i).Range('a6:i6').Copy Destination:=Sheets('sheet1').Range('a' & LastRow).Offset(1, 0) '复制到特定非空行的下一行NextMsgBox '处理完毕'Application.ScreenUpdating = TrueEnd Sub他按alt F11,把代码贴入模块，点运行就行了。就是要他的结果。我来解释下我的思路原理：我的解决方案。遍历工作表，提取每个工作表的第6行，复制到sheet1里的A2开始，难点1是偏移，用offset。难点2，判断非空的最后一行，用en那个100个工作表，1分钟内运行完。如果复制粘贴100次，大家试下就知道要多久了，起码我肯定你的手已经废掉了，哈哈。End.作者：Sharpen（中国统计网特邀认证作者）本文为中国统计网原创文章，需要转载请联系中国统计网（小编微信：itongjilove），转载时请注明作者及出处，并保留本文链接。
推一荐：&&|&&
喜欢该文的人也喜欢WPS论坛表格好帖推荐
利用Excel VBA从数据库中提取及计算生成报表用的基础数据
一键分享：
通过分析大量报表的设计特点和规律提出了一种用Excel生成电力报表的解决方案该方案利 用ExcelVBA从数据库中提取及计算生成报表用的基础数据放入到Excel的工作表中作为基础数据层报表 管理人员可按报表需要从数据中间层中引用基础数据生成所需的各种报表 在软件开发设计报表时通常是采用报表设 计器或者使用第三方产品来完成在数据库前端开发工具中一般都提供了相应的报表生成工具 如在VFP中的ReportDesigner在VB和.NET中的 CrystalReport在Delphi中的QuickReport在Power- Builder中的数据窗口(DataWindow)等用户通过 使用这些报表工具可以方便灵活地定义一个规 则的报表然而在定义不规则报表时这些工具 并不很方便特别是国内的复杂报表往往需要大 量填充相应的单元格1对电力生产企业的报表 数据量比较大报表的数据和格式经常需要变动 需要用户自由定义如果使用上述语言通过程序 编写的报表限制的较死报表管理人员想简单 改报表的样式很困难并且生成的报表是打印的 纸制报表不能生成电子格式的报表因此设计 一种面向用户的通用报表对用户来说是非常必要 的用户在使用阶段能够根据自己的要求对报表 的格式和内容进行调整修改新增和删除的报 表这种报表大大加强了报表实现的灵活性和用 户的交互性能够更好地满足用户的报表需求23 利用ExcelVBA从数据库中提取数据并计算 生成报表用的基础数据放入到Excel的某一工作 表中作为基础数据层报表管理人员可按报表需 要从基础数据层中引用基础数据生成所需的报表 可以完美地解决生成电力复杂报表的问题本文 提出利用Excel设计电力企业的日报月报和年报 表系统的方法能够快速生成日报及各种月报和 年报管理员可以随时修改报表的内容及格式快 速打印报表还可保存电子文档供发邮件及日后 查阅1报表关键技术的实现 电力日报月报和年报用来统计线路的供电 量机组的发电量及机组的出力数据水文数据及 部分数据的去年同期多年同期的数据比较值等 生成的报表数据要发布到企业内部生产信息网或 利用短信发送报表中的部分数据到部分主管领导 手机上要求前端供发电量及机组出力数据水文 数据等实时采集系统采集完数据并入库后就能 自动生成日报还可人工重新生成日报按要求生 成月报年报所以要求制作报表的Excel文档有 简单的输入界面能让报表管理员方便选择日期 生成当日的报表还要求在每天零时过后能自动 生成前一天的日报表所以需要有日历控件和定 时器的功能本文选用WindowsXP系统Office 2003数据库是Sybase数据库1.1日历控件 在Excel文档中用一工作表制作简单的输入 界面做为主界面在主界面中要有日历控件方便 报表管理员选择日期在主界面中形成日历控件 的方法如下4在主界面里要选择视图工具栏 控件工具箱在弹出的控件工具箱的其他控件中 选日历控件11.0,拖到主页面上显示出日历控件 调整好控件大小这个日历控件就可以使用如图 1所示1.2定时器功能 由于日报要对外发布所以要求每日零时以 后能自动生成前一天的日报所以就需要一个定 时器的功能除了用于控制在日历控件右边动态 地1s更新一次显示系统的当前时间还要在当 天的零时过后去启动自动日报生成报表的Excel 文档要一直处在运行状态该ExcelVBA要具有定 时器的功能'窗口与消息API声明PrivateDeclareFunctionSetTimerLib&user32.dll&(ByValhwndAsLong,_ByValnIDEventAsLong,ByValuElapseAsLong,_ByVallpTimerFuncAsLong)AsLong'API函数用于创建一个计时器PrivateDeclareFunctionKillTimerLib&user32.dll&(ByValhwndAsLong,_ByValnIDEventAsLong)AsLong'API函数用于销毁一个计时器PublicDeclareFunctionFindWindowLib&user32.dll&Alias&FindWindowA&_(ByVallpClassNameAsString,ByVallpWindowNameAsString)AsLong'API函数寻找窗口列表中第一个符合指定条件的顶级窗口PublichMainAsLong'主窗口句柄在定时模块中新建一个子过程enableTimer其功能是调用API函数SetTimer创建一个定时器具体代码如下SubenableTimer()SetTimerhMain,,AddressOfOnTimeEndSub其中hMain是与计时器联系的窗口句柄1001是一个计时器的标识符1000是指定的超时值在定时模块中新建一个子过程DisableTimer该子过程其功能是调用API函数KillTimer销毁一个定时器具体代码如下SubDisableTimer()KillTimerhMain,1001'销毁一个计时器EndSub在VBA工程中设计一登录窗体要求输入用户名和密码若用户名和密码正确后按确定钮后启动定时器界面如图2所示确定按键对应的代码如下PrivateSubConfirm_Button_Click()regSys'验证用户名和密码子过程IfbLogin=TrueThen'如果用户名和密码正确hMain=FindWindow(vbNullString,Me.Caption)'获取窗口句柄enableTimer'调用子过程设置一个计时器Me.Hide'隐藏登录窗口EndIfEndSub生成定时器的方法如下通过API函数控制子 过程定时执行的方法使用API之前首先要对其 进行声明在VBA工程中要新建一定时模块定 时模块的声明部分进行API函数的声明代码如 下登录窗口启动后就会启动定时器在定时模 块中新建一个子过程OnTime其功能是更新主 窗口的显示时间和每天零时后待数据库中做日报 的电量数据和水文数据的数据记录齐全后自动生 成日报1.3与Sybase数据库建立连接 Excel与Sybase数据库使用ADO存取数据通 过ODBC方法访问ODBC是一种标准的低级接 口通过它可以访问不同的数据库当然也可以访 问Sybase数据库具体方法是在Excel的开发语言 VBA中使用ActiveXDataObject(ADO),通过ADO 连接ODBC数据源进而连接到Sybase数据库5 Excel的开发语言是VBA使用ADO时要先 引用MicrosoftActiveXDataObject库这样就可在 VBA中使用ADO对象利用ADO的Connection对 象就可以连接ODBC数据源进而连接Sybas数据 库最后利用ADO对象操作Sybase数据库6 使用ADO对象编程访问数据库一般按以下 步骤编写代码Step1使用Connection对象建立数据源 Step2使用Recordset对象打开记录集对象 Step3使用Recordset对象Field对象调用记 图1日报主界面窗口 图2登录窗口界面 3Excel213 录集中的数据 Step4使用Connection对象中断连接 在VBA中要使用ADO访问数据库首先要将 ADO对象引入到当前工程中选择菜单工具/引 用命令系统打开引用对话框如图3所示在 列表框中选择MicrosoftActiveXDataObjects2.5 Library&,然后单击确定按钮在VBA中就可以 使用ADO对象模型 在VBA工程中建一数据库连接模块模块中 和Sybase数据库连接的代码如下 PublicSubConn_Connection() DimStr_ConnectionAsString SetConn=NewADODB.Connection Str_Connection=&provider=MSDASQL;DRIVER={Sybase System11};SRVR=QDC_DATA;_ DSN=;DB=hydragen_& Str_Connection=Str_Connection+&UID=PWD= wxw123;PERSISRSECURITY_INFO=F WorkGrounds2=24;& Conn.ConnectionTimeout=15 Conn.OpenStr_Connection Conn.CursorLocation=adUseClient '一定要加此游标在本地 IfConn.State=0Then CallClose_Connection MsgBox&数据服务器连接不成功:& EndIf EndSub 在验证用户名和密码的子过程中调用此过程 就和Sybase建立起连接其中的QDC_DATA是做 报表机器上的ODBC数据源名hydragen_prod是 Sybase的数据库名 2报表数据的提取及生成 以日报表为例在数据库的各个数据表中通 过SQL提取日报所需的各种数据FunctionGet_DataSw 是提取日报中部分的水文数据函数如 PublicFunctionGet_DataSw(ByValCZIDAsInteger,ByVal dWaterDateAsDate)AsQDC_SWDATA_TYPE DimlRsetAsNewADODB.Recordset DimiYearAsInteger DimiMonthAsInteger DimiDayAsInteger DimswDataAsQDC_SWDATA_TYPE'水文数据结构 DimdWaterDateQAsDate IflRset.State=1ThenlRset.Close sqlstr=&select*fromday_reportwheregg_date&='&& Format(dWaterDate,&yyyy-mm-dd&)&&'& sqlstr=sqlstr+&andgg_date&'&&Format(dWaterDate +1,&yyyy-mm-dd&)&&'& sqlstr=sqlstr+&andczid=&&CZID&&orderbygg_date& lRset.Opensqlstr,Conn,adOpenKeyset,adLockOptimistic IflRset.RecordCount&=1Then IfNotIsNull(lRset(&shang_0&).Value)Then swData.sy0sw=lRset(&shang_0&).Value IfNotIsNull(lRset(&shang_1&).Value)Then swData.sy8sw=lRset(&shang_1&).Value IfNotIsNull(lRset(&ruliu_1_P&).Value)Then swData.rrk=Int(lRset(&ruliu_1_P&).Value) swData.result=1 Else swData.result=-1 EndIf Get_DataSw=swData EndFunction 函数SubGet_DataH是把取出的数据写入到 Excel中的工作表Data中的部分单元格中,如 PublicSubGet_DataH(ByValMdateAsDate) '取隔河岩去年本日水文数据 GhySwData=Get_DataSw(2,dWater_Date) '水文数据结构 IfGhySwData.result=1Then .Range(&H28&)=GhySwData.sy0sw '隔去年同期零时上游水位 .Range(&H29&)=GhySwData.sy8sw '隔去年同期8时上游水位 .Range(&H30&)=GhySwData.rrk '隔去年同期日均入库流量 Else Show_TS&没有取到隔河岩去年同期日水文信息& EndIf EndSub 所有取出及计算的报表用到的基础数据都写 入Data的单元格中日报表样在Excel中工作表 日报中日报中的数据除公式外都是从工作表 Data中引用的工作表Data如图4所示日报中 的单元格H32中的数据引用的公式为=Data!H28 图3MicrosoftActiveXDataObjects2.5Library引用 H33中的数据引用的公式为=Data!H29H34中的 数据引用的公式为=Data!H30日报表如图5所示 生成日报过程中若得到的数据不合理要在 主界面的提示窗口中用文字显示出来报表管理 员可对Data中的数据进行修改包括加入备注汉 字修改后的结果能回存到数据库中的日报数据 表对应记录以后要查询日报数据就以库中人工 修改后的数据为准除非管理员人工确定重新生 成当日的日报生成的日报见图5新建1个Excel文档,在生成的日报工作表名 字位置点右鼠标健选择复制或移动工作表就可把 生成的日报表移到新建的文档中去报表中的数 据不依赖于引用的基础数据这个日报数据文档 可用来保存及对外发送月报表的实现也和日报 表类似只是中间数据按每个电厂数据放到sheet 的某1个工作表中所有的月报表表样事先制作 好后月报中的数据从各个基础数据表中引用报 表管理员可随时修改表样及报表中的数据3结束语 利用Excel直接在客户端从数据库中提取数 据作为基础数据层放在Excel工作表中各式报表 先制作好表样表中的数据从基础数据层的工作 表中引用这种方式制作表样也比高级语言画复 杂表样方式要容易特别是多张不同样式报表中 的重复数据较多的报表实现起来更容易用户还 可以随时按需要进行修改能满足电力企业报表 的需要.查看: 5958|回复: 1
如何使用VBA取消同一工作簿中所有工作表的数据筛选？
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
网站名称: Excel技巧网 | Excel专家栖息谷 | 微软中文技术社区合作伙伴站内采编:
署名作者: wise
版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
适用版本: 03以前版本&
语言环境: 简体中文
学习方法: 掌握Excel技巧的关键是动手操作
下载 ≠ 知识
免费注册成为本站会员，享用更多功能，结识更多Office办公高手！
才可以下载或查看，没有帐号？
Q:如何使用VBA取消同一工作簿中所有工作表的数据筛选？
A:ALT+F11→插入模块→模块中输入以下代码：
Sub test()
&&Dim i As Long
&&For i = 1 To Sheets.Count
& & '循环所有工作表
& &&&If Sheets(i).AutoFilterMode Then
& &&&'如果工作表上显示有“自动筛选”下拉箭头，即筛选已打开
& && &&&Sheets(i).Cells.AutoFilter
& && & '则取消单元格的数据筛选
& &&&End If
&&Next i
End Sub复制代码
魅力值 +10
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
先通过Sheets(i).AutoFilterMode返回是否自动筛选模式，然后取消筛选。
站长推荐 /1
40集公式入门动画教程，一天带你打好公式的基础。
Excel技巧网的会员探讨问题仅代表其个人意见，与网站的立场无关。任何违反国家和地方相关法律法规的言论，本站有义务协助政府相关部门追究发言者的责任！
本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者，本站之外任何单位或个人未经允许，不得将其用于商业用途。
若非原文作者，任何形式的非商业性转载必须获得Excel技巧网或作者允许，并注明作者和出处。
会员发表的帖子如涉及版权纠纷，须自行负责。详情请参考注册时的网站服务条款。
本站特聘法律顾问：沈学律师
Powered by拒绝访问 | www.ggdoc.com | 百度云加速
请打开cookies.
此网站 (www.ggdoc.com) 的管理员禁止了您的访问。原因是您的访问包含了非浏览器特征(4513864bcbb343ef-ua98).
重新安装浏览器，或使用别的浏览器}