我正在寻求从两个非常大的excel工作簿中加入特定数据，然后将其保存到另一个工作簿中。为了做到这一点，我使用ADODB对象来使用SQL命令，这使执行连接比执行成千上万的VLookup容易得多。我的安装程序提示状态名称，并且数据源被格式化以依赖于该状态。假设您输入了“内华达州”。它将连接到名为“内华达州-生物元数据多重续集”的文件，并将其与另一个名为“内华达州-网站数据仅多个续续号”的文件并从中选择某些字段。
在调试时，我可以看到连接工作正常，并且在将字符串变量'sql'更改为等于“ SELECT * FROM [biologicalresult $]”的情况下，它将可以正常工作。问题似乎与以下事实有关：我通过ADODB查询将其连接到另一个工作簿，并且出现错误：“无法更新。数据库或对象是只读的”
Option Explicit
Sub RunSELECT()
On Error GoTo ErrorHandling
Dim cn As Object, rs As Object, output As String, sql As String, state As String
state = InputBox("Please insert the state name with the first letter being capital")
Set cn = CreateObject("ADODB.Connection")
'---Connecting to the Data Source---
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.connectionstring = "Data Source=T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\State Data\" & state & _
" - biological metadata multiple cont;Extended Properties=""Excel 12.0 Xml;HDR=YES;Readonly = false;IMEX = 0"";"
.Open
End With
'---Run the SQL SELECT Query---
sql = "SELECT b.OrganizationIdentifier, b.OrganizationFormalName, b.MonitoringLocationIdentifier, s.LatitudeMeasure, s.LongitudeMeasure, " & _
"b.ActivityIdentifier , b.ActivityTypeCode, b.ActivityMediaName, b.ActivityMediaSubdivisionName, " & _
"b.ActivityStartDate, b.ProjectIdentifier, b.CharacteristicName, b.ResultSampleFractionText, b.ResultMeasureValue, b.[ResultMeasure/MeasureUnitCode] " & _
"FROM [biologicalresult$] b INNER JOIN (SELECT * FROM [Excel 12.0 Xml; IMEX = 0; HDR = Yes; Database = T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\" & _
"State Data\" & state & " - Site data only multiple cont;Readonly = False].[station$]) s " & _
"ON b.MonitoringLocationIdentifier = s.MonitoringLocationIdentifier;"
Set rs = cn.Execute(sql)
'---Clean up---
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing
Exit Sub
ErrorHandling:
MsgBox ("Source: " & Err.Source & vbNewLine & "Number: " & Err.Number & vbNewLine & "Description: " & Err.Description & vbNewLine & "Help Context: " & Err.HelpContext)
done:
End Sub
感谢您的任何帮助！
可以肯定的是，该错误信息不足，因为问题可能是由于文件路径问题引起的。但是，这种简单的印刷方式可能会对以后犯同样细微错误的读者有所帮助。顺便说一句，对于长名称，请考虑单独的变量，并将其连接为连接和SQL字符串。
包括完整路径（包括扩展名）.xlsx或.xlsm源文件中。
meta_file = "T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\State Data\" _
& state & " - biological metadata multiple cont.xlsx"
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.connectionstring = "Data Source=" & meta_file & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;Readonly=false;IMEX=0"";"
.Open
End With
在方括号名称空间内的Database参数之前和之后删除空格...Database=T:\Marketing\...。否则，引擎会将空间解释为名称的一部分。甚至考虑删除所有参数IMEX，HDR，Readonly等之间的空格。
site_file = "T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\State Data\" _
& state & " - Site data only multiple cont.xlsx"
'---Run the SQL SELECT Query---
sql = "SELECT b.OrganizationIdentifier, b.OrganizationFormalName, " & _
" b.MonitoringLocationIdentifier, s.LatitudeMeasure, " & _
" s.LongitudeMeasure, b.ActivityIdentifier, b.ActivityTypeCode, " & _
" b.ActivityMediaName, b.ActivityMediaSubdivisionName, " & _
" b.ActivityStartDate, b.ProjectIdentifier, b.CharacteristicName, " & _
" b.ResultSampleFractionText, b.ResultMeasureValue, " & _
" b.[ResultMeasure/MeasureUnitCode] " & _
"FROM [biologicalresult$] b " & _
"INNER JOIN (SELECT * FROM [Excel 12.0 Xml;IMEX=0;HDR=Yes;" & _
" Database=" & site_file & ";Readonly=False].[station$]) s " & _
"ON b.MonitoringLocationIdentifier = s.MonitoringLocationIdentifier;"
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句