Excel和Access的导入数据路径问题解决方案

几经波折,苦苦寻觅,终于搞明白Excel和Access的绝对/相对路径问题了,不容易啊!话说这个大家经常碰到的问题,怎么就没看到有人好好地总结一下呢?

下面所有代码均取自ExcelHome 网友分享。


ACCESS 导入 EXCEL

将access导入excel相对路径

用于EXCEL

注意:该代码不适用于 SQL语句中带有路径名或文件名的情形 ,即

 

不能用“新建数据库查询”的方式导入!因为SQL语句里有路径和文件名!

而必须

 

用“导入数据”的方式,这样SQL语句里就不会含有路径和文件名了!

Private Sub Workbook_Open()

Dim strCon$

strCon = ActiveSheet.QueryTables(1).Connection

iFlag = “Source=”

iPath = ThisWorkbook.Path

iStr = Split(Split(strCon, iFlag)(1), “” & Replace(Dir(iPath & “*.mdb”), “.mdb”, “”) & “”)(0)

ActiveSheet.QueryTables(1).Connection = Replace(strCon, iStr, iPath)

End Sub


EXCEL 导入 EXCEL(可不同一Excel文件)

SQL多工作簿适应路径 【注:只适用于EXCEL文件引用EXCEL文件,引用ACCESS不行】

Private Sub Workbook_Open()

Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As String

‘定义变量

On Error Resume Next

strCon = ActiveSheet.QueryTables(1).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

‘ iFlag = “第一次” 【“第一次”是指引用的文件的文件名】

iStr = Split(Split(strCon, iFlag)(1), “第一次”)(0) ‘在变量strCon中截取文件路径信息

iPath = ThisWorkbook.Path ‘获取当前活动工作簿的完全路径

With ActiveSheet.QueryTables(1) ‘替换数据透视表中缓存信息中的文件完全路径

.Connection = VBA.Replace(strCon, iStr, iPath)

.CommandText = VBA.Replace(.CommandText, iStr, iPath)

End With

End Sub


EXCEL 导入 EXCEL(限同一Excel文件)

‘适用于本工作簿中所有工作表的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 = ActiveWorkbook.FullName ‘获取当前活动工作簿的完全路径

On Error Resume Next

For Each sht In ActiveWorkbook.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

End Sub