当前位置: 首页 > news >正文

VBA 批量处理Excel文件

目录

  • 一. 批量创建Excel文件
    • 1.1 VBA的方式
    • 1.2 Powershell方式
  • 二. 批量删除文件
  • 三. 批量重命名文件
  • 四. 合并多个Excel数据到一个Excel文件中


一. 批量创建Excel文件

1.1 VBA的方式

Sub CreateFiles()Dim strPath As String, strFileName As StringDim i As Long, rDim pathSeparator As StringOn Error Resume Next' 用户选择文件夹路径With Application.FileDialog(msoFileDialogFolderPicker)' 如果用户未选择文件夹则退出程序If .Show ThenstrPath = .SelectedItems(1)ElseExit SubEnd IfEnd With' 给路径添加分隔符pathSeparator = Application.pathSeparatorIf Right(strPath, 1) <> pathSeparator ThenstrPath = strPath & pathSeparatorEnd If' 取消屏幕刷新Application.ScreenUpdating = False' 取消警告提示,当有重名工作簿时直接覆盖Application.DisplayAlerts = False' 数据装入数组rr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)' 标题不要,因此从第2个元素开始遍历数组rFor i = 2 To UBound(r)' 新建工作簿With Workbooks.Add' 以指定名称、默认文件类型保存工作簿.SaveAs strPath & r(i, 1), xlWorkbookDefault' 关闭工作簿.Close TrueEnd WithNextApplication.ScreenUpdating = TrueApplication.DisplayAlerts = TrueMsgBox "Excel批量创建完成。"
End Sub

1.2 Powershell方式

# 指定要创建的文件数量
$excelCount = 5
# 指定文件名前缀
$fileNamePrefix = "Excel文件"# 循环创建指定数量的 Excel 文件
1..$excelCount | ForEach-Object {# 设置文件名,这里使用 .xlsx 格式$fileName = "${fileNamePrefix}_$_.xlsx" # 获取文件路径$filePath = Join-Path -Path $PWD -ChildPath $fileName  # 创建 Excel 工作簿并保存$excel = New-Object -ComObject Excel.Application$workbook = $excel.Workbooks.Add()$workbook.SaveAs($filePath)$workbook.Close()$excel.Quit()<#用来显式释放 Excel COM 对象的资源,以确保在脚本执行完成后,释放 Excel 进程和相关资源,避免资源泄漏和占用问题。使用 Out-Null 可以将输出结果丢弃,避免将释放对象的消息输出到控制台。#>[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-NullWrite-Host "文件: ${fileName} ===> 创建完成!"  # 输出已创建的文件名
}

二. 批量删除文件

⏹获取指定文件夹下的文件

  • Range("A:B").Clear: k = 1
    • ::冒号在 VBA 中用来分隔两条语句,表示同时执行两个操作。
  • strFileName = Dir
    • 获取下一个文件的文件名,通过这个操作,实现了遍历文件夹中的所有文件。
Sub GetFiles()Dim strPath As StringDim strFileName As String, k As LongWith Application.FileDialog(msoFileDialogFolderPicker)' 获取用户选择的文件夹的路径,如果未选取,则退出程序If .Show Then strPath = .SelectedItems(1)ElseExit SubEnd IfEnd WithApplication.ScreenUpdating = FalseIf Right(strPath, 1) <> "\" Then strPath = strPath & "\"End If' 清除A:B列的所有数据Range("A:B").Clear: k = 1' 向A1,B1列填入数据Cells(1, 1) = "旧文件名": Cells(1, 2) = "是否删除"' 获取指定路径下的文件(通配符获取首个文件名)strFileName = Dir(strPath & "*.xls*")Do While strFileName <> ""k = k + 1Cells(k, 1) = strPath & strFileName' 第2次调用Dir函数,未使用任何参数,则获取同目录下的下一个文件名strFileName = DirLoopApplication.DisplayAlerts = TrueEnd Sub

