本人想通过EXCEL调取Sql server中BOM信息，但因为VBA中字符变量有255字符限制，SQL查询语句超过1500字符，导致SQL查询语句无法执行。想请教如何突破字符变量255字符限制，网上有说一些解决办法，可试了根本没用。

环境版本：EXCEL 2019

举例：

Dim strSQL as string

Dim strCn as string

Dim cn As Object
Dim rs As Object
Set cn = CreateObject("Adodb.Connection")
Set rs = CreateObject("Adodb.Recordset")

strCn = "Provider=sqloledb;Server=" 192.168.1.100 ";Database="ERP";Uid="sa";Pwd="123456"

strSQL=”WITH BC (G,H,I,J,K,L,M,N,O,P,Q,R,S,T) AS (SELECT c3.BOMMaster B,c4.Code H,c4.Name I,c4.SPECS J,c3.UsageQty K,c3.IssueUOM L,c3.SupplyWareHouse M,c3.ParentQty N,c4.ItemFormAttribute O,c3.IssueStyle P,c4.DescFlexField_PrivateDescSeg3 Q,c5.MinRcvQty R,c6.PurProcessLT S,c6.FixedLT T FROM CBO_BOMComponent c3 LEFT JOIN CBO_ItemMaster c4 ON c3.ItemMaster = c4.ID LEFT JOIN dbo.CBO_PurchaseInfo c5 ON c4.PurchaseInfo=c5.ID LEFT JOIN dbo.CBO_MrpInfo c6 ON c4.MrpInfo=c6.ID), FBC ( A,B,D,C, E, F) AS (SELECT  m1.Org A,m1.ID B,m1.BOMVersionCode D,c2.Code C,c2.Name E,c2.SPECS F FROM CBO_BOMMaster m1 LEFT JOIN CBO_ItemMaster c2 ON m1.ItemMaster = c2.ID WHERE m1.DisableDate>=GETDATE() AND m1.EffectiveDate<=GETDATE() AND m1.Status=2 AND m1.org='1001911084536821'), FB (A,B,C,D,E,F,G,H,I,J, K,L,M,N,O,P,Q,R,S,T) AS (SELECT u.A,u.B,u.C,u.D,u.E,u.F,v.G,v.H,v.I,v.J,v.K,v.L,v.M,v.N,v.O,v.P,v.Q,v.R,v.S,v.T FROM FBC u LEFT JOIN BC v ON v.G = u.B),
TR(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T) AS (SELECT * FROM FB where C ='SMDU26GB0028' AND D='E03' UNION ALL SELECT t1.A,t1.B,t1.C,t1.D,t1.E,t1.F,t1.G,t1.H,t1.I,t1.J,t1.K,t1.L,t1.M,t1.N,t1.O,t1.P,t1.Q,t1.R,t1.S,t1.T FROM FB t1 JOIN TR t2 ON t1.C = t2.H)
SELECT * FROM TR”

cn.Open strCn
rs.Open strSQL, cn
------------------------------------------------------------

strSQL会被截断为255个字符

找表格定制，技术员一对一服务 http://ffcell.com/Wo/index.aspx

回复：楼主