vba操作excel - open

 

Function setExcel()

 

    Dim objWBK_EXCEL As Workbook

 

    Dim errMsg As String

    errMsg = ""

 

On Error GoTo Error_Lable

    errMsg = "Open File Error !"

    Set objWBK_EXCEL = Workbooks.Open(ThisWorkbook.Path & "/" & "test.xls")

 

    'delete hidden sheet

    Application.DisplayAlerts = False

    Dim cntSheets As Integer

    cntSheets = objWBK_EXCEL.Sheets.Count

 

    For i = 1 To cntSheets

        If objWBK_EXCEL.Worksheets("Sheet" & i).Visible = False Then

            MsgBox "The hidden sheet will be delete [" & objWBK_EXCEL.Worksheets("Sheet" & i).Name & "]"

            objWBK_EXCEL.Worksheets("Sheet" & i).Delete

            Exit For

        End If

    Next i

 

 

    'Copy new sheet to excel

    errMsg = "Sheet copy Error! !"

    ThisWorkbook.Sheets("NewSheet").Copy After:=Workbooks("test.xls").Sheets(Sheets.Count)

 

 

    'Input name

    'Old name delete

    errMsg = "Old name delete! !"

    ActiveWorkbook.Names("bb").Delete

 

    errMsg = "New name add! !"

    'ActiveWorkbook.Names.Add Name:="aa", RefersToR1C1:="=Sheet2!R1C1:R26C2"

    Workbooks("test.xls").Names.Add Name:="aa", RefersToR1C1:="=NewSheet!R1C1:R26C2"

 

 

    'Input into cell Function

    errMsg = "Input into cell Function! !"

'    cntSheets = objWBK_EXCEL.Sheets.Count

'    For i = 1 To cntSheets

'        If objWBK_EXCEL.Worksheets("Sheet" & i).Visible = True Then

'            objWBK_EXCEL.Worksheets("Sheet" & i).Range("A7").Value = "=SUM(A1:A6)"

'        End If

'    Next i

 

    Dim objSheet As Worksheet

    For Each objSheet In objWBK_EXCEL.Worksheets

        If objSheet.Visible = True Then

            objSheet.Range("A7").Value = "=SUM(A1:A6)"

        End If

    Next objSheet

 

 

    'Hidden this sheet before save

    Workbooks("test.xls").Sheets("NewSheet").Visible = False

 

    'end save excel

    Workbooks("test.xls").Close SaveChanges:=True

 

 

    Exit Function

 

 

Error_Lable:

 

    MsgBox errMsg

 

End Function