excel - 列出所有打开的 Excel 文件，以便用户可以选择一个文件

Question

我想将数据从旧文件复制到新文件。
我有复制我的数据的代码。旧文件和新文件的名称发生了变化，我每次都必须在我的代码中更改它。
有没有办法，我可以获取打开的 Excel 文件列表并选择我想要数据的文件和我想要将数据复制到的文件？

Sub Copy_rental()

Dim PYFILE As String
Dim CYFILE As String
Dim Columnstocopy As String

PYFILE = "Pro-invest HIE ADL Trust -  2018 WP.XLSX"
CYFILE = "Pro-invest HIE ADL Trust -  2019 WP.XLSX"

Columnstocopy = "C"

Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "10:" & Columnstocopy & "14").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "10").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "16:" & Columnstocopy & "24").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "16").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "26:" & Columnstocopy & "27").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
   
Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "29").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

编辑:感谢提示，我正在使用此代码获取打开的 Excel 文件的列表，但它似乎无法正常工作。 VBA 也没有抛出任何错误。这可能是因为我在personal.xlsb 工作簿上制作了用户表单吗？
newblankworkbook 中的相同代码似乎正在运行。

Private Sub Onesource_copy_data_Initialize()

Dim wkb As Workbook

Me.Onesource_ComboBox1_oldfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox1_oldfile.AddItem wkb.Name
Next wkb

Me.Onesource_ComboBox1_newfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox1_oldfile.AddItem wkb.Name
Next wkb

End Sub

编辑 2:我认为问题是 Onesource_copy_data_Initialize .我应该把它保留为 UserForm_Initialize .

Private Sub UserForm_Initialize()

Dim wkb As Workbook

Me.Onesource_ComboBox_oldfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_oldfile.AddItem wkb.Name
Next wkb

Me.Onesource_ComboBox_newfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_newfile.AddItem wkb.Name
Next wkb

End Sub

EDIT3:我做了一个组合框，它正在工作。
组合框代码

Private Sub Onesource_Copy_Cancel_CommandButton_Click()
Stopped = True
Unload Me
End Sub


Public Sub Onesource_Copy_Start_CommandButton_Click()

PYFILE = Me.Onesource_ComboBox_oldfile.Value
CYFILE = Me.Onesource_ComboBox_newfile.Value
' Calling Macro
Call Test123

Unload Me
End Sub


Private Sub UserForm_Initialize()

Dim wkb As Workbook

Me.Onesource_ComboBox_oldfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_oldfile.AddItem wkb.Name
Next wkb

Me.Onesource_ComboBox_newfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_newfile.AddItem wkb.Name
Next wkb

End Sub

Excel 代码的开始
将变量声明为公开的，因为这是必需的

Public PYFILE As String

Public CYFILE As String

Sub Copy_PreTemplate_to_NewTemplate()
'
' Copy previous Template data to new Template
'
Onesource_Copy_form.Show

End Sub


Sub Test123()

Windows(PYFILE).Activate
Sheets("Non-numeric details").Select
Range("C6:C10").Select
Selection.Copy
Windows(CYFILE).Activate
Sheets("Non-numeric details").Select
Range("C6").Select
ActiveSheet.Paste

End sub

Answer 1

如果工作表不改变位置，您可以使用它们的数字索引而不是工作表名称:

Sheets(0).Select

如果工作表确实改变了位置，则遍历所有工作表以查找某些文本以指示哪个工作表是什么。
否则，请维护可能名称的 map 。

This isn't really a programming problem, it's a problem around the process. A better solution is don't rename the sheets or use a Naming Convention such as sheetName-YYYYMMDD.