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

巴特西

VBA方法总结

最新文章

热门文章