|
楼主 |
发表于 2022-3-31 17:12
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- function Main() {
- //选择文件夹方式查询
- //将查询选择的文件夹下面的所有xls表格
- //对数据源格式要求
- //访问WPS.PUB查看代码详解
- Application.ScreenUpdating = true;
- Application.ScreenUpdating = false;
- const Driver = "{Microsoft Excel Driver (*.xls)}" //ODBC驱动,所以如果你没有驱动,该程序运行将失败
- let mypath = Application.FileDialog(msoFileDialogFolderPicker);
- let files
- let T = 0;
- let QTnum1 = ThisWorkbook.Connections.Count
- mypath.Filters.Clear();
- mypath.Filters.Add("All files", "*.*");
- if(mypath.Show() == -1) {
- mypath = mypath.SelectedItems.Item(1);
- mypath = mypath.replace("\","\\\");
- (obj=>{
- obj.LookIn = mypath;
- obj.SearchSubFolders = false;
- obj.FileName = "*.xls";
- obj.LastModified=msoLastModifiedAnyTime
- obj.Execute(msoSortByFileName,msoSortOrderAscending);
- files = obj.FoundFiles;
- })(Application.FileSearch)
- let r = Range("A65536").End(xlUp).Offset(1).Row;
- Rows("2:"+r).Delete()
- //计时开始
- let ST =new Date().getTime();
- //暂定每个表格中都用同一个格式(1部门,2部门,三部门
- let TableDB = Array("1部门","2部门","3部门");
- //后续需要更改
- let querystr = ThisWorkbook.Sheets(1).Range("B1").Value2
- let connstring=""; //连接字符
- let SQL = []; //存储SQL语句
- let WBDBS = "" //跨表字符串"[EXCEL 8.0;DataBase=xxxx.xls;]."
- let N = 1; //计数。1-49(总49)
- let QTadd = "" //查询
- for(let i=1;i<files.Count+1;i++){
- let filePath = files(i).replace(/\\/g,"\\\");
- let filenames = files(i).split("\");
- let filename = filenames[filenames.length-1].split(".")[0]
- //粗糙的做法。不能识别xxx.xxx.xls
- if(N==1){
- connstring = "ODBC;Driver="+Driver+";DBQ="+filePath+";ReadOnly=0;FIL=excel 8.0;";
- }
- for(let v of TableDB){
- WBDBS = N<=TableDB.length?"":"[EXCEL 8.0;DataBase="+filePath+";]."
- SQL[N-1] = "SELECT [姓名],[客户号],[档案编号],'"+v+"' as [部门],'"+filename+"' as [文件名] FROM "+WBDBS+"["+v+"$A3:F100] WHERE [姓名]='"+querystr+"'"
- N++;
- }
- if(N%49==0){
- //执行查询,重置计数器
- sqlstr = SQL.join(" UNION ALL ");
- r = Range("A65536").End(xlUp).Offset(1).Row;
- QTadd = ThisWorkbook.Sheets.Item(1).QueryTables.Add(connstring,Range("A65536").End(xlUp).Offset(1),sqlstr);
- QTadd.Refresh();
- //ThisWorkbook.Connections.Item(QTnum+1).Delete()
- if(r>2){Rows(r).Delete()}
- N=1;
- SQL = [];
- sqlstr = "";
- WBDBS="";
- QTadd = "";
- }
- }//遍历所有文件
- if(SQL.length>0){
- //执行查询
- sqlstr = SQL.join(" UNION ALL ");
- r = Range("A65536").End(xlUp).Offset(1).Row;
- QTadd = ThisWorkbook.Sheets.Item(1).QueryTables.Add(connstring,Range("A65536").End(xlUp).Offset(1),sqlstr);
- QTadd.Refresh();
- //ThisWorkbook.Connections.Item(QTnum+1).Delete()
- //已知问题。会导致保存失败WPS崩溃
- if(r>2){Rows(r).Delete()}
- N=1;
- SQL = [];
- sqlstr = "";
- WBDBS="";
- QTadd = "";
-
- }
- T = new Date().getTime()-ST;
- }
- MsgBox("查询完毕!\n用时:"+T+"ms",jsOKOnly,"JSA多表联查-WPSPUB")
- Application.ScreenUpdating = true;
- let QTnum2 = ThisWorkbook.Connections.Count
- //该循环避免了下次打开时提示外部数据方法。但在Excel中打开依然存在无效
- //该循环是已知问题的修复办法
- for(let i=QTnum2;i>QTnum1;i--){
- ThisWorkbook.Connections.Item(i).Delete()
- }
- }
-
复制代码
方便手机用户观看代码 |
|