我如何在VBA中创建新的excel文件/工作簿而不自动打开它

Question

我创建了一个子例程，用于根据输入字符串(新文件名)创建一个新的Excel工作簿。如下:

Private Sub AddSaveAsNewWorkbook(FILE As String)
    Dim path As String
    path = FILE & ".xlsx"
    Set Wk = Workbooks.Add
    With Wk
        .SaveAs Filename:=path
    End With
End Sub

但是，我使用这个子例程生成了超过40,000个新文件。如果程序每次都打开这些文件，我的电脑就会崩溃。有没有办法做到这一点，而不是自动打开Excel文件?

谢谢。

Answer 1

如果您计划创建40,000新文件，那么.Add和.SaveAs将减慢您的PC速度。既然你不想打开它，那么我建议你使用ACE。下面是一个简单的例子:

(基于VB。

Sub Sample()
    Dim cn As New ADODB.Connection, cmd As New ADODB.Command
    Dim FilePath As String, sFileName As String
    Dim i As Long
    
    '~~> Folder to save the files
    FilePath = "C:Temp"
        
    For i = 1 To 40000
        sFileName = FilePath & "File - " & i & ".xlsx"
        
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & sFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            .Open
            
            Set cmd = New ADODB.Command
            cmd.ActiveConnection = cn
            
            '~~> Command to create the table
            cmd.CommandText = "CREATE TABLE Sheet1 (Sno Int, " & _
                                                   "Employee_Name VARCHAR, " & _
                                                   "Company VARCHAR, " & _
                                                   "Date_Of_joining DATE, " & _
                                                   "Stipend DECIMAL, " & _
                                                   "Stocks_Held DECIMAL)"
            '~~> Adding Data
            cmd.CommandText = "INSERT INTO Sheet1 (Sno, Employee_Name, " & _
                                                  "Company,Date_Of_joining,Stipend,Stocks_Held) " & _
                                                  "values ('1', 'Siddharth Rout', 'Defining Horizons', " & _
                                                  "'20/7/2014','2000.75','0.01')"
                              
            cmd.Execute
            
            .Close
        End With
    Next i
End Sub

注:两个cmd。命令文本是Gserg在下面的评论中强调的替代方案。