VBA 如何使用变量文件名引用活动文件

Question

我必须将数据从导出 excel 文件(export (1).xlsx(导入到我的基本文件中(Productlist.xlsx(。 因此，此在线应用程序生成一个名为export (1).xlsx我们不存储这个导出文件，我们只是点击"Productlist.xlsx"中的导入按钮，数据被导入到工作表 4 上，然后我们关闭我们的"export (1).xlsx"。

只要文件名恰好"export (1).xlsx"，代码就可以工作。 但是当我进行第二次导出时，文件名被更改为"export (2) (1).xlsx"一种解决方案是关闭应用程序并重新开始，但这令人沮丧......

我正在寻找一种使用通配符引用此导出文件的方法，以替换文件名中的变量部分... 例如Application.Goto Workbooks("Export *.XLSX").Sheets(1).Range("A2:AX500")

我已经看到线程使用通配符在存储目录中查找文件，但到目前为止还没有线程引用活动工作簿......

这是我的代码：

Sub ImportOvWeb()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim PasteStart As Range
Dim sPath As String, sFile As String
Set wb1 = ActiveWorkbook
Set PasteStart = Sheets(4).Range("A2")
wb1.Sheets(4).Unprotect
' Delete all entrees from previous import
Application.Goto (wb1.Sheets(4).Range("A2:AX500"))
Selection.ClearContents
Range("A2").Select
' Import data from 'export.xlsm'
Application.Goto Workbooks("Export (1).XLSX"").Sheets(1).Range("A2:AX500")
Selection.Copy
Application.Goto PasteStart
ActiveSheet.Paste
Range("A2").Select   
End Sub

谢谢

汤姆

Answer 1

您可以使用函数遍历打开的工作簿，查找名为">export"的工作簿：

Sub ImportOvWeb()
Dim wbSrc As Workbook
Dim wbDest As Workbook
Dim shtPaste As Worksheet
Dim sPath As String, sFile As String
Set wbDest = ActiveWorkbook
Set shtPaste = wbDest.Sheets(4)
shtPaste.Unprotect
shtPaste.Range("A2:AX500").ClearContents
Set wbSrc = FindByName("export") '<< find a matching workbook
If Not wbSrc Is Nothing Then
'got a match  - copy the data
wbSrc.Sheets(1).Range("A2:AX500").Copy shtPaste.Range("A2")
Else  
MsgBox "Export workbook not found!" 
End if
End Sub
'Find the first workbook with a name containing "nm"
'Returns Nothing if no match
Function FindByName(nm As String) As Workbook
Dim wb 
For Each wb in Workbooks
If wb.name like "*" & nm & "*" Then
Set FindByName = wb
Exit For
End If
Next wb
End Function    

Answer 2

这就是我会这样做的，我已经从您不使用的变量和冗余行中清除了您的代码：

Option Explicit
Sub ImportOvWeb()
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
' Delete all entrees from previous import
With wb1.Sheets(4)
.Unprotect
.Range("A2:AX500").ClearContents
End With
'look for the file
Dim wb As Workbook
Dim wb2 As Workbook
For Each wb In Application.Workbooks
'if any of the workbooks is called export will set it to the wb2 variable
If wb.Name Like "*export*" Then
Set wb2 = wb
Exit For
Else
MsgBox "there is no export file openned. Please try again."
End
End If
Next wb
' Import data from 'export.xlsm'
wb2.Sheets(1).Range("A2:AX500").Copy wb1.Sheets(4).Range("A2")
End Sub