编写一个Excel脚本的一些总结

VBA入门

http://blog.youkuaiyun.com/sunxing007/article/details/5658384

1. alt+F11 vba编程界面


2. Excel cell color and color index map:
#00FF00 4
#CCFFCC 35 
FFFF00 6
FF0000 3
C0C0C0 15
http://dmcritchie.mvps.org/excel/colors.htm


3. when copy sheet to another workbook, the color of some cell is lost. You should first copy the custom color from source workbook to target workbook;
attention: when you change the color, but the color index is not change;
Such as: normally color index is map to #008000; when you changed the stardard color to #CCFFCC, but the color index 
is not change.
http://www.excelbanter.com/showthread.php?t=102323


    ' copy color
    targetWorkbook.Colors = targetWorkbook1.Colors

4. select and open excel file
    Dim NewFN As String
Dim targetWorkbook1 As Workbook
    NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", title:="Please select first file")
    If Len(NewFN) = 0 Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
        Exit Sub
    Else
        Set targetWorkbook1 = Workbooks.Open(Filename:=NewFN, ReadOnly:=False)
    End If

5. the last sheet of excel can not be delete
Sub RemoveAllSheet(ByRef wb As Workbook)
    Application.DisplayAlerts = False
    
    Dim sh As Worksheet
    For Each sh In wb.Worksheets
        If sh.Name <> "Sheet1" Then sh.Delete
    Next
    
    Application.DisplayAlerts = True
End Sub


6. sheet copy
Sub CopyFirtstSheetToTarget(ByRef souce As Workbook, ByRef Target As Workbook)
    Application.DisplayAlerts = False
    
    souce.Sheets(1).Copy after:=Target.Sheets("Sheet1")
    ' rename
    Dim sheetName As String
    sheetName = ActiveSheet.Name
    
    ActiveSheet.Name = souce.Name & "_" & sheetName
    Application.DisplayAlerts = True
End Sub


7. get table range
Sub GetTableRange(ByRef lastTableIndex As Integer)
    Last = ActiveSheet.Underline(Rows.count, "A").End(xlUp).Row
    Dim myInterior As Interior
    For i = TABLE_START_INDEX To Last
       Set myInterior = ActiveSheet.Underline(i, "B").Interior
       If (myInterior.ColorIndex = 5) Then
                lastTableIndex = i
       End If
    Next i
End Sub


8. delete all content
Sub DeleteAllContent()
    ActiveSheet.Underline.Clear
End Sub