Writing SQL Queries against Excel files using OLEDB connection
系统：Windows 7 64bit
软件：Excel 2013 64bit
选择要查询的文件－》建立连接－》写查询语句－》执行查询，把结果保存在一个新的Seet中
1.建立连接
（1）Window系统对共享文件夹的访问有权限控制。客户端用Administrator2登录，bartender的身份即为Administrator2，需要配置可读取权限。
（2）Office本身对Excel的权限控制，可以将Excel设置为 只读，存档等权限。
step 1:Check your connectionstring properties in your excel connection manager
Specify Excel as your data source
ok
strPath = "\\Srkserver\ss-冲压\标签\怡得乐\SRK冲压件标签系统V1.1 20190801.xlsm"
str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES"";"
=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=;Extended Properties="Excel 12.0 Xml;IMEX=1;HDR=YES";读取excel 找不到可安装的ISAM
数据链接字符串写法错误；漏了分号.
ok
str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;IMEX=0;HDR=YES;Readonly=1;"";"
=>Provider=Microsoft.ACE.OLEDB.12.0;Data Source="<FileName>";Extended Properties="Excel 12.0 Xml;HDR=Yes;IMEX=0;Readonly=1;"add MAXSCANROWS=0
?
Extended Properties="Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Bypass UserInfo Validation=False;HDR=YES;MODE=READ;READONLY=TRUE;"
What are the possible MODE for Provider=Microsoft.ACE.OLEDB.12.0
When connecting to an external data source, in this case an Access database, you can set the access permissions to a few values:
Read (Lets you open a read-only copy of the database)
ReadWrite (Permits multiple processes to open and modify the database)
Share Deny None (Neither read nor write access can be denied to others)
Share Deny Read (Prevents others from opening in read mode)
Share Deny Write (Prevents others from opening in write mode)
Share Exclusive (Prevents others from opening in read or write mode)
Write (Lets you open a write-only copy of the database)
check the permissions for an excel file for my application
1.If you're using ASP, you'll need to add the IUSER_* user as in https://docs.microsoft.com/en-US/office/troubleshoot/excel/query-update-data
2.The account you will need to grant Modify permissions to the XLS file will likely be NETWORK SERVICE if this code is running in an ASP.NET application (it's specified in the IIS Application Pool). To find out exactly what account your code is running as, you can do a simple:
Response.Write(Environment.UserDomainName + "\\" + Environment.UserName);3.Grant EVERYONE full permissions to the folder in which the file was being written. Normally, ASP.NET runs under the NETWORK SERVICE account, and that already had permissions. However, the OleDb code is unmanaged, so it must run under some other security context. (I am currently too lazy to figure out which account, so I just used EVERYONE.)
4.Using the ADO drivers such as Excel and Access in ASP.NET is generally speaking a no-no, except for single-user scenarios. You can't expect multi-user database functionality from a single-user file-based driver. Due to its multi-user nature, ASP.NET is multithreaded.
5.
he only scenario where Excel/Access data read/write might be acceptable is if the files are to be used for a single-operation only by a single user, e.g. Excel import to the database over the web. But even in that case, don't take concurrency for granted.
在VBA中使用SQL语句筛选日期
"日期"Field的格式是真正的日期格式时,日期两边用#号连接
例如 ：
#2013-7-13#Sub MultipleSelect_Group1()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim mypath As String
Dim SQL As String
Dim i As Integer
'Dim d As Date '两种方式，一种是设置日期类型
Dim d As String '一种是设置文本类型
mypath = ThisWorkbook.FullName
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & mypath
'd = VBA.DateSerial(2018, 9, 26) '日期类型要用DateSerial方法生成日期格式的变量用作筛选条件
d = Format("2018/9/26", "yyyy/m/d") '文本类型要用Format函数将变量变为变体型，设置好格式后变为时间变量
SQL = "SELECT 逾期日期 FROM [sheet1$] WHERE 逾期日期 >=#" & d & "#" '用&符号拼接变量Format(
'SQL = "SELECT 逾期日期 FROM [sheet1$] WHERE Format(逾期日期,'yyyy/m/dd') >= '2018/9/26'"
'直接将excel中的日期字段变换格式
Set rst = cnn.Execute(SQL)
Worksheets(2).Select
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1) = rst.Fields(i).Name
Next
Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub
方法介绍
1.设置日期型变量，用VBA.DateSerial方法生成变量
2.用Format将文本变量设置为变体型变量，后转化为日期型变量
3.直接将记录中的日期字段设置为文本类型变量
A列的时间信息，统一转化为标准格式yyyy-mm-dd hh:mm:ss
Sub 转换()
Set sht = ThisWorkbook.Worksheets("示例")
maxRow = sht.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To maxRow Step 1
old_version = sht.Cells(i, "A")
new_version = Format(old_version, "yyyy-mm-dd hh:mm:ss")
sht.Cells(i, "B") = new_version
sht.Cells(i, "B").NumberFormatLocal = "yyyy-mm-dd hh:mm:ss"
Next i
End Sub
代码中有两个关键信息，一是将信息转化为标准格式，NumberFormatLocal是修改显示格式
以下两种方式中，在编辑栏显示的信息是一样的，但展示的效果是不一样的
代码中变量new_version数据类型是字符串，并不是时间格式，所示只是看起来像而已，本质上数据类型还是字符串
come from:Mark records being printed from Excel database
https://support.seagullscientific.com/hc/en-us/articles/360000523227-Mark-records-being-printed-from-Excel-database
OnNewRecord event
'Define the object names.
dim objConn dim strConn dim rs
'Define the database connection parameter constants.
Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H0001
'Connect to the Excel file.
Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Fruits.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
'Create a record set of one record which is the current record being printed.
Set rs = CreateObject("ADODB.Recordset") rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText rs.Find "Product = '" & Field("Sheet1$.Product") & "'"
'Set the "Printed" field of the record to be "YES" thus flagging it as having been printed.
rs.Fields("Printed") = "YES" rs.Update