当前位置: 首页 > 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;上正式发布的分布式操作系统。 华为鸿蒙…...

变量 varablie 声明- Rust 变量 let mut 声明与 C/C++ 变量声明对比分析

一、变量声明设计&#xff1a;let 与 mut 的哲学解析 Rust 采用 let 声明变量并通过 mut 显式标记可变性&#xff0c;这种设计体现了语言的核心哲学。以下是深度解析&#xff1a; 1.1 设计理念剖析 安全优先原则&#xff1a;默认不可变强制开发者明确声明意图 let x 5; …...

关于iview组件中使用 table , 绑定序号分页后序号从1开始的解决方案

问题描述&#xff1a;iview使用table 中type: "index",分页之后 &#xff0c;索引还是从1开始&#xff0c;试过绑定后台返回数据的id, 这种方法可行&#xff0c;就是后台返回数据的每个页面id都不完全是按照从1开始的升序&#xff0c;因此百度了下&#xff0c;找到了…...

CocosCreator 之 JavaScript/TypeScript和Java的相互交互

引擎版本&#xff1a; 3.8.1 语言&#xff1a; JavaScript/TypeScript、C、Java 环境&#xff1a;Window 参考&#xff1a;Java原生反射机制 您好&#xff0c;我是鹤九日&#xff01; 回顾 在上篇文章中&#xff1a;CocosCreator Android项目接入UnityAds 广告SDK。 我们简单讲…...

UR 协作机器人「三剑客」:精密轻量担当(UR7e)、全能协作主力(UR12e)、重型任务专家(UR15)

UR协作机器人正以其卓越性能在现代制造业自动化中扮演重要角色。UR7e、UR12e和UR15通过创新技术和精准设计满足了不同行业的多样化需求。其中&#xff0c;UR15以其速度、精度及人工智能准备能力成为自动化领域的重要突破。UR7e和UR12e则在负载规格和市场定位上不断优化&#xf…...

JAVA后端开发——多租户

数据隔离是多租户系统中的核心概念&#xff0c;确保一个租户&#xff08;在这个系统中可能是一个公司或一个独立的客户&#xff09;的数据对其他租户是不可见的。在 RuoYi 框架&#xff08;您当前项目所使用的基础框架&#xff09;中&#xff0c;这通常是通过在数据表中增加一个…...

SiFli 52把Imagie图片,Font字体资源放在指定位置,编译成指定img.bin和font.bin的问题

分区配置 (ptab.json) img 属性介绍&#xff1a; img 属性指定分区存放的 image 名称&#xff0c;指定的 image 名称必须是当前工程生成的 binary 。 如果 binary 有多个文件&#xff0c;则以 proj_name:binary_name 格式指定文件名&#xff0c; proj_name 为工程 名&…...

短视频矩阵系统文案创作功能开发实践,定制化开发

在短视频行业迅猛发展的当下&#xff0c;企业和个人创作者为了扩大影响力、提升传播效果&#xff0c;纷纷采用短视频矩阵运营策略&#xff0c;同时管理多个平台、多个账号的内容发布。然而&#xff0c;频繁的文案创作需求让运营者疲于应对&#xff0c;如何高效产出高质量文案成…...

【VLNs篇】07:NavRL—在动态环境中学习安全飞行

项目内容论文标题NavRL: 在动态环境中学习安全飞行 (NavRL: Learning Safe Flight in Dynamic Environments)核心问题解决无人机在包含静态和动态障碍物的复杂环境中进行安全、高效自主导航的挑战&#xff0c;克服传统方法和现有强化学习方法的局限性。核心算法基于近端策略优化…...

【从零学习JVM|第三篇】类的生命周期(高频面试题)

前言&#xff1a; 在Java编程中&#xff0c;类的生命周期是指类从被加载到内存中开始&#xff0c;到被卸载出内存为止的整个过程。了解类的生命周期对于理解Java程序的运行机制以及性能优化非常重要。本文会深入探寻类的生命周期&#xff0c;让读者对此有深刻印象。 目录 ​…...

根目录0xa0属性对应的Ntfs!_SCB中的FileObject是什么时候被建立的----NTFS源代码分析--重要

根目录0xa0属性对应的Ntfs!_SCB中的FileObject是什么时候被建立的 第一部分&#xff1a; 0: kd> g Breakpoint 9 hit Ntfs!ReadIndexBuffer: f7173886 55 push ebp 0: kd> kc # 00 Ntfs!ReadIndexBuffer 01 Ntfs!FindFirstIndexEntry 02 Ntfs!NtfsUpda…...