使用VBA从Excel导出数据到SQL Server时遇varchar转numeric错误

Let's break down the two key issues causing your error, plus a better practice to avoid this kind of problem entirely.

1. You're Treating Numeric Values as Strings in Your SQL Query #

Looking at your INSERT statement, you're wrapping all your numeric variables (like gil, gibnr) in single quotes ('). This tells SQL Server to interpret those values as varchar (text) instead of numeric types.

While SQL can often implicitly convert a varchar-formatted integer to numeric, it fails with real numbers—especially if your system uses a comma as the decimal separator, which would break numeric parsing entirely. That's why integers worked but real numbers throw the error.

2. VBA Variable Type Mismatch #

You mentioned your SQL columns are numeric(7,11), but you're using Long variables in VBA. Long is an integer type—it can't store decimal values (real numbers). When you try to assign a real number to a Long, it gets truncated or causes unexpected behavior, leading to invalid data being passed to SQL.

Step-by-Step Fixes #

First: Correct Your VBA Variable Types

Change the variables storing real numbers from Long to Double (for floating-point values) or use Variant with CDec() (for precise decimal values, ideal for financial data):

' Use Double for real numbers
Dim gil, gibnr, gul, fil, fibnr, ful, qil, qibnr, qul, xil, xibnr, xul, oil, oibnr, oul, cil, cibnr, cul, nil, nibnr, nul, bul, hul, lul, indul As Double

' Or for precise decimal (numeric) values:
' Dim gil As Variant, gibnr As Variant, gul As Variant... 
' Then assign with: gil = CDec(Range("A1").Value)

Second: Remove Single Quotes from Numeric Values in SQL

Modify your INSERT statement to remove the single quotes around numeric variables. Only string values need single quotes:

conn.Execute "insert into dbo.Intl_LL (UWU,OBU,Profile_ID, Insured_name, Claim_number,Claim_desc, Event_Name, UY, AY, AQ, Date_of_loss, Region, CCY, Policy_number, Branch, LE, MPL,Claim_alert_email, Comments, [Large Profile Flag], [Earmark Flag], [Tracked for Qtrly dev], [Gross Incurred], [Gross IBNR], [Gross Ultimate], [FAC Incurred], [FAC IBNR], [FAC Ultimate], [QS Incurred],[QS IBNR],[QS Ultimate],[XOL Incurred],[XOL IBNR],[XOL Ultimate],[Ceded OTH Incurred],[Ceded OTH IBNR],[Ceded OTH Ultimate],[Ceded Total Incurred],[Ceded Total IBNR],[Ceded Total Ultimate],[Net Incurred],[Net IBNR],[Net Ultimate],[Booked Ultimate],version)" & _ 
"values ('" & sUWU & "', '" & sOBU & "','" & sProfile & "', '" & sInsured & "','" & sClaim & "','" & sClmdesc & "','" & sEvent & "','" & sUY & "','" & sAY & "','" & sAQ & "','" & sDOL & "','" & sRegion & "','" & sCCY & "','" & sPolnum & "','" & sBranch & "','" & sLE & "','" & sMPL & "','" & sClaimalert & "','" & sComm & "','" & sLargeF & "','" & sEarF & "','" & sTrackF & "', " & gil & ", " & gibnr & ", " & gul & ", " & fil & ", " & fibnr & ", " & ful & ", " & qil & ", " & qibnr & ", " & qul & ", " & xil & ", " & xibnr & ", " & xul & ", " & oil & ", " & oibnr & ", " & oul & ", " & cil & ", " & cibnr & ", " & cul & ", " & nil & ", " & nibnr & ", " & nul & ", " & bul & ", " & ver & ")"

Notice how variables like gil now have no surrounding single quotes—they're passed directly as numeric values.

Bonus: Use Parameterized Queries (Recommended)

String concatenation for SQL queries is error-prone and risky (SQL injection). Parameterized queries eliminate type conversion issues entirely and are safer. Here's a quick example:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO dbo.Intl_LL (UWU, OBU, [Gross Incurred], [Gross IBNR]) VALUES (?, ?, ?, ?)"

' Add string parameters (adjust length to match your SQL column definitions)
cmd.Parameters.Append cmd.CreateParameter("UWU", adVarChar, adParamInput, 50, sUWU)
cmd.Parameters.Append cmd.CreateParameter("OBU", adVarChar, adParamInput, 50, sOBU)

' Add numeric parameters for your real number columns
cmd.Parameters.Append cmd.CreateParameter("GrossIncurred", adNumeric, adParamInput, , gil)
cmd.Parameters.Append cmd.CreateParameter("GrossIBNR", adNumeric, adParamInput, , gibnr)

' Execute the query
cmd.Execute

You'd add all your remaining parameters in the same way, matching each SQL column's data type.

内容的提问来源于stack exchange，提问作者Rajat Gupta