Excel使用宏操作另一个Excel文件

Admin | 2007-10-16 21:59:37 | 被阅次数 | 13383

温馨提示!

如果未能解决您的问题，请点击搜索；登陆可复制文章，点击登陆

      #使用宏在一个Excel文件中操作另一个Excel文件。
     办公软件中使用宏处理一些信息，尤其是大量的重复信息，可以提高效率。

     操作过程结构如下：

   Dim strFilePath As String
    Dim strFileName As String
    Dim strFile As String
    Dim excel As Object
    Dim sheet As Object
    Dim Workbook As Object

    'The target file
    strFileName = "test_out.xls"
    strFilePath = Me.Parent.Path
    strFile = strFilePath & "\" & strFileName

    'STEP 0: proof the existence of excel file
    If Dir(strFile) = "" Then
        MsgBox "The target file:" & vbCrLf & _
                strFile & vbCrLf & _
                "is NOT exsit!"
        Exit Sub
    End If

    'STEP 1: open the excel file
    Set excel = CreateObject("excel.application")
    Set Workbook = excel.Workbooks.Open(strFile)

    'STEP 2: find the needed sheet
    Set sheet = Workbook.ActiveSheet

    'STEP 3: process
    MsgBox sheet.Range("a1").Value

    sheet.Range("a2").Value = "sunyt"

    'STEP 4: close file
    '      : to save file firstly
    Workbook.Save
    Workbook.Close
    excel.Quit
    Set sheet = Nothing
    Set Workbook = Nothing
    Set excel = Nothing