⏹删除文件

  • Set dataRange = Range("A1").CurrentRegion
    • 用于获取指定单元格的当前区域的语法。
    • 返回一个表示当前区域的 Range 对象,该区域是从指定单元格开始向右和向下延伸到包含数据的边界。
    • 在此案例中,数据结构如下
    [["旧文件名", "是否删除"],["文件路径1", "删除"],["文件路径2", "删除"]......
    ]
    
  • Dir(dataRange(i, 1)) <> ""
    • Dir 是一个 VBA 中用于操作文件系统的函数。
    • 主要用于检查文件或目录是否存在,以及获取目录中的文件和子目录列表。
  • Kill dataRange(i, 1):VBA 中用于删除文件或目录的语句。它可以用来删除指定路径下的文件或目录。
Sub DeleteFile()Dim dataRange As RangeDim i As Long' 数据装入数组Set dataRange = Range("A1").CurrentRegion' 标题行不要,从数组第二行开始遍历For i = 2 To dataRange.Rows.Count' 如果第2列为删除,并且要删除的文件存在的话,才会执行删除命令If dataRange(i, 2) = "删除" And Dir(dataRange(i, 1)) <> "" Then' Kill语句删除指定文件Kill dataRange(i, 1)End IfNextMsgBox "批量删除完成!"
End Sub

三. 批量重命名文件

  • Name r(i, 1) As r(i, 2):将A2单元格中的文件,重命名为B2单元格中的文件名
    • 在 VBA 中,Name 关键字用于重命名文件或文件夹。
    • Name "文件绝对路径1" As "文件绝对路径2"
Sub ChangeFileName()Dim r, i As Long' 数据装入数组r = Range("A1").CurrentRegion ' 标题行不要,从数组第二行开始遍历For i = 2 To UBound(r)' Name语句重命名Name r(i, 1) As r(i, 2) NextMsgBox "文件批量重命名完成!"End Sub

四. 合并多个Excel数据到一个Excel文件中

  • Val(InputBox("请输入标题的行数,默认标题行数为1", "提醒", 1))
    • Val函数可以将数字字符串转换为数字
  • With GetObject(strPath & strFileName)
    • 只读形式读取文件时,使用getobject会比workbooks.open稍快
  • Exit Do:跳出本次Do while循环,相当于continue的效果。
  • .Range("A1:B1") = Array("来源工作簿名称", "来源工作表名称"):同时向A1,B1单元格赋值。
  • IIf(nTitleRow = 0, 1, 0)IIf(条件, 真时返回的值, 假时返回的值)
  • InStr(1, shtData.Name, strKey, vbTextCompare):InStr函数,用于在一个字符串中查找另一个字符串,并返回第一个匹配的位置。
    • 1:指定搜索的起始位置,这里是从字符串的第一个字符开始搜索。
    • shtData.Name:待被搜索的字符串。
    • strKey:要查找的子字符串(从shtData.Name中查找strKey)。
    • vbTextCompare:指定比较方式,这里使用文本比较,表示不区分大小写进行比较。
