问将excel vba函数值插入到access中
EN

Stack Overflow用户

提问于 2016-03-02 14:54:24

回答 1查看 403关注 0票数 1

我一直在为这个问题苦苦思索，似乎不能自己解决。我写了一个Excel工具，它可以计算出特定的金额。我必须对10,000个不同的案例执行此操作，并且必须将结果存储在Access中。不过，我还没弄明白是怎么回事。我需要使用的函数如下所示：

> Public Function Assets(TG As Integer, t As Integer, n As Integer,
> Tarif As String, j As Integer)
> 
> Assets = ...
> 
> End Function

在同一模块中，我有一个结合了几个过程的宏，还应该将结果插入到我的Access数据库中。我在这个数据库中有两列:案例编号和结果值。

> Sub Results()
> 
> Dim n As Integer Dim Tarif As String Dim TG As Integer Dim t As
> Integer Dim i As Integer Dim j As Integer Dim dbfile2 As String Dim
> dbe2 As Object    ' As DAO.DBEngine Dim db2  As Object    ' As
> DAO.Database Dim rs2 As Object     ' AS DAO.Recordset
> 
> 
> n = ... Tarif = ... TG = ...
> 
> For j = 1 To 10000
> 
>     'Opens the Access database
>     dbfile2 = "\\r74efc11\PR_GDV_TRA\Transparenz_2016_2017\20_TP_F\02_Arbeitspakete\CRK_Berechnung\Beispiel-Hochrechnung\Hochrechnung_Ablaufvermögen_Test.accdb"
>     Set dbe2 = CreateObject("DAO.DBEngine.120")
>     Set db2 = dbe2.OpenDatabase(dbfile2)
>    
>     db2.Execute "INSERT INTO Tabelle1 (Nr, [Value])VALUES(" & j & "," & Assets(TG,n,n,j) & ")"
>   
>     db2.Close
>     Set db2 = Nothing
>     Set dbe2 = Nothing
> 
> Next j
> 
> End Sub

这段代码在没有Assets function的情况下工作得很好，并且Assets function也可以在Excel中正常运行。我只想在我的Access数据库中插入结果。有人能帮我吗？我在网上能找到的就是如何插入工作表函数，但这并不适用于我的情况。我试着修改它，但没有任何运气。

excel

ms-access

vba

平台电商解决方案

为客户提供电商客服、电商图片系统识别及搜索的快速搭建方案

回答 1

Stack Overflow用户

发布于 2016-05-01 13:37:02

你不应该替换，而应该转换。虽然现在已经解决了这个问题，但在将来你可能会从这个函数中受益，它就是这样做的：

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function