我编写了以下 vba 代码,该代码应该从 old.xlsx 的某些单元格导入任何现有数据。
这些单元格是数据透视表的一部分,并且 OE > location > qual in 作为其行的子单元格。
该列由手动输入的日期决定。
strFormula 的格式正确。打印它并手动将其输入到单元格中,得到
一个结果。但是,使用 VBA 总是返回错误 2015。
我已经无计可施了。我手动测试了代码的每个部分并且它有效。
它只是工作表。评估似乎不适用于字符串。
使用 wsA.Evaluate("B6") 效果很好,并将值保存到当前工作表中。
有什么我不知道的问题或者我错过了什么吗?
Sub Einstellungen()
Dim wbB As Workbook
Set wbB = ThisWorkbook
Dim wsB As Worksheet
Set wsB = ActiveSheet
Dim importColumn As String
importColumn = InputBox("Input Column Name", "Column Name")
Dim importColumnNumber As Integer
importColumnNumber = Range(importColumn& 1).Column
Dim wbA As Workbook
Set wbA = Workbooks.Open(ThisWorkbook.Path & "\old.xlsx")
Dim wsA As Worksheet
Set wsA = wbA.Sheets("Sheet1")
Dim date As String
date = InputBox("Input Date of Export Column", "Date")
Call copyData("A", "NY", "1", date, 4, importColumnNumber , wsA, wsB)
Call copyData("A", "NY", "2", date, 4, importColumnNumber , wsA, wsB)
Call copyData("A", "NY", "3", date, 4, importColumnNumber , wsA, wsB)
Call copyData("A", "NY", "4", date, 4, importColumnNumber , wsA, wsB)
Call copyData("A", "NY", "5", date, 4, importColumnNumber , wsA, wsB)
wbA.Close SaveChanges:=True
End Sub
Function copyData(OE As String, location As String, qual As String, date As String, rowNumber As Integer, startColumnNumber As Integer, wsA As Worksheet, wsB As Worksheet)
Dim strFormula As String
strFormula = "=PIVOTDATENZUORDNEN(""Name"";$A$3;""OE"";""" & OE & """;""location"";""" & location& """;""qual"";""" & qual & """;""date"";""" & date & """)"
Dim value As Variant
value = wsA.Evaluate(strFormula)
Dim columnNumber As Integer
Select Case qual
Case "1"
columnNumber = startColumnNumber + 1
Case "2"
columnNumber = startColumnNumber + 2
Case "3"
columnNumber = startColumnNumber + 3
Case "4"
columnNumber = startColumnNumber + 4
Case "5"
columnNumber = startColumnNumber + 5
End Select
Debug.Print strFormula
Debug.Print value
If Not IsError(value) Then
wsB.Cells(rowNumber, columnNumber ).value = value
Else
wsB.Cells(rowNumber, columnNumber ).value = 0
End If
End Function