Sub CollectWorkBookDatas()Dim shtActive As Worksheet, rng As Range, shtData As WorksheetDim nTitleRow As Long, k As Long, nLastRow As LongDim i As Long, j As Long, nStartRow As LongDim aData, aResult, nStarRng As LongDim strPath As String, strFileName As StringDim strKey As String, nShtCount As Long' 获取用户选择的文件夹路径With Application.FileDialog(msoFileDialogFolderPicker)If .Show Then strPath = .SelectedItems(1)ElseExit SubEnd IfEnd With' /_/_/_/_/_/_/_/_/获取用户输入的数据Start/_/_/_/_/_/_/_/_/strKey = InputBox("请输入需要合并的工作表所包含的关键词:" & vbCrLf & "如未填写关键词,则默认汇总全部表格数据", "提醒")' 如果点击了取消或者关闭按钮,则退出程序If StrPtr(strKey) = 0 ThenExit SubEnd IfnTitleRow = Val(InputBox("请输入标题的行数,默认标题行数为1", "提醒", 1))If nTitleRow < 0 ThenMsgBox "标题行数不能为负数。", 64, "警告": Exit SubEnd If
' /_/_/_/_/_/_/_/_/获取用户输入的数据End/_/_/_/_/_/_/_/_/Set shtActive = ActiveSheetWith Application' Excel 的屏幕刷新设置为 False' 在执行后续操作时将不会看到屏幕上的更新,可以加快代码执行速度。.ScreenUpdating = False' Excel 的显示警告设置为 False]' 在执行后续操作时将不会显示警告框,比如保存文件时的覆盖提示等。.DisplayAlerts = False' Excel 的更新链接时询问设置为 False' 在打开包含链接的工作簿时将不会询问是否要更新链接。.AskToUpdateLinks = FalseEnd With' 声明结果数组ReDim aResult(1 To 80000, 1 To 1)' 清空当前表格数据Cells.ClearContents ' 设置单元格为文本格式Cells.NumberFormat = "@"' 补全路径If Right(strPath, 1) <> "\" ThenstrPath = strPath & "\"End If' 使用Dir函数遍历excel文件strFileName = Dir(strPath & "*.xls*") Do While strFileName <> ""' 避免同名文件重复打开出错If strFileName = ThisWorkbook.Name Then ' 继续下一个excel文件strFileName = Dir ' 跳出本次While循环Exit Do End If' 以只读形式读取文件时,使用getobject会比workbooks.open稍快With GetObject(strPath & strFileName)' 遍历Excel中的各sheet页For Each shtData In .Worksheets ' 如果表中包含关键字则进行汇总(不区分关键词字母大小写)If InStr(1, shtData.Name, strKey, vbTextCompare) Then' 获取sheet页中的使用区域Set rng = shtData.UsedRange' 判断工作表是否存在数据If rng.Count > 1 Then' 汇总工作表的数量nShtCount = nShtCount + 1 ' 判断遍历数据源是否应该扣掉标题行nStartRow = IIf(nShtCount = 1, 1, nTitleRow + 1)' 数据区域读入数组arraData = rng.Value' 动态调整结果数组brr的最大列数If UBound(aData, 2) + 2 > UBound(aResult, 2) ThenReDim Preserve aResult(1 To UBound(aResult), 1 To UBound(aData, 2) + 2)End If' 遍历行For i = nStartRow To UBound(aData)k = k + 1' 数组第一列放工作簿名称aResult(k, 1) = strFileName' 数组第二列放工作表名称aResult(k, 2) = shtData.Name' 遍历列For j = 1 To UBound(aData, 2)aResult(k, j + 2) = aData(i, j)Next' 如果数据行数到达结果数组的上限,则将数据导入汇总表,并清空结果数组If k > UBound(aResult) - 1 ThenWith shtActive' 获取放置来源数据的位置nLastRow = .Cells(Rows.Count, 1).End(xlUp).Row ' 判断是否扣除标题行If nLastRow = 1 ThennStarRng = IIf(nTitleRow = 0, 1, 0).Range("A1").Offset(nStarRng).Resize(k, UBound(aResult, 2)) = aResult' 前两列放来源工作簿和工作表名称.Range("A1:B1") = Array("来源工作簿名称", "来源工作表名称")Else' 放结果数组的数据.Range("A1").Offset(nLastRow).Resize(k, UBound(aResult, 2)) = aResultEnd IfEnd Withk = 0' 重新设置结果数组ReDim aResult(1 To UBound(aResult), 1 To UBound(aResult, 2))End IfNextEnd IfEnd IfNext.Close False '关闭工作簿End With' 继续下一个excel文件strFileName = DirLoopIf k > 0 Then' 激活汇总表shtActive.Select ' 放置数据的位置nLastRow = Cells(Rows.Count, 1).End(xlUp).Row' 如果汇总表数据为空,说明需要汇总的数据没有超过结果数组的上限If nLastRow = 1 ThennStarRng = IIf(nTitleRow = 0, 1, 0)Range("a1").Offset(nStarRng).Resize(k, UBound(aResult, 2)) = aResultRange("a1:b1") = Array("来源工作簿名称", "来源工作表名称")ElseRange("a1").Offset(nLastRow).Resize(k, UBound(aResult, 2)) = aResultEnd IfEnd If' 更新Excel的设置With Application.ScreenUpdating = True.DisplayAlerts = True.AskToUpdateLinks = TrueEnd WithMsgBox "一共汇总完成。" & nShtCount & "个工作表!", , "提示"End Sub

