Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Moving linked Excel/Access files to another machine
Hi, I'm new and stuck trying to design an Excel worksheet with macros that import data from an Access database to populate the spreadsheet. It works fine on my machine, but when I try to move it to another machine the excel file loses the location of the database and comes up with error messages to that effect. Is there a way to include a relative path to the database in the code of the macros so that I can move both files together to any machine? (or any other way of doing it) Please excuse my lack of knowledge if this is an easy one...
Many thanks, Carole |
#2
|
|||
|
|||
Unfortunately Excel has a propensity to store absolute paths to
databases. You could run a macro to change the paths. Something like this one: Sub ChangeQuerySources() Dim stFrom As String Dim stTo As String Dim stConn As String Dim QT As QueryTable Dim WS As Worksheet Dim V ' change all querytables from one directory to another ' and change the directory from stFrom to stTo ' These are for your case: stFrom = InputBox("Change database path from:", Default:="C:\ProjectA\") If stFrom = "" Then Exit Sub stTo = InputBox("Change database path to:", Default:="Y:\App\ProjectA\") If stTo = "" Then Exit Sub For Each WS In ActiveWorkbook.Worksheets For Each QT In WS.QueryTables stConn = Flatten(QT.Connection) stConn = Subst(stConn, stFrom, stTo) QT.Connection = SplitToArray(stConn, 255) QT.Sql = SplitToArray(Subst(Flatten(QT.Sql), stFrom, stTo), 255) QT.Refresh BackgroundQuery:=False Next Next End Sub Function Flatten(V) As String Dim I As Integer If IsArray(V) Then For I = LBound(V) To UBound(V) Flatten = Flatten & V(I) Next Else Flatten = V End If End Function Function Subst(ByVal InString As String, stReplace As String, stWith As String) As String ' replace any occurrence of stReplace in InString with stWith ' could use the built in Replace function if using Excel 2000 or later Dim stResult As String Dim iChar As String iChar = InStr(LCase(InString), LCase(stReplace)) ' string compare is case sensitive Do While iChar 0 stResult = stResult & Left(InString, iChar - 1) & stWith InString = Mid(InString, iChar + Len(stReplace)) iChar = InStr(LCase(InString), LCase(stReplace)) Loop Subst = stResult & InString End Function Function SplitToArray(ST As String, Lump As Integer) ' break a long string up into an array with each element of size Lump ' don't bother if string is not longer than Lump Dim A() Dim I As Integer If Len(ST) <= Lump Then SplitToArray = ST Else ReDim A(1 To Len(ST) \ Lump + 1) For I = 1 To Len(ST) \ Lump + 1 A(I) = Mid(ST, 1 + (I - 1) * Lump, Lump) Next SplitToArray = A() End If End Function Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting Linked Files | Excel Discussion (Misc queries) | |||
Moving Files in Excel | Excel Discussion (Misc queries) | |||
Linked files - Caching of data | Excel Discussion (Misc queries) | |||
copying files with query(s), the query is still linked to the ori. | Excel Discussion (Misc queries) | |||
Two spreadsheets linked - moving cells | Excel Discussion (Misc queries) |