09 - VBA常用代码汇总（02）

1 Tools

1.1 关闭用户表单

Unload Me

1.2 终止下面操作

Exit Sub

1.3 字符串拼接

str1 & str2

1.4 保留路径的空格字符

filePath= "C:\User\Desktop\Test Data\a.txt" 
filePath = """" & filePath & """"

1.5 去掉换行符

fileDir = Replace(fileDir, vbCrLf, "")

1.6 Excel操作

1.6.1 绝对路径

currentFilePath = Application.ActiveWorkbook.FullName 

1.6.2 SheetName

currentSheet = ActiveSheet.Name

1.6.3 Save

ActiveWorkbook.Save

1.6.4 某个Sheet某个单元格的值

sheetName = 'Sheet1'
ThisWorkbook.Sheets(sheetName).Range("A1").Value 

2 数据初始化

Private Sub UserForm_Initialize()
	# ann: 这里面放置开启VB时需要做的初始化操作
    Text1.Text = ThisWorkbook.Sheets(sheetName).Range("B1").Value
    Text2.Text = ThisWorkbook.Sheets(sheetName).Range("B2").Value
    Text3.Text = ThisWorkbook.Sheets(sheetName).Range("B3").Value
    Text4.Text = ThisWorkbook.Sheets(sheetName).Range("B4").Value
End Sub

3 执行脚本

Public Function runProcess(cmd As String) As String
	# cmd：要执行的脚本代码
    Dim oShell As Object
    Dim oExec As Object, oOutput As Object
    Dim s As String, sLine As String
    Set oShell = VBA.CreateObject("Wscript.Shell")
    Set oExec = oShell.Exec(cmd)
    Set oOutput = oExec.StdOut
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbNewLine
    Wend
    Set oOutput = Nothing: Set oExec = Nothing
    Set oShell = Nothing
    runProcess = s
End Function

4 关闭某个正在打开的文件

Public Function closeFile() As String
	# close: 关闭文件
    Dim wb As Workbook
    For Each wb In Workbooks
        'MsgBox (wb.Name)
        Dim fileName AS String
        If wb.Name = fileName Then
            Workbooks(wb.Name).Close SaveChanges:=True
        End If
    Next
End Function

5 文件另存

Public Function renameWipReport(fileDir As String) As String
'
'将sourceFileName文件另存为targetFileName
'
    Dim wb As Workbook, pstr As String
    # 去掉字符串中的换行符
    fileDir = Replace(fileDir, vbCrLf, "")
    pstr = fileDir & sourceFileName
    'ActiveWorkbook.SaveCopyAs Filename:=y
    Set wb = Workbooks.Open(pstr)
    wb.SaveAs Filename:=fileDir + targetFileName, FileFormat:=xlCSV, CreateBackup:=False
    wb.Close False
    Kill pstr
End Function

6 选择文件

Private Sub SelectFile_Click()
    Dim Dia1 As Object, Strr As String, PPath As String
    Set Dia1 = Application.FileDialog(msoFileDialogFilePicker)
    With Dia1
        .AllowMultiSelect = False '限制只能同时选择一个文件
        .Filters.Clear
        # fileType: 这里面的文件类型为xlsx，可以添加多个文件类型
        .Filters.Add "所有文件", "*.xlsx", 1 '限制显示的文件类型
        .Show
        For Each vrtSelectedItem In .SelectedItems
            PPath = vrtSelectedItem
        Next
    End With
    # TODO:这里可以将所选的文件路径赋值给别的Object
    ChangeFilePath.Value = PPath
End Sub

7 选择文件夹

Private Sub SelectPythonMain_Click()
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "请选择目录"
        If .Show Then
         	# TODO:这里可以将所选的文件夹路径赋值给别的Object
            Me.pythonMain.Value = .SelectedItems(1)
        End If
    End With
End Sub