定义数组
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