I want to relink all excel files(Linked tables) in my database from a monthly directory to a daily directory ie
C:\cadproj\MONTHLY\input\Manual
to
C:\cadproj\Dailyrep\input\Manual
I can do this for linked tables and text files but not for excel files
the code for the text files is below.
Can anyone please show me how to do this for excel files.
The directories im linking between contain several other excel files which arnt relevant to the database, and I dont want to hard code the table names. Any help is appreciated
Function LinkedExcelTables(DailyMonthly As String)
Dim tbl As TableDef
Dim LocalTblname As String
Dim EndConPos, StartConPos As Integer
Dim EndConStr, StartConStr As String
Const Link = "Text"
Const CONNECT_PREFIX = ";DATABASE="
Const MONTHLYPATH = "C:\cadproj\MONTHLY\input\Manual"
Const DAILYPATH = "C:\cadproj\Dailyrep\input\Manual"
Set db = DBEngine(0)(0)
Dim Connection As String
db.TableDefs.Refresh
If DailyMonthly = "Monthly" Then
For Each tbl In db.TableDefs
Connection = tbl.Connect
On Error Resume Next
If Connection <> "" And (InStr(Connection, Link) <> 0) Then
LocalTblname = tbl.Name
StartConPos = InStr(Connection, CONNECT_PREFIX)
StartConStr = Mid(tbl.Connect, 1, StartConPos - 1)
EndConStr = CONNECT_PREFIX & MONTHLYPATH
Debug.Print "old" & tbl.Name & Connection
tbl.Connect = StartConStr & EndConStr
db.TableDefs(LocalTblname).RefreshLink
Debug.Print "New" & tbl.Name & StartConStr & EndConStr
End If
Next
End If
If DailyMonthly = "Daily" Then
For Each tbl In db.TableDefs
Connection = tbl.Connect
On Error Resume Next
If Connection <> "" And (InStr(Connection, Link) <> 0) Then
LocalTblname = tbl.Name
StartConPos = InStr(Connection, CONNECT_PREFIX)
StartConStr = Mid(Connection, 1, StartConPos - 1)
EndConStr = CONNECT_PREFIX & DAILYPATH
Debug.Print "old" & tbl.Name & Connection
tbl.Connect = StartConStr & EndConStr
db.TableDefs(LocalTblname).RefreshLink
Debug.Print "New" & tbl.Name & StartConStr & EndConStr
End If
Next
End If
End Function
C:\cadproj\MONTHLY\input\Manual
to
C:\cadproj\Dailyrep\input\Manual
I can do this for linked tables and text files but not for excel files
the code for the text files is below.
Can anyone please show me how to do this for excel files.
The directories im linking between contain several other excel files which arnt relevant to the database, and I dont want to hard code the table names. Any help is appreciated
Function LinkedExcelTables(DailyMonthly As String)
Dim tbl As TableDef
Dim LocalTblname As String
Dim EndConPos, StartConPos As Integer
Dim EndConStr, StartConStr As String
Const Link = "Text"
Const CONNECT_PREFIX = ";DATABASE="
Const MONTHLYPATH = "C:\cadproj\MONTHLY\input\Manual"
Const DAILYPATH = "C:\cadproj\Dailyrep\input\Manual"
Set db = DBEngine(0)(0)
Dim Connection As String
db.TableDefs.Refresh
If DailyMonthly = "Monthly" Then
For Each tbl In db.TableDefs
Connection = tbl.Connect
On Error Resume Next
If Connection <> "" And (InStr(Connection, Link) <> 0) Then
LocalTblname = tbl.Name
StartConPos = InStr(Connection, CONNECT_PREFIX)
StartConStr = Mid(tbl.Connect, 1, StartConPos - 1)
EndConStr = CONNECT_PREFIX & MONTHLYPATH
Debug.Print "old" & tbl.Name & Connection
tbl.Connect = StartConStr & EndConStr
db.TableDefs(LocalTblname).RefreshLink
Debug.Print "New" & tbl.Name & StartConStr & EndConStr
End If
Next
End If
If DailyMonthly = "Daily" Then
For Each tbl In db.TableDefs
Connection = tbl.Connect
On Error Resume Next
If Connection <> "" And (InStr(Connection, Link) <> 0) Then
LocalTblname = tbl.Name
StartConPos = InStr(Connection, CONNECT_PREFIX)
StartConStr = Mid(Connection, 1, StartConPos - 1)
EndConStr = CONNECT_PREFIX & DAILYPATH
Debug.Print "old" & tbl.Name & Connection
tbl.Connect = StartConStr & EndConStr
db.TableDefs(LocalTblname).RefreshLink
Debug.Print "New" & tbl.Name & StartConStr & EndConStr
End If
Next
End If
End Function
