Excel批量创建和打开工作簿

162 阅读 0 评论 107 点赞

我是靠谱客的博主愉快口红，这篇文章主要介绍Excel批量创建和打开工作簿，现在分享给大家，希望可以做个参考。

批量创建工作簿

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
VB.Net
Sub 批量新建工作簿()
    Dim strcount As String, intcounter As Integer
    Dim mypath As Workbook
    Path = ActiveWorkbook.Path  ' 获取当前工作簿所在的路径
    strcount = InputBox("please input the number of new workbook")
    If IsNumeric(strcount) Then
        For intcounter = 1 To strcount
            Workbooks.Add   ' 创建工作簿
            ActiveWorkbook.SaveAs Path & "" & ActiveWorkbook.Name  '保存工作簿到磁盘
        Next intcounter
    End If
    MsgBox "there are " & strcount & "of workbooks is created"
End Sub

批量打开工作簿

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub 批量打开工作簿()
    Dim wk As Workbook
    myPath = "F:学习资料随书光盘Excel函数与图表应用实例解析02素材" '指定文件存储在这个目录
    myFile = Dir(myPath & "*.xls") '选中所有的xls文件
    
    Do While myFile <> ""
        If myFile = False Then Exit Sub
        If myFile <> ThisWorkbook.Name Then
               Workbooks.Open (myPath & myFile)
        End If
        myFile = Dir
    Loop
End Sub