刷新BackgroundQuery VBA

Question

Sub OPInport(month As Integer, year As Integer)
'
' Macro6 Macro
'F und G

'
    Dim selectedRange As Date
    Dim WrkBook As Workbook
    Dim WrkSheet As Worksheet
    Set WrkBook = ActiveWorkbook
    Set WrkSheet = ActiveSheet

selectedRange = DateSerial(year, month, 1)
MsgBox selectedRange

WrkBook.Sheets(1).Columns("G:H").NumberFormat = "dd.mm.yyyy"

Range("$A$1").Value = "Change"

Application.CutCopyMode = False
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "ODBC;DSN=OpsApps;UID=Alligatoah;Trusted_Connection=Yes;APP=Microsoft Office 2016;WSID=AT000616;DATABASE=OpsApps" _
    , Destination:=Range("$B$1")).QueryTable
    .CommandText = Array( _
    "SELECT IPM_V_TV_URB.Customer, IPM_V_TV_URB.KNUM, IPM_V_TV_URB.DMRF, IPM_V_TV_URB.HeaderBoM, IPM_V_TV_URB.ProgramReleasedCosts, IPM_V_TV_URB.PlnLaunch, IPM_V_TV_URB.SystemSDate, IPM_V_TV" _
    , _
    "_URB.ActualCosts" & Chr(13) & "" & Chr(10) & "FROM OpsApps.dbo.IPM_V_TV_URB IPM_V_TV_URB" & Chr(13) & "" & Chr(10) & "WHERE (IPM_V_TV_URB.SystemSDate>={ts selectedRange & 00:00:00'})" _ 
    )
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_from_OpsApps"
    .Refresh BackgroundQuery:=False
End With

我想选择一个特定范围的数据，但当我尝试这样做时，它会显示“Runtime Error 1004 General ODBC Error”并指出最后一行，.refresh BackgroundQuery:=false。我对VBA还很陌生，真的找不到解决这个问题的方法。当我使用SelectedDate更改where问题中的日期时，它才开始显示错误消息。

Answer 1

您需要将选定的日期连接到字符串中-您当前所做的是使用变量的字面名称作为日期。

"_URB.ActualCosts" & Chr(13) & "" & Chr(10) & "FROM OpsApps.dbo.IPM_V_TV_URB IPM_V_TV_URB" & Chr(13) & "" & Chr(10) & "WHERE (IPM_V_TV_URB.SystemSDate>={ts '" & Format(selectedRange, "yyyy-mm-dd") & " 00:00:00'})"

您可能需要更改所使用的日期格式。