相关文章:

VBA 批量处理Excel文件

目录 一. 批量创建Excel文件1.1 VBA的方式1.2 Powershell方式 二. 批量删除文件三. 批量重命名文件四. 合并多个Excel数据到一个Excel文件中 一. 批量创建Excel文件 1.1 VBA的方式 Sub CreateFiles()Dim strPath As String, strFileName As StringDim i As Long, rDim pathSe…...

PG实例连接访问控制

实例访问控制可以控制来自于不同主机&#xff0c;不同用户是否允许访问指定的数据库&#xff0c;以及验证方式。 与oracle中的连接管理器的功能相同&#xff0c;之前有写过一篇oracleCMAN连接管理器的配置实操&#xff1a; 配置oracle连接管理器&#xff08;cman&#xff09;…...

2024-05-07 商业分析-如何在社会层面做一个更好的工具人-记录

摘要: 2024-05-07 商业分析-如何成为一个靠谱的工具人 如何在社会层面做一个更好的工具人 那么今天讲的这个主题呢&#xff0c;对吧&#xff1f;你们一看啊&#xff0c;就觉得这个就不应该我讲是吧啊&#xff0c;但是呢这个逻辑呢我还得跟你们讲一下啊&#xff0c;就是如何成为…...

C++设计模式-创建型设计模式

设计模式 设计模式是什么 设计模式是指在软件开发中&#xff0c;经过验证的&#xff0c;用于解决在特定环境下&#xff0c;重复出现的&#xff0c;特定问题的解决方案&#xff1b;其实就是解决问题的固定套路。但是要慎用设计模式&#xff0c;有一定的工程代码量之后用它比较…...

code-server容器webpack的ws无法连接解决方法

TLDR 通过指定client的wsrul去连接ws devServer.client.webSocketURL ‘wss://<Forwarded uri>/ws’ 拓扑 1、code-server: 用于编写代码、启动webpack dev-server 服务&#xff1b;[https://<domain>:8001] 2、webpack: 用于浏览dev-server服务&#xff1b;[ht…...

leetcode47-Permutations II

分析 给定一个可包含重复数字的序列 nums &#xff0c;按任意顺序 返回所有不重复的全排列。 示例 1&#xff1a; 输入&#xff1a;nums [1,1,2] 输出&#xff1a; [[1,1,2], [1,2,1], [2,1,1]] 题目 由于元素是重复的&#xff0c;要求返回不重复的&#xff0c;所以一定会有…...

246 基于matlab的交流电机动态方程

基于matlab的交流电机动态方程&#xff0c;用于交流电机动态分析。输入电机的额定功率(kW)、电机的额定转速(r/min)、转子外径(m)、铁心长(m)转子槽数、电机极对数 等参数&#xff0c;输出转速变化、力矩变化等结果。程序已调通&#xff0c;可直接运行。 246 交流电机动态 转速…...

7天入门Android开发之第2天——四大组件之活动

一、活动是什么 活动&#xff08;Activity&#xff09;是 Android 应用程序中的一个重要组件&#xff0c;它代表用户界面上的单个窗口&#xff0c;通常会填充整个屏幕。通过活动&#xff0c;可以创建各种各样的用户界面&#xff0c;并控制界面的行为。活动可以包含各种 UI 元素…...

自然语言(NLP)

It’s time for us to learn how to analyse natural language documents, using Natural Language Processing (NLP). We’ll be focusing on the Hugging Face ecosystem, especially the Transformers library, and the vast collection of pretrained NLP models. Our proj…...

