VBA文件操作
2024-08-29 11:16:47
做这些东西主要是为了，实现，我们的最终目标。
查到 两个大表里面的变化数据。
所以 这次
①实现了 文件操作的一部分内容。
包括，excel的打开。分四个步骤。
1、路径
2、打开工作博
3、操作
4、关闭工作簿
②路径里面 包括 文件 是否 存在 的判断逻辑，如果 文件不存在，要记得 终止函数。
③以及 for 循环的 书写。还有 如果 到达了 查找目的以后，退出 for循环。
布局的创建。
判断文件存在的效果图
最终结果图
Sub 矩形1_Click()
'
' 矩形1_Click Macro
' Dim strPath1 As String
Dim strPath2 As String
Dim strFilename1 As String
Dim strFilename2 As String
Dim strFile1 As String
Dim strFile2 As String strPath1 = Cells(, )
strPath2 = Cells(, )
strFilename1 = Cells(, )
strFilename2 = Cells(, ) strFile1 = strPath1 & "/" & strFilename1
strFile2 = strPath2 & "/" & strFilename2 If Dir(strFile1) = "" Then
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is NOT exist!"
Exit Sub
Else
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is exist!"
End If End Sub ===============================step Sub 矩形1_Click()
'
' 矩形1_Click Macro
' Dim strPath1 As String
Dim strPath2 As String
Dim strFilename1 As String
Dim strFilename2 As String
Dim strFile1 As String
Dim strFile2 As String strPath1 = Cells(, )
strPath2 = Cells(, )
strFilename1 = Cells(, )
strFilename2 = Cells(, ) strFile1 = strPath1 & "/" & strFilename1
strFile2 = strPath2 & "/" & strFilename2 If Dir(strFile1) = "" Then
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is NOT exist!"
Exit Sub
Else
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is exist!"
End If '判定文件是否 存在 2 Dim excel As Object
Dim sheet As Object
Dim Workbook As Object Set excel = CreateObject("excel.application")
Set Workbook = excel.Workbooks.Open(strFile1)
Set sheet = Workbook.ActiveSheet Cells(, ) = sheet.Cells(, ) End Sub =============================step Sub 矩形1_Click()
'
' 矩形1_Click Macro
' Dim strPath1 As String
Dim strPath2 As String
Dim strFilename1 As String
Dim strFilename2 As String
Dim strFile1 As String
Dim strFile2 As String strPath1 = Cells(, )
strPath2 = Cells(, )
strFilename1 = Cells(, )
strFilename2 = Cells(, ) strFile1 = strPath1 & "/" & strFilename1
strFile2 = strPath2 & "/" & strFilename2 If Dir(strFile1) = "" Then
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is NOT exist!"
Exit Sub
Else
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is exist!"
End If '判定文件是否 存在 2 Dim excel As Object
Dim sheet As Object
Dim Workbook As Object Set excel = CreateObject("excel.application")
Set Workbook = excel.Workbooks.Open(strFile1)
Set sheet = Workbook.ActiveSheet Cells(, ) = sheet.Cells(, ) '查找项目
Dim changedItemCol As Integer For changedItemCol = To
If sheet.Cells(, changedItemCol) = "列4" Then
Exit For
End If
Next changedItemCol Cells(, ) =
Cells(, ) = changedItemCol End Sub
================================================step Sub 矩形1_Click()
'
' 矩形1_Click Macro
'
' getFile
Dim strPath1 As String
Dim strPath2 As String
Dim strFilename1 As String
Dim strFilename2 As String
Dim strFile1 As String
Dim strFile2 As String strPath1 = Cells(, )
strPath2 = Cells(, )
strFilename1 = Cells(, )
strFilename2 = Cells(, ) strFile1 = strPath1 & "/" & strFilename1
strFile2 = strPath2 & "/" & strFilename2 If Dir(strFile1) = "" Then
MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is NOT exist!"
Exit Sub
'Else
' MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is exist!"
End If If Dir(strFile2) = "" Then
MsgBox "The target file :" & vbCrLf & strFile2 & vbCrLf & "is NOT exist!"
Exit Sub
'Else
' MsgBox "The target file :" & vbCrLf & strFile1 & vbCrLf & "is exist!"
End If 'get workbook and sheet Dim excel1 As Object
Dim sheet1 As Object
Dim Workbook1 As Object Set excel1 = CreateObject("excel.application")
Set Workbook1 = excel1.Workbooks.Open(strFile1)
Set sheet1 = Workbook1.ActiveSheet Dim excel2 As Object
Dim sheet2 As Object
Dim Workbook2 As Object Set excel2 = CreateObject("excel.application")
Set Workbook2 = excel2.Workbooks.Open(strFile2)
Set sheet2 = Workbook2.ActiveSheet 'find Item
Dim changedItemName As String
Dim itemName1 As String
Dim itemName2 As String itemName1 = Cells(, )
itemName2 = Cells(, )
changedItemName = Cells(, )
'循环一次，判定多次好，还是 循环多次判定一次好。。。
'现在我就想吃饭，无所谓了。。。反正也不是什么大体量的工 作。不用纠结这个 Dim col As Integer
'变更前
For col = To
If sheet1.Cells(, col) = itemName1 Then
Exit For
End If
Next col Cells(, ) = itemName1
Cells(, ) =
Cells(, ) = col For col = To
If sheet1.Cells(, col) = itemName2 Then
Exit For
End If
Next col Cells(, ) = itemName1
Cells(, ) =
Cells(, ) = col For col = To
If sheet1.Cells(, col) = changedItemName Then
Exit For
End If
Next col Cells(, ) = changedItemName
Cells(, ) =
Cells(, ) = col '变更后
For col = To
If sheet2.Cells(, col) = itemName1 Then
Exit For
End If
Next col Cells(, ) = itemName1
Cells(, ) =
Cells(, ) = col For col = To
If sheet2.Cells(, col) = itemName2 Then
Exit For
End If
Next col Cells(, ) = itemName1
Cells(, ) =
Cells(, ) = col For col = To
If sheet2.Cells(, col) = changedItemName Then
Exit For
End If
Next col Cells(, ) = changedItemName
Cells(, ) =
Cells(, ) = col
Set sheet1 = Nothing
Set sheet2 = Nothing
Set Workbook1 = Nothing
Set Workbook2 = Nothing
Set excel1 = Nothing
Set excel2 = Nothing
End Sub
参考文献
最新文章
- wordpress 分类相关
- innodb的锁
- 使用<c:if>标签处理页面数据
- SqlServer：传递超长字符串参数时，参数被自动换行。
- Nhibernate中 Many-To-One 中lazy="proxy" 延迟不起作用的原因
- [Effective C++ --026]尽可能延后变量定义式的出现时间
- malloc函数的底层实现你是否清楚
- 脚本录制--html模式和url模式
- jeecg自定义datagrid查询
- Linux系统KVM虚拟化技术
- 知识点：Mysql 基本用法之存储过程
- NetCore入门篇：（五）Net Core项目使用静态文件
- 显示图片中CDC和HDC问题
- CSS 属性的推荐书写顺序
- 随机获取图片的api接口
- volume不能挂载mysql permission denied问题
- code forces 994B
- GCD(st表+二分)
- Qt — tableWidget插入复选框
- 51nod1412(dp)