请教：SQL查询自适应路径和文件名更改

yj0715 · 发表于 2014-12-20 10:48

EXCEL SQL导入外部数据（MS ACE OLEDB连接）移动工作薄后，不能刷新（不能适应路径变化）
示例1随便改变文件路径和名称，更改数据源后就能刷新得到最新结果，而示例2不行
里面有宏代码，不知道怎么修改，也能让示例2得到同样效果

yj0715 · 发表于 2014-12-24 22:25

自己顶一下，，烦请坛友帮忙解决。谢谢。

335081548 · 发表于 2014-12-25 10:01

附件呢？

maxiaoan · 发表于 2014-12-26 09:34

请上传附件

yj0715 · 发表于 2014-12-27 21:10

重新上传附件，请老师们不吝赐教，谢谢。

335081548 · 发表于 2014-12-28 14:34

在原来的基础上修改了一下

'适用于本工作簿中所有工作表的SQL查询表，透视表采用引入外部数据方式对本工作簿中的连接
'适应路径和文件名更改,但基本上不适用于SQL语句中带有路径名或文件名的情形，也不适用于引入其他工作簿的情形。
Sub SQL表透视表适应路径和文件名更改()
Dim strCon As String, iPath As String
Dim i As Integer, j As Integer, iFlag As String, iStr As String
Dim iT As Integer, jT As Integer
Dim sht As Worksheet
'定义变量
iPath = ThisWorkbook.FullName '获取本工作簿的完全路径
On Error Resume Next
For Each sht In ThisWorkbook.Worksheets
'SQL查询表的处理
i = sht.QueryTables.Count
If i > 0 Then
For j = 1 To i
strCon = sht.QueryTables(j).Connection
'遍历工作表中数据透视表中缓存连接信息赋值给变量strCon
Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符
Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式
iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag
Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag
Case Else '没有引入外部数据或其他方式，不予处理
Exit Sub
End Select
iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息
With sht.QueryTables(j) '替换SQL查询表中缓存信息中的文件完全路径
.Connection = VBA.Replace(strCon, iStr, iPath)
.CommandText = VBA.Replace(.CommandText, iStr, iPath)
End With
Next j
End If
'数据透视表的处理
iT = sht.PivotTables.Count
If iT > 0 Then
For jT = 1 To iT
strCon = sht.PivotTables(jT).PivotCache.Connection
'遍历工作表中数据透视表中缓存连接信息赋值给变量strCon
Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符
Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式
iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag
Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag
Case Else '没有引入外部数据或其他方式，不予处理
Exit Sub
End Select
iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息
With sht.PivotTables(jT).PivotCache '替换据透视表缓存信息中的文件完全路径
.Connection = VBA.Replace(strCon, iStr, iPath)
.CommandText = VBA.Replace(.CommandText, iStr, iPath)
End With
Next
End If
Next
'SQL查询表的处理2
For Each Conn In ThisWorkbook.Connections
strCon = Conn.OLEDBConnection
'遍历SQL查询表中缓存连接信息赋值给变量strCon
Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符
Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式
iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag
Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag
Case Else '没有引入外部数据或其他方式，不予处理
Exit Sub
End Select
With Conn.OLEDBConnection
.Connection = VBA.Replace(strCon, iStr, iPath)
.SourceDataFile = ThisWorkbook.FullName
End With
Next
End Sub

复制代码

yj0715 · 发表于 2014-12-31 19:38

335081548老师，成功了，感激不尽。这个问题困扰我很久了。
原有的：SQL查询表的处理语句本身有问题吗？

yj0715 · 发表于 2015-1-2 23:26

本帖最后由 yj0715 于 2015-1-3 18:56 编辑

本以为解决了，却是如下情况：
MS 2003默认的：Provider=Microsoft.Jet.OLEDB.4.0连接可以适应文件名更改和路径更改。
MS 2010默认的：Provider=Microsoft.ACE.OLEDB.12.0连接还是不行。附件如下：

SQL查询自适应路径和文件名更改.rar (16.85 KB, 下载次数: 57)

张雄友 · 发表于 2015-1-3 19:27

yj0715 发表于 2015-1-2 23:26
本以为解决了，却是如下情况：
MS 2003默认的：Provider=Microsoft.Jet.OLEDB.4.0连接可以适应文件名更改和 ...

感谢提供支持，其实我的意思是：《SQL查询自适应路径和文件名更改》只是对路径与工作簿名更改有效的；但对更改工作表名是无效的。

yj0715 · 发表于 2015-1-3 19:35

张雄友发表于 2015-1-3 19:27
感谢提供支持，其实我的意思是：《SQL查询自适应路径和文件名更改》只是对路径与工作簿名更改有效的；但对 ...

说的就是路径和工作薄（文件）名的更改自适应的问题。
经过反复测试，这个代更码只适用于2003 版本建立的外部连接，2010版本无法做到自适应路径和文件名变化

		自动登录	找回密码
密码			免费注册

[求助] 请教：SQL查询自适应路径和文件名更改