学习java第六十天

Advice的类型&#xff1a; &#xff08;1&#xff09;前置通知&#xff08;Before Advice&#xff09;&#xff1a;在连接点&#xff08;Join point&#xff09;之前执行的通知。 &#xff08;2&#xff09;后置通知&#xff08;After Advice&#xff09;&#xff1a;当连接点退…...

OpenFeign修改HttpClient为Apache HttpClient 5

OpenFeign中http client 如果不做特殊配置&#xff0c;OpenFeign默认使用JDK自带的HttpURLConnection发送HTTP请求&#xff0c; 由于默认HttpURLConnection没有连接池、性能和效率比较低。所以修改为Apache HttpClient 5。 总结为两步&#xff1a; 加依赖改yml 具体操作请往…...

【busybox记录】【shell指令】comm

目录 内容来源&#xff1a; 【GUN】【comm】指令介绍 【busybox】【comm】指令介绍 【linux】【comm】指令介绍 使用示例&#xff1a; 逐行比较两个排序后的文件 - 默认输出 逐行比较两个排序后的文件 - 如果一个文件的排序有问题&#xff0c;那么反错&#xff08;默认&…...

工作中遇到的问题,如何解决的

1. gorm update 一条记录的某个字段后&#xff0c;立刻&#xff08;1ms&#xff09;select这条记录&#xff0c;会有读取不到最新结果的情况&#xff1a; transaction已经提交&#xff0c;数据最后也是更新的。 猜测原因&#xff1a;MySQL没能及时把那条很大的record“刷盘”到…...

数据结构(c):队列

目录 &#x1f37a;0.前言 1.什么是队列 2. 队列的实现 2.1定义队列节点 2.2定义队列 2.3队尾入队列 2.4判断队列是否为空 2.5队头出队列 2.6 队列首元素 2.7队尾元素 2.8队列内的元素个数 2.9销毁队列 3.试运行 &#x1f48e;4.结束语 &#x1f37a;0.前言 言C之…...

Vue单页面应用和多页面应用的区别

概念&#xff1a; SPA单页面应用&#xff08;SinglePage Web Application&#xff09;&#xff0c;指只有一个主页面的应用&#xff0c;一开始只需要加载一次js、css等相关资源。所有内容都包含在主页面&#xff0c;对每一个功能模块组件化。单页应用跳转&#xff0c;就是切换…...

php扩展

