gpt4 book ai didi

excel - Excel/VBA 中分配工作簿变量时出现运行时错误 91

转载 作者:行者123 更新时间:2023-12-03 07:30:57 25 4
gpt4 key购买 nike

我在Creator工作簿中有此代码,并且我正在从我选择的数据文件中复制数据。但代码给了我以下错误:

Object variable or with block variable not set

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 按照规范 from here 所做的事情。 :

Displays the standard Open dialog box and gets a file name from the user without actually opening any files.

因此,一旦获得字符串形式的文件名(带有文件路径),就应该将其记录为字符串。使用此字符串,可以使用 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”更改为相关内容。

关于excel - Excel/VBA 中分配工作簿变量时出现运行时错误 91,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54418129/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com