ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: yj0715

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2015-1-4 10:21 | 显示全部楼层
yj0715 发表于 2015-1-3 19:35
说的就是路径和工作薄(文件)名的更改自适应的问题。
经过反复测试,这个代更码只适用于2003 版本建立的 ...
  1. 'SQL查询表的处理2
  2.     For Each Conn In ThisWorkbook.Connections
  3.         With Conn.OLEDBConnection
  4.             .SourceDataFile = ThisWorkbook.FullName
  5.             Arr = .Connection
  6.             Brr = VBA.Split(Arr, ";")
  7.             For i = 1 To UBound(Brr)
  8.                 If VBA.InStr(Brr(i), "Data Source") Then
  9.                     Brr(i) = "Data Source=" & ThisWorkbook.FullName
  10.                     Exit For
  11.                 End If
  12.             Next
  13.             Arr = VBA.Join(Brr, ";")
  14.             .Connection = Arr
  15.         End With
  16.     Next
复制代码

TA的精华主题

TA的得分主题

发表于 2015-1-4 10:22 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
  1. Sub SQL适应路径和文件名更改()
  2.     Dim strCon As String, iPath As String
  3.     Dim i As Integer, j As Integer, iFlag As String, iStr As String
  4.     Dim iT As Integer, jT As Integer
  5.     Dim sht As Worksheet
  6.     '定义变量
  7.     iPath = ThisWorkbook.FullName                '获取本工作簿的完全路径
  8.     On Error Resume Next
  9.     For Each sht In ThisWorkbook.Worksheets

  10.         'SQL查询表的处理
  11.         i = sht.QueryTables.Count
  12.         If i > 0 Then
  13.             For j = 1 To i
  14.                 strCon = sht.QueryTables(j).Connection
  15.                 '遍历工作表中数据透视表中缓存连接信息赋值给变量strCon
  16.                 Select Case Left(strCon, 5)  'select case语句,条件为strCon变量中从左侧取5个字符
  17.                 Case "ODBC;"                 '判断缓存连接信息中的数据连接方式,如果是ODBC方式
  18.                     iFlag = "DBQ="           '将"DBQ=" 赋值给变量iFlag
  19.                 Case "OLEDB"                 '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
  20.                     iFlag = "Source="            '将"DBQ=" 赋值给变量iFlag
  21.                 Case Else                    '没有引入外部数据或其他方式,不予处理
  22.                     Exit Sub
  23.                 End Select
  24.                 iStr = Split(Split(strCon, iFlag)(1), ";")(0)  '在变量strCon中截取文件路径信息
  25.                 With sht.QueryTables(j)      '替换SQL查询表中缓存信息中的文件完全路径
  26.                     .Connection = VBA.Replace(strCon, iStr, iPath)
  27.                     .CommandText = VBA.Replace(.CommandText, iStr, iPath)
  28.                 End With
  29.             Next j
  30.         End If

  31.         '数据透视表的处理
  32.         iT = sht.PivotTables.Count
  33.         If iT > 0 Then
  34.             For jT = 1 To iT
  35.                 strCon = sht.PivotTables(jT).PivotCache.Connection
  36.                 '遍历工作表中数据透视表中缓存连接信息赋值给变量strCon
  37.                 Select Case Left(strCon, 5)  'select case语句,条件为strCon变量中从左侧取5个字符
  38.                 Case "ODBC;"                 '判断缓存连接信息中的数据连接方式,如果是ODBC方式
  39.                     iFlag = "DBQ="           '将"DBQ=" 赋值给变量iFlag
  40.                 Case "OLEDB"                 '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
  41.                     iFlag = "Source="            '将"DBQ=" 赋值给变量iFlag
  42.                 Case Else                    '没有引入外部数据或其他方式,不予处理
  43.                     Exit Sub
  44.                 End Select
  45.                 iStr = Split(Split(strCon, iFlag)(1), ";")(0)  '在变量strCon中截取文件路径信息
  46.                 With sht.PivotTables(jT).PivotCache     '替换据透视表缓存信息中的文件完全路径
  47.                     .Connection = VBA.Replace(strCon, iStr, iPath)
  48.                     .CommandText = VBA.Replace(.CommandText, iStr, iPath)
  49.                 End With
  50.             Next
  51.         End If
  52.     Next

  53.     'SQL查询表的处理2
  54.     For Each Conn In ThisWorkbook.Connections
  55.         With Conn.OLEDBConnection
  56.             .SourceDataFile = ThisWorkbook.FullName
  57.             Arr = .Connection
  58.             Brr = VBA.Split(Arr, ";")
  59.             For i = 1 To UBound(Brr)
  60.                 If VBA.InStr(Brr(i), "Data Source") Then
  61.                     Brr(i) = "Data Source=" & ThisWorkbook.FullName
  62.                     Exit For
  63.                 End If
  64.             Next
  65.             Arr = VBA.Join(Brr, ";")
  66.             .Connection = Arr
  67.         End With
  68.     Next
  69. End Sub
