Excel VBA打开文件目录

WinXP系统下使用VBA打开文件并拷贝内容至新的工作表

Sub Get_data()

    Dim objDialog As Object, blnFile As Boolean, strLoadFile As String, filename As String
    Set objDialog = CreateObject("UserAccounts.CommonDialog")
    objDialog.Filter = "Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*"
    blnFile = objDialog.ShowOpen
    If blnFile Then
        strLoadFile = objDialog.filename
        filename = CreateObject("Scripting.FileSystemObject").GetFileName(strLoadFile)
        Workbooks.Open filename:=strLoadFile
        Sheets("源数据").Select
        ActiveSheet.UsedRange.Select
        Selection.Copy
        Windows("统计模板.xlsm").Activate
        Sheets("目的数据").Select
        Range("A1").Select
        ActiveSheet.Paste
        Windows(filename).Activate
        ActiveWindow.Close
    End If
    Set objDialog = Nothing
    Sheets("首页").Select
End Sub