excelsheet限制_⽤VBA实现⽤SQL语句查询EXCEL中的数据
随着Office新版本功能越来越丰富强⼤，加上有各种专业的扩展，VBA这种古⽼的脚本语⾔，平时似乎派不上⽤场了。可是，有时候总会遇到⼀些“⼩问题”，范不上⽤各种⽜逼的语⾔（⽐如python），这时候⽤VBA来搞定还很⽅便的。
最近就遇到这样的问题，公司新上线了⼀个⼩程序商城，上架了⼀个试⽤申请活动。后台导出的订单中显⽰的只有⼀个活动名称，但库房配货的时候需要包含产品的明细。由于系统上线时间很赶，后台还没有完全开发好，这样就需要先导出订单列表，再⼿机匹配明细，⽣成⼀个新的出库单表⽤于打印配货。这种问题在数据库中，只要⽤SQL写个left join查询就⾏了。可是在EXCL中，有点⿇烦啊。
于是想到了VBA，使⽤VBA是可以把EXCEL当作数据库对象，建⽴连接，然后使⽤SQL查询，于是简单写了个脚本，实现从EXCEL数据⽂件中进⾏SQL查询：
选择要查询的⽂件－》建⽴连接－》写查询语句－》执⾏查询，把结果保存在⼀个新的Seet中。
代码如下：
Public Sub makeConn() '与⽂件建⽴数据库连接
Dim fileName, constr, Sql As String
Dim conn As Object
sql语句实现的四种功能Dim rs As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim arr
Dim cat As Object
Dim n As Integer
Dim sheetname As String
Set cat = CreateObject("adox.catalog") '这个是为了读取⽂件中的Sheet名字，作为SQL查询中的表名
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
fileName = ws.Cells(5, 5).Value '⽂件名变量
' Debug.Print fileName
Set conn = CreateObject("ADODB.Connection") '创建⼀个连接对象
Set conn = CreateObject("ADODB.Connection") '创建⼀个连接对象
Set rs = CreateObject("dset")            '创建⼀个记录集对象
conn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & fileName '打开⽂件连接
Set cat.ActiveConnection = conn
Debug.Print cat.Tables.Count
For n = 1 To cat.Tables.Count
If Right(cat.Tables.Item(n - 1).Name, 14) <> "FilterDatabase" Then
Debug.Print cat.Tables.Item(n - 1).Name
ws.Range("G" & (n + 4)).Value = Replace(cat.Tables.Item(n - 1).Name, "'", "")
End If
Next
Set cat = Nothing
conn.Close
Set conn = Nothing
MsgBox "连接成功！"
End Sub
Sub sqlQuery()  '执⾏SQL查询并返回结果
Dim Sql As String
Dim conn, rs As Object
Dim fileName As String
Dim ws As Worksheet
Dim arr
Dim TotalColumns, i As Integer
Set conn = CreateObject("ADODB.Connection") '创建⼀个连接对象
Set rs = CreateObject("dset")            '创建⼀个记录集对象
With ThisWorkbook
fileName = .Worksheets("tools").Cells(5, 5).Value
conn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & fileName '打开⽂件连接    Sql = .Worksheets("tools").Cells(8, 5).Value
rs.Open Sql, conn, 1, 1
'    arr = rs.getrows
.Worksheets.Add after:=Worksheets(1) '增加⼀个sheet ⽤于存放查询结果
.Worksheets(2).Name = "result"
'    .Worksheets("result").Range("A1") = Application.WorksheetFunction.Transpose(arr)
TotalColumns = rs.Fields.Count '下⾯的循环把表头（即列名）写到Excel表的第⼀⾏
For i = 0 To TotalColumns - 1
'Debug.Print rs.Fields(i).Name
.Worksheets(2).Cells(1, i + 1) = rs.Fields(i).Name
Next
.Worksheets(2).Range("A2").CopyFromRecordset rs  '把得到的结果插⼊表中
End With
rs.Close
conn.Close
Set conn = Nothing
MsgBox "work done"
End Sub
Sub gfpa()  '选择⽬标⽂件
Call getFilePath(5, 5)
End Sub
Public Function getFilePath(ByVal x As Byte, ByVal y As Byte) As String '弹出⽂件选择框，获取⽂件路径
Public Function getFilePath(ByVal x As Byte, ByVal y As Byte) As String '弹出⽂件选择框，获取⽂件路径
Dim strFolder As String
'差异：msoFileDialogFilePicker
With Application.FileDialog(msoFileDialogFilePicker)
'⽂件对话框的题⽬，根据个⼈情况进⾏设定
.Title = "Select File"
'默认打开的⽂件对话框路径，此处是d盘
.InitialFileName = "d:"
If .Show Then
'获取到路径
strFolder = .SelectedItems(1)
Debug.Print strFolder
Cells(x, y).Value = strFolder
End If
End With
End Function
有时候，⼯具不⼀定要多强⼤，能快速完成任务提⾼效率的⼯具就是好⼯具。另外，有⼀个功能强⼤的数据库管理⼯具叫作DatabaseNet5不错，可以连接各种数据库，包括EXCEL，只不过试⽤有次数限制，如果只是偶尔处理⼀下类似的这种需求，⽤VBA还是⾮常⽅便快捷的！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。