如何使用工作簿对象VBA选择文件

Question

>我有这样的代码：

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim LastRow As Long
Set wsCopy = Workbooks("file1.xlsm").Worksheets("Order")
Set wsDest = Workbooks("orders.xlsm").Worksheets("All Data")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(0).Row
wsCopy.Range("A1:I9" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)
LastRow = Sheets("All Data").UsedRange.Rows.Count
Sheets("All Data").Range("L" & lDestLastRow).Value = "order made?:"
Sheets("All Data").Range("L" & lDestLastRow + 1).Value = "Yes/No"
Range("L" & lDestLastRow).Font.Bold = True
wsDest.Activate

在我有工作簿("file1.xlsm"(的位置，我想选择文件进行复制。可以使用工作簿或任何其他方法吗？

Answer 1

使用 GetOpenFilename-method， Workbooks.Open 并将字符串替换为变量：

Dim selectedFilename As Variant
selectedFilename = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xl*;*.xm*”)
If selectedFilename <> False Then
  Workbooks.Open FileName:=selectedFilename
End If
Set wsCopy = Workbooks(Mid$(selectedFilename, InStrRev(selectedFilename, "") + 1)).Worksheets("Order")