我必须从多个以数字命名的Excel文件（1.xlsx，2.xlsx，3.xlsx等）中复制数据。我写了这个宏。它运行。但是没有任何复制发生，我运行宏的主要工作簿仍然为空。
Sub filecopy()
' The macro is running in the main file, which I saved as .xlsm
' This main.xlsm is in the same folder as the files from which I copy the data
Dim Filename As String, Pathname As String,xx as Double
Activesheet.Usedrange.Clear 'I delete the current contents of the sheet
Pathname = ActiveWorkbook.Path
Filename = Dir(Pathname & "*.xlsx")
xx = 1 'the first column where the contents of the first file goes
Do While Len(Filename) > 0
Cells(1, xx).Formula = "='[" & Filename & "]Sheet1'!A1"
Cells(2, xx).Formula = "='[" & Filename & "]Sheet1'!B2"
Cells(3, xx).Formula = "='[" & Filename & "]Sheet1'!C3"
xx = xx + 1 'next file next column
Filename = Dir()
Loop
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value 'every formula goes to value
MsgBox "Work Complete", vbInformation
End Sub
您的代码中有2个错误：
1.\缺少->filename为空
替换Filename = Dir(Pathname & "*.xlsx")为Filename = Dir(Pathname & "\*.xlsx")
2.公式不正确->文件名不完整
更改您的公式，例如Cells(1, xx).Formula = "='[" & Filename & "]Sheet1'!A1"与此Cells(1, xx).Formula = "='" & Pathname & "\[" & Filename & "]Sheet1'!A1"
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句