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