我有以下格式的excel資料：
當我單擊 UploadData 按鈕時，此資料將上傳到我連接的 SQL 資料庫中。這僅在上傳整數和字串時有效，當我嘗試將日期推送到資料庫時，我收到以下錯誤：
運行 VBA 代碼時，它似乎忽略了我在 Excel 作業表中應用日期列的自定義日期格式（“yyyy-mm-dd”），以使其格式正確。如何編輯我的 VBA 代碼以使日期列資料采用“yyyy-mm-dd”格式，我認為它應該與我的代碼中 For 回圈中的“查詢”變數有關，以便當它運行查詢，它將 Date1 的值設定為正確的時間格式。注意：在查詢變數下，我包含了我在 excel 表中使用的公式，它允許我以正確的格式提取日期值，盡管我無法在 VBA 中正確宣告它而不會出現錯誤
Sub UploadGsaData()
' Create the required variables
Dim query As String 'Variable for SQL query
Dim Date1 As String
Dim Address1 As String
Dim Mobile1 As Integer
Dim Salary1 As Integer
Dim TRow As Long
Dim CRow As Long
' Do not update the cells on the current sheet, this is performed after the SQL query
Application.ScreenUpdating = False
' Unprotect sheet to allow SQL changes to be made
ActiveSheet.Unprotect
' Send the ending row value
TRow = 7
' Inititate For Loop to iterate table of data
For CRow = 3 To TRow
Date1 = Sheets("Sheet1").Range("B" & CRow).Value
Address1 = Sheets("Sheet1").Range("C" & CRow).Value
Mobile1 = Sheets("Sheet1").Range("D" & CRow).Value
Salary1 = Sheets("Sheet1").Range("E" & CRow).Value
query = "REPLACE INTO testdata(Date, Address, Mobile, Salary) VALUES('" & Date1 & "', '" & Address1 & "', '" & Mobile1 & "', '" & Salary1 & "')"
'="REPLACE INTO operations_logbook(date_time, category, description, logged_by) VALUES('"&TEXT(B3,"yyyy-mm-dd")&"', '"&D6&"', '"&D10&"', '"&D8&"')"
' Call mysql query function to run query to upload log to database ( called a different name as different driver)
Call server_2_query_mysql_database(server2, database1, query, ActiveSheet.Range("A1"))
Next
' Reapply the Sheet protection now that the query is complete
ActiveSheet.Protect
' Update the Excel sheet so that any new data is updated
Application.ScreenUpdating = True
End Sub
uj5u.com熱心網友回復：
改變這個...
Date1 = Sheets("Sheet1").Range("B" & CRow).Value
……到這……
Date1 = Sheets("Sheet1").Range("B" & CRow).Text
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405394.html
標籤：