VB-运行时错误'1004'-应用程序定义或对象定义的错误 [英] VB - Run-time error '1004' - Application-defined or object-defined error

问题描述

我已经在Excel中创建了 CommandButton ，并开始对 VBA 进行编码.想法是将参数传递给我的 CommandString ，以便用户可以过滤.

I have created a CommandButton within Excel and started coding VBA. The idea is to pass parameters to my CommandString so that the user can filter.

2个参数字段在 SQL

这是我的VBA代码，在我点击 CommandButton

Here is my VBA code which executes after I Click the CommandButton :

Private Sub CommandButton1_Click()
    Dim FromDate As Date
    Dim ToDate As Date

    FromDate = Sheets("Bips Travel Summary").Range("J3").Value
    ToDate = Sheets("Bips Travel Summary").Range("J4").Value

    'Pass the Parameters values to the Stored Procedure used in the Data Connection
    With ActiveWorkbook.Connections("192.168.0.3 Timesheets1").OLEDBConnection
        .CommandText = "SELECT ID, Employee,  WT, [Amount Per Kilometer], Currency, SUM([Number (Amount of km)]) AS [Number (Amount of km)], SUM([Total (per record)]) AS [Total (per record)] FROM ( SELECT S.ID ,S.FirstName + ' ' + S.LastName AS [Employee],TS.DateWorked AS [DateTraveled],C.Customer_Name,NULL AS [WT],EC.AA_Rate AS [Amount Per Kilometer],NULL AS [Currency],TS.Travel AS [Number (Amount of km)],TT.TravelDescription,TS.Travel * CONVERT(float, EC.AA_Rate) AS [Total (per record)] FROM [Timesheets].[dbo].[timesheets] TS INNER JOIN [Timesheets].[dbo].[traveltype] TT ON TS.TravelTypeCode = TT.TravelTypeCode INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code WHERE TS.DateWorked BETWEEN '" & FromDate & "' AND '" & ToDate & "') as A GROUP BY ID, Employee, WT, [Amount Per Kilometer], Currency"
        ActiveWorkbook.Connections("192.168.0.3 Timesheets1").Refresh

    End With
End Sub

在输入 FromDate 的值作为 20100101 后和 ToDate 作为 20150813 时，我收到一条错误消息，该错误消息落在该脚本上:

After entering the value for FromDate as 20100101 and ToDate as 20150813 I get an error message which falls over on this script:

FromDate = Sheets("Bips Travel Summary").Range("J3").Value

错误消息显示为:

Runtime error '13':
Type mismatch

由于我是 VBA 的新手，所以不确定从这里去哪里.有人可以指出正确的方向来解决这个问题吗?

Not sure where to go from here as I am very new to VBA. Could anyone please point me in the right direction to solve this issue?

推荐答案

该错误表明您正在尝试分配错误的数据类型.

The error says that you are trying to assign the wrong data type.

变量 FromDate 和 ToDate 被声明为 Date 类型，但是您正在尝试为它们分配文本.

Variables FromDate and ToDate are declared as Date type, but you are trying to assign texts to them.

如果您的单元格中有以下格式的日期:"20100101"和"20150813"，则需要先将它们转换为日期，然后再分配给以下变量:

If you have dates in this format in your cells: '20100101' and '20150813', you need to convert them to dates before assigning to those variables like below:

Private Sub CommandButton1_Click()
    Dim txtFromDate As String
    Dim txtToDate As String
    Dim FromDate As Date
    Dim ToDate As Date

    txtFromDate = Sheets("Bips Travel Summary").Range("J3").Value
    FromDate = DateSerial(Left(txtFromDate, 4), Mid(txtFromDate, 5, 2), Right(txtFromDate, 2))
    txtToDate = Sheets("Bips Travel Summary").Range("J4").Value
    ToDate = DateSerial(Left(txtToDate, 4), Mid(txtToDate, 5, 2), Right(txtToDate, 2))

    'Pass the Parameters values to the Stored Procedure used in the Data Connection
    With ActiveWorkbook.Connections("192.168.0.3 Timesheets1").OLEDBConnection
        .CommandText = "SELECT ID, Employee,  WT, [Amount Per Kilometer], Currency, SUM([Number (Amount of km)]) AS [Number (Amount of km)], SUM([Total (per record)]) AS [Total (per record)] FROM ( SELECT S.ID ,S.FirstName + ' ' + S.LastName AS [Employee],TS.DateWorked AS [DateTraveled],C.Customer_Name,NULL AS [WT],EC.AA_Rate AS [Amount Per Kilometer],NULL AS [Currency],TS.Travel AS [Number (Amount of km)],TT.TravelDescription,TS.Travel * CONVERT(float, EC.AA_Rate) AS [Total (per record)] FROM [Timesheets].[dbo].[timesheets] TS INNER JOIN [Timesheets].[dbo].[traveltype] TT ON TS.TravelTypeCode = TT.TravelTypeCode INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code WHERE TS.DateWorked BETWEEN '" & FromDate & "' AND '" & ToDate & "') as A GROUP BY ID, Employee, WT, [Amount Per Kilometer], Currency"
        ActiveWorkbook.Connections("192.168.0.3 Timesheets1").Refresh

    End With
End Sub

这篇关于VB-运行时错误'1004'-应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！