ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 521|回复: 22

[求助] 多工作表查询·数据多时能提升速度

[复制链接]

TA的精华主题

TA的得分主题

发表于 2024-4-2 16:24 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
image.jpg

  1. Sub 查询1()
  2.     Dim Wb As Workbook, arr1, arr2(1 To 10000, 1 To 800)
  3.     Dim k%, j%, z%, x%, Zlast%, st$, MyFile$, sel_path$
  4.     Dim objDialog As FileDialog, f, rng As Range
  5.     Range("A2:P" & Rows.Count) = "" '清空原有的数据
  6.     Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
  7.     With objDialog
  8.         .Title = "请选择文件"
  9.         .AllowMultiSelect = True  '允许选择多个文件
  10.         .Filters.Add "Excel文件", "*.xls;*.xlsx", 1
  11.         If .Show = 0 Then
  12.             MsgBox "已取消操作!"
  13.             Exit Sub
  14.          End If
  15.          Application.ScreenUpdating = False '关闭屏幕刷新
  16.     End With
  17.     st = ThisWorkbook.Sheets(1).[P1]
  18.     For Each f In objDialog.SelectedItems
  19.         Set Wb = Workbooks.Open(f)
  20.         With Wb
  21.             For x = 1 To Wb.Sheets.Count '循环打开的工作簿里的工作表
  22.                 arr1 = .Sheets(x).Range("A1").CurrentRegion.Offset(1) '把工作表区域数据装到数组arr1里
  23.                 For j = 1 To UBound(arr1, 1) '循环数组arr1里的行
  24.                     If arr1(j, 3) = st Then '判断是否和查询值相等
  25.                         k = k + 1
  26.                         For z = 1 To 8
  27.                             arr2(k, z) = arr1(j, z) '把数组arr1满足条件装到数组arr2里
  28.                         Next z
  29.                     End If
  30.                  Next j
  31.             Next x
  32.             .Close True '关闭wb工作簿
  33.         End With
  34.     Next
  35.     [A2].Resize(k, 8) = arr2 '把数组arr2读出来
  36.     [P2] = Application.WorksheetFunction.Sum(Range("h2:h40"))
  37.     Application.ScreenUpdating = True '屏幕刷新
  38. End Sub
复制代码
请教群里的前辈,实际我的文件夹每个工作薄数据有上万条信息,
同时也是放在局域网共享文件夹中,查询时就很慢,有没有办法提升一下查询速度。


多工作簿查询.rar

160.64 KB, 下载次数: 14

TA的精华主题

TA的得分主题

发表于 2024-4-2 17:12 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
目前数组感觉速度已经没多少能提升的了,如果真的数据量太大,并原始数据规范统一的话,用SQL查询

TA的精华主题

TA的得分主题

发表于 2024-4-2 17:15 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
把open改为GetObject好像能快一小小小丢丢。。

TA的精华主题

TA的得分主题

发表于 2024-4-2 18:04 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2024-4-2 18:11 | 显示全部楼层
时间基本上耗费在打开工作簿上面,这句“.Close True”改成“.Close False”,看看是否会好一点点。
Python可以实现不打开工作簿读取数据,但是读取的速度也不怎么快。

TA的精华主题

TA的得分主题

发表于 2024-4-2 19:25 来自手机 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
Set Wb = Workbooks.Open(f)

这句费时,建议多表合并后,sql查询

TA的精华主题

TA的得分主题

发表于 2024-4-2 21:46 | 显示全部楼层
本帖最后由 shiruiqiang 于 2024-4-2 21:48 编辑

1、根据表格存放,需要判断f<> thisworkbook,避免误选

image.png

TA的精华主题

TA的得分主题

发表于 2024-4-2 21:59 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 shiruiqiang 于 2024-4-2 22:02 编辑

楼主的vba 代码我这里大概5秒多

image.jpg
sql查询就0.4秒多一点,学习使用了fzxba老师的代码
image.jpg

多工作簿查询.rar

166.19 KB, 下载次数: 15

TA的精华主题

TA的得分主题

发表于 2024-4-2 22:01 | 显示全部楼层
我个人浅薄的认为有2种方法可能会更快:
1. 使用VBA的ADO进行数据查询连接,这里使用的SQL语句也很简单。
2. 使用PowerQuery建立数据查询后加载到工作表中。

TA的精华主题

TA的得分主题

发表于 2024-4-3 07:23 | 显示全部楼层
既然放在共享文件夹,那么假设有多人需要同时查询,这样总会有读文件冲突的问题,快不起来
还是做web接口吧
能想到最简单的做法如图,只是把多张表读到一个列表arr里,然后建一个接口接受name参数,从列表里查出结果返回。。。
使用附件前,需要在powershll里安装importexcel和pode两个模块,然后修改ip地址,然后其他人在vba里也通过这个ip地址查询


动画.gif

多工作簿查询.zip

179.57 KB, 下载次数: 7

评分

2

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

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

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

GMT+8, 2024-4-13 04:52 , Processed in 0.038884 second(s), 13 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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