分享解决方法：用于查看已链接的 Micorosoft Excel 工作表的连接，已经失去

bluestone_yang

问题概述：

本人通过ADO链接操作EXCEL，第一次运行无误，第二次出现以下问题：“错误：-2147467259（80004005）用于查看已链接的 Micorosoft Excel 工作表的连接，已经失去。”





初始状态下的语句如下（当然略过了很多部分了）：

1. Dim adoCN As Object
   
2. Dim sqlP$, sqlS$, sqlI_Initial$, sqlI_Final$, sql$
   
3. Set adoCN = CreateObject("adodb.connection")
   
4. adoCN.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1;hdr=yes';data source=" & Workbooks(wbI).FullName
   
5. 
   
6.         sqlP = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Purchase'as [Transaction Type],[Bill Date] from [" & shP & "$] where [Bill Date]>='" & firstDay & "' and [Bill Date]<='finalDay'"
   
7.         sqlS = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Sales' as [Transaction Type],[Bill Date] from [" & shS & "$] where [Bill Date]>='" & firstDay & "' and [Bill Date]<='finalDay'"
   
8. sqlI_Initial = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Initial Invetory'as [Transaction Type],[Inventory Date] from [" & shI & "$] where [Inventory Date]='" & firstDay & " '"
   
9.   sqlI_Final = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Final Invetory' as [Transaction Type],[Inventory Date] from [" & shI & "$] where [Inventory Date]='" & finalDay & " '"
   
10. sql = sqlP & "union all " & sqlS & "union all " & sqlI_Initial & "union all " & sqlI_Final
    
11. 
    
12. Sheets("Process").[A2].CopyFromRecordset adoCN.Execute(sql)
    
13. Dim objPivotCache As Object
    
14. Set objPivotCache = Workbooks(wbI).PivotCaches.Add(SourceType:=xlExternal)
    
15. Set objPivotCache.Recordset = adoCN.Execute(sql)
    
16. objPivotCache.CreatePivotTable TableDestination:=Workbooks(wbI).Sheets("Balance").Range("A3"), TableName:="Balance"
    

复制代码

第一次运行没问题，第二次就出现上面的情况了

解决方法很简单
添加

1. adoCN.Close

复制代码

然后就可以了，最后如下：

1. Dim adoCN As Object
   
2. Dim sqlP$, sqlS$, sqlI_Initial$, sqlI_Final$, sql$
   
3. Set adoCN = CreateObject("adodb.connection")
   
4. adoCN.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1;hdr=yes';data source=" & Workbooks(wbI).FullName
   
5. 
   
6.         sqlP = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Purchase'as [Transaction Type],[Bill Date] from [" & shP & "$] where [Bill Date]>='" & firstDay & "' and [Bill Date]<='finalDay'"
   
7.         sqlS = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Sales' as [Transaction Type],[Bill Date] from [" & shS & "$] where [Bill Date]>='" & firstDay & "' and [Bill Date]<='finalDay'"
   
8. sqlI_Initial = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Initial Invetory'as [Transaction Type],[Inventory Date] from [" & shI & "$] where [Inventory Date]='" & firstDay & " '"
   
9.   sqlI_Final = "select [Organization Name],[Distributor Name],[Distributor Code],[Client Code],[Distributor ProductCode],[Distributor ProductName],[Distributor Product Unit],[Distributor Product Quantity],'Final Invetory' as [Transaction Type],[Inventory Date] from [" & shI & "$] where [Inventory Date]='" & finalDay & " '"
   
10. sql = sqlP & "union all " & sqlS & "union all " & sqlI_Initial & "union all " & sqlI_Final
    
11. 
    
12. Sheets("Process").[A2].CopyFromRecordset adoCN.Execute(sql)
    
13. Dim objPivotCache As Object
    
14. Set objPivotCache = Workbooks(wbI).PivotCaches.Add(SourceType:=xlExternal)
    
15. Set objPivotCache.Recordset = adoCN.Execute(sql)
    
16. objPivotCache.CreatePivotTable TableDestination:=Workbooks(wbI).Sheets("Balance").Range("A3"), TableName:="Balance"
    
17. 
    
18. 
    
19. adoCN.Close
    
20. Set adoCN = Nothing

复制代码

不清楚能否解决所有的这类情况，至少我的问题解决了；
网上搜索的一下，如果这个方法不能解决，大家请移步参考：
[紧急求助] VBA使用SQL查询，ADODB连接出现莫名错误，救命啊！
[求助] excel通过ado操作工作表的问题
请教：recordset超多次重复OPEN占用内存的问题

mybadboy

谢谢分享！

gtwlren

上次没关闭

Fungling

没用。不是这个原因

sancoz

Fungling 发表于 2023-10-25 16:24
没用。不是这个原因

我也遇到一样的情况，也的确不是CLOSE连接能解决，原因是数据源的格式错误导致的，可以尝试检查数据源的数据格式是否有异常的情况