VBA 整理

最新推荐文章于 2022-09-27 10:57:39 发布

Tyourotou

最新推荐文章于 2022-09-27 10:57:39 发布

阅读量228

点赞数

分类专栏： EXCEL 文章标签： vba

本文链接：https://blog.csdn.net/Tyourotou/article/details/108709727

版权

EXCEL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

VBA 整理

定义数组

	Dim arr As Variant
    arr = Array("1001", "1201", "1571", "1701", "2102")

读取文件

	Dim wk As Workbook
   Dim ws As Worksheet
   Set wk = Application.Workbooks.Open(path)
   Set ws = wk.Worksheets(1)
   '获取sheet最大行数
   maxRow = ws.[A500000].End(xlUp).Row

判断一个文件是否存在

   Set objFileSystem = CreateObject("Scripting.FileSystemObject")
   If objFileSystem.fileExists(path) <> True Then
       MsgBox "文件不存在。"
       Exit Sub
   End If

判断文件夹是否存在，不存在创建

	If Dir(desktopPath & "\文件夹名\", vbDirectory) = "" Then
        MkDir desktopPath & "\文件夹名\"
    End If

判断文件是否存在，存在删除

	Dim newFile As String
	newFile = desktopPath & "\文件夹\" & "文件名.xlsx"
	If objFileSystem.fileExists(newFile) Then
		Kill newFile
	End If

创建一个新EXCEL文件

	Set w = Application.Workbooks.Add
	On Error Resume Next
	w.SaveAs Filename:=newFile
	On Error Resume Next
	Sheets("sheet1").Name = "sheet名"

EXCEL赋值，添加颜色，设置列宽，格式

	'赋值
	Sheets("sheet名").Cells(行号, 列号) = ws.Cells(2, l)
	'添加颜色
	Sheets("sheet名").Cells(行号, 列号).Interior.Color = RGB(135, 231, 173)
	'设置列宽
	Sheets("sheet名").Columns(列号).ColumnWidth = ws.Cells(2, l).ColumnWidth
	'设置格式
	Sheets("sheet名").Cells(newRow, k).NumberFormatLocal = "yyyy/mm/dd h:mm:ss"

EXCEL画格子，设置字体，加粗

	'选取范围
	Sheets("sheet名").Range(Cells(2, 1), Cells(newRow - 1, 119)).Select
	'上线
	Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
	'下线
	Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
	'左线
	Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
	'右线
	Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
	'竖线
	Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
	'横线
	Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
	
	Sheets("sheet名").Range(Cells(1, 1), Cells(newRow, 119)).Select
	'字体
	Selection.Font.Name = "黑体"
	
	Sheets("sheet名").Range(Cells(2, 1), Cells(2, 119)).Select
	'粗体
	Selection.Font.Bold = True

文件选择框

With Application.FileDialog(msoFileDialogFilePicker)
	'单选择
	.AllowMultiSelect = False
	'清除文件过滤器
	.Filters.Clear
	'添加文件过滤后缀
	.Filters.Add "Excel Files", "*.xls;*.xlsx"
	.Filters.Add "All Files", "*.*"
	
	'FileDialog 对象的 Show 方法显示对话框，并且返回 -1（OK）和 0（Cancel）。
	If .Show = -1 Then
		'得到选择的文件
	    Sheet1.Cells(3, 3) = .SelectedItems(1)
	End If
End With

创建并输出txt文件

	'文件的全路径
    MyFName = ThisWorkbook.Path & "/" & filename
    '创建文件
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set myTxt = fso.CreateTextFile(filename:=MyFName, OverWrite:=True)
    '写入文件
    myTxt.Write "写入内容" & vbCrLf
    '关闭文件
    myTxt.Close

关于字符串操作

	'以逗号分割
	tableName = Split(Sheets("sheet名").Name, ",")(1)
	'替换	冒号替换为空
	nowDate = Replace(nowDate, ":", "")
	'判断是否相同 0为相同
	StrComp(Sheet1.Cells(i, 1), "A") = 0