查看扩展: print_r(get_loaded_extensions());判断扩展: if (!extension_loaded(gd)) {if (!dl...

mac电脑如何安装python及环境搭建

&#xff08;1&#xff09;进入官网&#xff1a;Download Python | Python.org&#xff0c;根据自己电脑选择python (2)这里我选择的是mac,点击&#xff1a;macos&#xff0c;选择最近版本并点击进入 (3)选择mac版本&#xff1a; (4)点击就可以进入下载&#xff1a; (5)下载好之…...

大数据高级阶段面试题(实时)

1.Kafka的producer如何实现幂等性? ①开启幂等性&#xff0c;将Idempotent设置为true ②将ack设置为-1&#xff0c;确保相同的消息只会发送一次&#xff0c;避免重新发送 2.Kafka的ISR和OSR的作⽤分别是什么? ISR是副本和领导者的数据和状态要保持一致&#xff0c;如果出现…...

Material Studio 计算分子静电力、电荷密度以及差分电荷密度

1.先打开Material Studio导入要计算的分子cif文件或者mol文件&#xff0c;直接Flie-Import 2.高斯几何优化一下结构&#xff0c;参数按照我的设置就行&#xff0c;一般通用&#xff0c;后面出问题再调整 3.点完Run后会跳出很多计算过程&#xff0c;不用管&#xff0c;等他计算完…...

华为鸿蒙系统(Huawei HarmonyOS)

华为鸿蒙系统&#xff08;华为技术有限公司开发的分布式操作系统&#xff09; 华为鸿蒙系统&#xff08;HUAWEI HarmonyOS&#xff09;&#xff0c;是华为公司在2019年8月9日于东莞举行的华为开发者大会&#xff08;HDC.2019&#xff09;上正式发布的分布式操作系统。 华为鸿蒙…...

【C语言练习】080. 使用C语言实现简单的数据库操作

080. 使用C语言实现简单的数据库操作 080. 使用C语言实现简单的数据库操作使用原生APIODBC接口第三方库ORM框架文件模拟1. 安装SQLite2. 示例代码:使用SQLite创建数据库、表和插入数据3. 编译和运行4. 示例运行输出:5. 注意事项6. 总结080. 使用C语言实现简单的数据库操作 在…...

Android15默认授权浮窗权限

我们经常有那种需求&#xff0c;客户需要定制的apk集成在ROM中&#xff0c;并且默认授予其【显示在其他应用的上层】权限&#xff0c;也就是我们常说的浮窗权限&#xff0c;那么我们就可以通过以下方法在wms、ams等系统服务的systemReady()方法中调用即可实现预置应用默认授权浮…...

用docker来安装部署freeswitch记录

今天刚才测试一个callcenter的项目&#xff0c;所以尝试安装freeswitch 1、使用轩辕镜像 - 中国开发者首选的专业 Docker 镜像加速服务平台 编辑下面/etc/docker/daemon.json文件为 {"registry-mirrors": ["https://docker.xuanyuan.me"] }同时可以进入轩…...

css3笔记 (1) 自用

outline: none 用于移除元素获得焦点时默认的轮廓线 broder:0 用于移除边框 font-size&#xff1a;0 用于设置字体不显示 list-style: none 消除<li> 标签默认样式 margin: xx auto 版心居中 width:100% 通栏 vertical-align 作用于行内元素 / 表格单元格&#xff…...

在 Spring Boot 中使用 JSP

jsp&#xff1f; 好多年没用了。重新整一下 还费了点时间&#xff0c;记录一下。 项目结构&#xff1a; pom: <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0" xmlns:xsi"http://ww…...

渗透实战PortSwigger靶场:lab13存储型DOM XSS详解

进来是需要留言的&#xff0c;先用做简单的 html 标签测试 发现面的</h1>不见了 数据包中找到了一个loadCommentsWithVulnerableEscapeHtml.js 他是把用户输入的<>进行 html 编码&#xff0c;输入的<>当成字符串处理回显到页面中&#xff0c;看来只是把用户输…...

2.3 物理层设备

在这个视频中&#xff0c;我们要学习工作在物理层的两种网络设备&#xff0c;分别是中继器和集线器。首先来看中继器。在计算机网络中两个节点之间&#xff0c;需要通过物理传输媒体或者说物理传输介质进行连接。像同轴电缆、双绞线就是典型的传输介质&#xff0c;假设A节点要给…...

解析“道作为序位生成器”的核心原理

解析“道作为序位生成器”的核心原理 以下完整展开道函数的零点调控机制&#xff0c;重点解析"道作为序位生成器"的核心原理与实现框架&#xff1a; 一、道函数的零点调控机制 1. 道作为序位生成器 道在认知坐标系$(x_{\text{物}}, y_{\text{意}}, z_{\text{文}}…...

Python爬虫实战:研究Restkit库相关技术

1. 引言 1.1 研究背景与意义 在当今信息爆炸的时代,互联网上存在着海量的有价值数据。如何高效地采集这些数据并将其应用于实际业务中,成为了许多企业和开发者关注的焦点。网络爬虫技术作为一种自动化的数据采集工具,可以帮助我们从网页中提取所需的信息。而 RESTful API …...

轻量级Docker管理工具Docker Switchboard

简介 什么是 Docker Switchboard &#xff1f; Docker Switchboard 是一个轻量级的 Web 应用程序&#xff0c;用于管理 Docker 容器。它提供了一个干净、用户友好的界面来启动、停止和监控主机上运行的容器&#xff0c;使其成为本地开发、家庭实验室或小型服务器设置的理想选择…...