Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Verbindungspfad aktualisieren

Betrifft: Verbindungspfad aktualisieren von: Sparrow
Geschrieben am: 20.10.2014 12:30:25

Hallo!
Ich habe folgendes Problem: Ich habe eine Excel mit einer Verbindungsdatei - diese befindet sich in der gleichen Mappe! Wenn ich diese Excel nun an jemanden anders schicke aktualisiert sich der Pfad in der Verbindungseigenschaft leider nicht. Der andere User hat also meinen Pfad - bspw. /User/Max/Desktop - und nicht /User/Ich/Desktop - gibt es da eine Möglichkeit der automatischen Aktualisierung wo sich diese Datei gerade befindet?
Vielen Dank im Voraus
Beste Grüße
Sascha

  

Betrifft: AW: Verbindungspfad aktualisieren von: fcs
Geschrieben am: 20.10.2014 15:41:21

Hallo Sascha,

eine automatische Aktualisierung ist nur per Makro möglich.
Wenn die Abfrage auf die Daten innerhalb der Datei via Micorsoft-Querry erstellt ist, dann sollte folgendes Makro die Pfadangaben ggf. ersetzen/korrigieren.

Gruß
Franz

'Code im VBA-Editor unter "DieseArbeitsmappe" der Datei
Private Sub Workbook_Open()
  Call UpdateDatenverbindung
End Sub



'Code unter DieseArbeitsmappe oder in einem allgemeinen Modul der Datei
Sub UpdateDatenverbindung()
  'Update der Abfragedefinition, wenn die Quelle in dieser Datei ist und _
      die Datei unter einem anderen Namen gespeichert wird.
  Dim strConOld As String, strConNeu As String
  Dim strComOld As String, strComNeu As String
  Dim strPath As String, strFullName As String
  Dim strPathOld As String, strFullNameOld As String
  Dim strText As String, Pos1 As Integer, Pos2 As Integer
  Dim objCon As Object
  'Name und Pfad dieser Datei
  strPath = ThisWorkbook.Path
  strFullName = strPath & Application.PathSeparator & ThisWorkbook.Name
  'Daten der 1. Verbindung in der Datei
  Set objCon = ThisWorkbook.Connections(1)
  strConOld = objCon.ODBCConnection.Connection
  strComOld = objCon.ODBCConnection.CommandText
  'Debug.Print strConOld
  'Debug.Print strComOld
  'Dateiname im Connection String
  strText = strConOld
  Pos1 = InStr(1, strText, "DBQ=") + Len("DBQ=")
  Pos2 = InStr(Pos1, strText, ";")
  strFullNameOld = Mid(strConOld, Pos1, Pos2 - Pos1)
  
  'Pfad im Connection String
  Pos1 = InStr(Pos2, strText, "DefaultDir=") + Len("DefaultDir=")
  Pos2 = InStr(Pos1, strText, ";")
  strPathOld = Mid(strConOld, Pos1, Pos2 - Pos1)
  
  'Prüfen ob Dateiname dieser Datei mit Werten im Connection-String übereinstimmen
  If LCase(strFullNameOld) = LCase(strFullName) Then
    'MsgBox "Dateiname in Verbindung " & vbLf & strConOld & vbLf & "ist ok"
  Else
    'MsgBox "Dateiname in Verbindung " & vbLf & strConOld & vbLf & "ist nicht ok"
    'Dateiname und Pfad im Connection-String ersetzen
    Pos1 = InStr(1, strConOld, "DefaultDir=")
    strText = Mid(strConOld, Pos1)
    strConNeu = VBA.Replace(strText, strPathOld, strPath)
    strText = Left(strConOld, Pos1 - 1)
    strConNeu = VBA.Replace(strText, strFullNameOld, strFullName) & strConNeu
    objCon.ODBCConnection.Connection = strConNeu
    
    'Dateiname im Command-Text ersetzen
    strComNeu = VBA.Replace(strComOld, strFullNameOld, strFullName)
    objCon.ODBCConnection.CommandText = strComNeu
    
  End If
  ThisWorkbook.RefreshAll
End Sub



 

Beiträge aus den Excel-Beispielen zum Thema "Verbindungspfad aktualisieren"