我在Creator workbook
中有这段代码,我正在从我选择的数据文件中复制数据。但是代码给了我以下错误:
对象变量或未设置块变量
Sub transfer()
Dim myfile As Workbook
Dim myWs As Worksheet
Dim DataWs As Worksheet
Set myWs = ThisWorkbook.ActiveSheet
myfile = Application.GetOpenFilename(, , "Browse For Data file")
Workbooks.Open myfile
Set DataWs = myfile.Sheets("Instru Input")
myWs.Range("C3:C11000").Copy
DataWs.Range("E2").PasteSpecial xlPasteAll
myWs.Range("E3:E11000").Copy
DataWs.Range("F2").PasteSpecial xlPasteAll
myWs.Range("G3:G11000").Copy
DataWs.Range("G2").PasteSpecial xlPasteAll
myWs.Range("I3:I11000").Copy
DataWs.Range("H2").PasteSpecial xlPasteAll
myWs.Range("K3:K11000").Copy
DataWs.Range("I2").PasteSpecial xlPasteAll
myWs.Range("M3:M11000").Copy
DataWs.Range("J2").PasteSpecial xlPasteAll
myWs.Range("O3:O11000").Copy
ThisWorkbook.SaveAs
ThisWorkbook.Close
End Sub
这是
GetOpenFileName
根据这里的规范所做的:
显示标准的"打开"对话框,并从用户处获取文件名,而无需实际打开任何文件。
因此,一旦将文件名(带有文件路径)作为字符串获取,它就应该记录为字符串。使用此字符串,可以为工作簿变量分配Set myWb = Workbooks.Open(fileName)
:
Sub TestMe()
Dim fileName As String
fileName = Application.GetOpenFilename(, , "Browse For Data file")
Debug.Print fileName
Dim myWb As Workbook
Set myWb = Workbooks.Open(fileName)
End Sub
通常,这就是工作代码,将一些信息从一个工作表获取到另一个工作簿中的工作表的样子:
Sub TestMe()
Dim fileName As String
fileName = Application.GetOpenFilename(Title:="Browse For Data file")
Debug.Print fileName
Dim targetWs As Worksheet
Set targetWs = Workbooks.Open(fileName).Worksheets("Instru Input")
Dim sourceWs As Worksheet
'To avoid using the ActiveSheet:
Set sourceWs = ThisWorkbook.Worksheets("NameOfTheWorksheet")
With sourceWs
.Range("C3:C11000").Copy targetWs.Range("E2")
.Range("E3:E11000").Copy targetWs.Range("F2")
'And so on ...
End With
ThisWorkbook.SaveAs "WriteFileAddressHere.xlsb"
ThisWorkbook.Close
End Sub
确保将"NameOfTheWorksheet"字符串和"WriteFileAddressHere.xlsb"更改为相关内容。