通过"Wb“到分局

Question

有没有办法将字符串或其他类型的数据传递给Sub()并基于该字符串定义和打开新的WorkBook？

例如

Sub main()
   Call workbook_generator("Wb1")
End Sub

Sub workbook_generator(name As String)
   Dim name as Workbook
   Set name = Workbooks.Add
End Sub

Answer 1

如果要重用工作簿对象，则必须传递它。我还添加了传递文件名的选项.

Option Explicit

Sub main()

Dim fullfilename As String
fullfilename = "D:\file1.xlsx"

Dim wb As Workbook
workbook_generator wb, fullfilename

wb.Worksheets(1).Name = "testsheet"
End Sub

Public Sub workbook_generator(ByRef wb As Workbook, Optional fullfilename As String)

Set wb = Application.Workbooks.Add
If fullfilename <> vbNullString Then
    'you should put a check here if folder exists
    'or at least an error handler
    wb.SaveAs fullfilename
End If

End Sub