复制代码

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-1-4 22:45 | 显示全部楼层
335081548老师:目前我所用的SQL IN EXCEL外部连接有两种,分别如下:
1、Microsoft.ACE.OLEDB连接(建立连接方式为OFFICE2010运行环境通过数据菜单,现有连接选项卡建立得来-引用自身所在工作薄某表建立内部连接)
连接字符串如下:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=E:\凭证模板.xlsm;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

其中主要有一个变量,工作薄路径,如本例的E:\凭证模板。

使用效果:此种连接已通过您修改的VBA代码使之能自适应路径和文件名变化而自动更新连接字符串。测试了多例,目前尚未发现问题。(如有不能适应的情况,将另上传附件)

2、直接连接SQL数据库(建立连接方式为OFFICE2010运行环境通过数据菜单,自其它来源,来自SQL SERVER建立得来)

连接字符串如下:
Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=UFDATA_004_2014;Data Source=PC2000;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PC2000;Use Encryption for Data=False;Tag with column collation when possible=False
其中主要有三个变量,sa密码,如本例的123;数据库名称,如 本例的UFDATA_004_2014;服务器名称,如本例的PC2000。

使用效果:此种连接通过您修改的VBA代码会改动Data Source=PC2000中的服务器名称,取代为当前文件所在路径加名称。而此种改变是不能够发生的。也就是说此种连接不要更改连接字符串中任何内容。

请求老师继续帮助修改代码,适应具体要求,谢谢为盼!

TA的精华主题

TA的得分主题

发表于 2015-1-5 16:04 | 显示全部楼层
  1. 'SQL查询表的处理2
  2.     For Each Conn In ThisWorkbook.Connections
  3.         With Conn.OLEDBConnection
  4.             .SourceDataFile = ThisWorkbook.FullName
  5.             Arr = .Connection
  6.             Brr = VBA.Split(Arr, ";")
  7.             For i = 1 To UBound(Brr)
  8.                 If VBA.InStr(Brr(i), "Provider=SQLOLEDB.1") Then Exit For
  9.                 If VBA.InStr(Brr(i), "Data Source") Then
  10.                     Brr(i) = "Data Source=" & ThisWorkbook.FullName
  11.                     Exit For
  12.                 End If
  13.             Next
  14.             Arr = VBA.Join(Brr, ";")
  15.             .Connection = Arr
  16.         End With
  17.     Next
复制代码

TA的精华主题

TA的得分主题

发表于 2015-1-5 16:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
其实,全部用VBA代码更好。

TA的精华主题

TA的得分主题

发表于 2015-8-29 17:42 | 显示全部楼层
麻烦问一下老师,用这个代码能更新数据透视表的SQL的连接字符串,却不能更新连接文件。附件的文件是用“数据”选项卡中的“现有连接”功能,连接本工作簿的工作表。之前的工作簿名称是A.xls,我重命名为B.xls,打开文件运行代码后,查看连接属性就发现了这个问题,请问有没有解决方案?
QQ截图20150829173819.png B.rar (232.79 KB, 下载次数: 16)

TA的精华主题

TA的得分主题

发表于 2016-10-15 17:12 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助

老师您好!
我有一个问题请教:下面的代码是录制宏得到的,在其工作薄中用此代码,同时修改红色字符,命令文本修改成功了。因我使用多个工作薄且每个工作薄内又有若干个透视表,命令文本中每次增加一个“月份”的统计表都要修改,
现在想能否新建一个工作薄将下面的代码改成通用的,一次性将同文件夹下所有透视表的连接命令文本,按要求全部修改呢?
(只修改连接命令文本,其它因是复制过来的不改动)谢谢!
Sub 修改写入连接命令文本()
    Sheets("分月汇总").Select
    With ActiveWorkbook.Connections("费用逐月统计汇总表数据源47").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array( _
            "select * from [上年结转$] union all " & Chr(13) & "" & Chr(10) & _
            "select * from [1-3月份$] union all " & Chr(13) & "" & Chr(10) & _
            "select * from [4-5月份$] union all " & Chr(13) & "" & Chr(10) & _
            "select * from [6-7月份$] union all " & Chr(13) & "" & Chr(10) & _
            "select * from [8-9月份$] union all " & Chr(13) & "" & Chr(10) & _
            "select * from [10-12月份$]")
    End With
End Sub


TA的精华主题

TA的得分主题

发表于 2017-7-29 16:47 | 显示全部楼层
能否在excel ms query (导入外部数据)的情况下也能根据数据源改变而自动改变路径呢

TA的精华主题

TA的得分主题

发表于 2017-8-5 23:04 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-8-5 23:13 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 20:47 , Processed in 0.049264 second(s), 7 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表