VBA方法总结

1．取得日文汉字的读音的方法（例如強→キョウ）

    Application.Getphonetic(str)

2．保存Excel文件时不弹出是否保存的alter

    wb.close(false) 

3．提示消息不要

    Application.displayAlter =false

4．Excel的sheet比例的大小调整

    ActiveWindow.Zoom = 70

5. 利用Excel来打开文本文件的方法　　

　　Dim jsFileSheet As Worksheet
    Dim jsWb As Workbook
    Workbooks.OpenText fileName:= _
        jsFileName, Origin:=65001, _
        startRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, TrailingMinusNumbers:=True
    Set jsWb = ActiveWorkbook
    Set jsFileSheet = jsWb.Sheets(jsFunName)

6. 在Excel中查找字符的位置 

Dim rngStart
Set rngStart = jsFileSheet.Cells.Find(keyWord)
Dim addr
addr = rngStart.Address
Do
    
    Set rngStart = jsFileSheet.Cells.FindNext(rngStart)
Loop Until addr = rngStart.Address

7. 删除指定的文件的方法

Sub deleteFiles(fileName As String)
    Dim fs As Object
    Set fs = CreateObject("scripting.filesystemobject")
    fs.DeleteFile fileName
End Sub

8. 删除指定文件夹里面的所有文件，不包括文件夹

Sub deleteFiles(path As String)
    
    Dim fs As Object
    Set fs = CreateObject("scripting.filesystemobject")
    fs.DeleteFile path & "*.*"
End Sub

9. 拷贝文件之后重新命名

Function CreatFile(template As String,path As String, beforeFileName As String, afterFileName As String)

    On Error GoTo lab1
        Dim Fso As Object
        Set Fso = CreateObject("Scripting.FileSystemObject")
        Fso.CopyFile template, path
        Name beforeFileName As afterFileName
        Set Fso = Nothing
        CreatFile = True
        Exit Function
lab1:
    CreatFile = False
End Function

10. 文件存在check

Function IsFileExists(ByVal strFileName As String) As Boolean
    If Dir(strFileName, 16) <> Empty Then
        IsFileExists = True
    Else
        IsFileExists = False
    End If
End Function

　　

　　

 

转载于:https://www.cnblogs.com/killclock048/p/9436353.html