sql-server - VBA 运行读取文件然后上传到文件到 SQL 表时出现运行时错误 3265

Question

启动此代码时遇到错误。任何人都可以帮忙吗？从 Excel 到 SQL 表的新开发。我使用此脚本的目的是读取 xlsx 文件，然后上传到 SQL 表。试图利用现有的代码。

在上传文件中是否有任何示例适合我修改列上的日期 dd/mm/yyyy 作为 sql 表仅采用 yyyy-mm-dd 日期格式，如下面的 Rs1 代码。

    Sub orderintake()
Dim filename As String
filename = uFN.sFile

If filename = "" Then Exit Sub

Application.ScreenUpdating = False

Sheets("Upload").Select


Dim RS1 As New ADODB.Recordset
Dim RS2 As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cnDB As New ADODB.Connection
Dim strCN As String
Dim strSQL As String
Dim strSQL2 As String


'Application.Workbooks.Open filename

strCN = "provider= Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';data source=" & filename

strSQL = "select * from [Upload$]"

cn.Open strCN

RS1.Open strSQL, cn

cnDB.Open dbconn.sConn

strSQL2 = "delete from orderintake where cmonth='" & RS1(7).Value & "'"
cnDB.Execute strSQL2

strSQL2 = "select * from orderintake where 1=0"
RS2.Open strSQL2, cnDB, adOpenKeyset, adLockOptimistic

Do Until RS1.EOF
    If IsNull(RS1(0).Value) = True Or Trim(RS1(0).Value) = "" Then Exit Do
    DoEvents
    RS2.AddNew

    'strSQL2 = "insert into orderintake values("

    For i = 0 To RS2.Fields.Count - 2

        RS2(i + 1).Value = RS1(i).Value

    Next i

    RS2.Update

    RS1.MoveNext

Loop

RS1.Close
cn.Close
cnDB.Close

'Application.Workbooks(Right(filename, Len(filename) - InStrRev(filename, "\"))).Close

Application.ScreenUpdating = True

MsgBox "Done!"

End Sub


Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Dim sCN As String

Function sConn() As String


Dim sFile As String, sLine As String, sDB As String

Open sFile For Input As #1

Line Input #1, sLine
sConn = "FILEDSN=" & sDB & ";uid=" + Trim(sLine)
Line Input #1, sLine
sConn = sConn + ";pwd=" + Trim(sLine)

Close #1


End Function


Function sFile() As String
        With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Show

        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            sFile = .SelectedItems(lngCount)
        Next lngCount

    End With

End Function

标签： sql-serverexcelvbaadodb