Externer Datenimport von variabler xlsm-Quelle

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 12:15:16

Hallo Forum, das hier ist mein erster Beitrag. Danke schon jetzt für eure tolle Unterstützung.
Ziel:
Ich möchte Daten in Excel importieren mittels einer Datenverbindung zu einem speziellen Ordner auf meinem Laptop. In dem Ordner liegen mehrere xlsm-Dateien. Es soll diejenige Datei importiert werden, dessen Ordnerpfad in Sheet1, Zelle C37 angegeben ist (Der Zelleninhalt in C37 und damit der jeweilige Pfad kann sich ändern).
Beispiel:
a) Inhalt Zelle C37: D:\Users\brem\Desktop\MARKT\VBA CSV Stammdaten\Positionsstammdaten\Input (xlms)\1test.xlsm -> Es sollen Daten aus der Datei "1test" in die Excel importiert werden
b) Inhalt Zelle C37: D:\Users\brem\Desktop\MARKT\VBA CSV Stammdaten\Positionsstammdaten\Input (xlms)\2test.xlsm -> Es sollen Daten aus der Datei "2test" in die Excel importiert werden
Problem:
Der VBA Code unten funktioniert zunächst, wenn ich das ganze unvariabel aufnehme, d.h. eine spezielle Datei in dem Ordner auswähle. Wenn ich aber die Dateiauswahl versuche variable zu gestalten mittels strQuelle bekomme ich eine Fehlermeldung (Laufzeitfehler 1004 - Die Abfrage oder das Öffnen der Tabelle konnte nicht ausgeführt werden)
VBA-CODE (Mittels Makro aufnehmen und Online-Recherche zusammengestellt):


Sub Daten_aktualisieren2()
' Pfad Variable definieren
Dim strQuelle As String
strQuelle = Worksheets("Sheet1").Cells(37, 3).Text
' Daten_aktualisieren Makro
    Sheets("Sheet2").Select
    With ActiveWorkbook.Connections("1test Sheet1$").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("Sheet1$")
        .CommandType = xlCmdTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source= _
strQuelle;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database=""""; _
Jet OLEDB:Re" _
        , _
        "gistry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:  _
_
Database Locking Mode=0;Jet OLEDB:Global Partia" _
        , _
        "l 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 OLE" _
        , _
        "DB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation= _
 _
False;Jet OLEDB:Limited DB Caching=False;" _
        , "Jet OLEDB:Bypass ChoiceField Validation=False")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = _
        "strQuelle"
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("1test Sheet1$")
        .Name = "1test Sheet1$"
        .Description = ""
    End With
    ActiveWorkbook.Connections("1test Sheet1$").Refresh
End Sub

Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Sepp
Geschrieben am: 04.06.2015 13:26:28
Hallo Werner,
logischerweise ungetestet!

Sub Daten_aktualisieren2()
  
  
  ' Pfad Variable definieren
  
  Dim strQuelle As String
  strQuelle = Worksheets("Sheet1").Cells(37, 3).Text
  
  ' Daten_aktualisieren Makro
  
  Sheets("Sheet2").Select
  With ActiveWorkbook.Connections("1test Sheet1$").OLEDBConnection
    .BackgroundQuery = True
    .CommandText = Array("Sheet1$")
    .CommandType = xlCmdTable
    .Connection = _
      Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;" _
      & "Data Source=" & strQuelle & ";" _
      & "Mode=Share Deny Write;Extended Properties="""";HDR=YES;Jet OLEDB:System database="""";" _
      & "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;" _
      & "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;Jet OLEDB:Limited DB Caching=False;" _
      & "Jet OLEDB:Bypass ChoiceField Validation=False")
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = ""
    .SourceDataFile = strQuelle
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
  End With
  With ActiveWorkbook.Connections("1test Sheet1$")
    .Name = "1test Sheet1$"
    .Description = ""
  End With
  ActiveWorkbook.Connections("1test Sheet1$").Refresh
End Sub


Gruß Sepp


Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 13:46:32
Hallo Sepp,
schonmal vielen Dank für deinen Vorschlag. Wenn ich das so kopiere, bekomme ich folgende Fehlermeldung: "Laufzeitfehler '13 - Typen unverträglich". Wenn ich dann auf Debuggen klicke, markiert mir Excel folgenden Bereich gelb:
.Connection = _
Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;" _
& "Data Source=" & strQuelle & ";" _
& "Mode=Share Deny Write;Extended Properties="""";HDR=YES;Jet OLEDB:System database="""";" _
& "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;" _
& "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;Jet OLEDB:Limited DB Caching=False;" _
& "Jet OLEDB:Bypass ChoiceField Validation=False")
Komisch finde ich, dass wenn ich den Dateipfad fest eingebe, alles super funktioniert. Siehe nachfolgend. Hier lädt er Daten aus der fest eingegebenen Datei problemlos aus.

Sub Daten_aktualisieren()
'
' Daten_aktualisieren Makro
'
'
    Sheets("Sheet2").Select
    With ActiveWorkbook.Connections("1test Sheet1$").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("Sheet1$")
        .CommandType = xlCmdTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\ _
Users\brem\Desktop\MARKT\VBA CSV Stammdaten\Positionss" _
        , _
        "tammdaten\Input (xlms)\1test.xlsm;Mode=Share Deny Write;Extended Properties=""HDR=YES;" _
";Jet OLEDB:System database="""";Jet OLEDB:Re" _
        , _
        "gistry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB: _
Database Locking Mode=0;Jet OLEDB:Global Partia" _
        , _
        "l 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 OLE" _
        , _
        "DB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation= _
False;Jet OLEDB:Limited DB Caching=False;" _
        , "Jet OLEDB:Bypass ChoiceField Validation=False")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = _
        "D:\Users\brem\Desktop\MARKT\VBA CSV Stammdaten\Positionsstammdaten\Input (xlms)\1test. _
xlsm"
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("1test Sheet1$")
        .Name = "1test Sheet1$"
        .Description = ""
    End With
    ActiveWorkbook.Connections("1test Sheet1$").Refresh
End Sub


Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Sepp
Geschrieben am: 04.06.2015 13:49:14
Hallo Werner,
lade eine Datei mit dem Code (mit festem Pfad) hoch, hier wird durch die Zeilentrennung der Code ggf. falsch dargestellt.

Gruß Sepp


Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 14:17:51
Hallo Sepp, unter diesem Link kommst du zur Datei:
https://www.herber.de/bbs/user/98029.xlsm

Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Sepp
Geschrieben am: 04.06.2015 14:53:30
Hallo Werner,

Sub Daten_aktualisieren2()
  
  
  ' Pfad Variable definieren
  
  Dim strQuelle As String
  strQuelle = Worksheets("Sheet1").Cells(37, 3).Text
  
  If Dir(strQuelle) = "" Then
    MsgBox "Datei nicht gefunden!", vbExclamation, "Hinweis"
    Exit Sub
  End If
  
  ' Daten_aktualisieren Makro
  
  Sheets("Sheet2").Select
  With ActiveWorkbook.Connections("1test Sheet1$").OLEDBConnection
    .BackgroundQuery = True
    .CommandText = Array("Sheet1$")
    .CommandType = xlCmdTable
    .Connection = _
      Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" _
      & strQuelle & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Re", _
      "gistry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partia", _
      "l 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 OLE", _
      "DB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;", _
      "Jet OLEDB:Bypass ChoiceField Validation=False")
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = ""
    .SourceDataFile = strQuelle
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
  End With
  With ActiveWorkbook.Connections("1test Sheet1$")
    .Name = "1test Sheet1$"
    .Description = ""
  End With
  ActiveWorkbook.Connections("1test Sheet1$").Refresh
End Sub


Gruß Sepp


Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 15:18:17
Hallo Werner, erstmal vielen Dank für deinen unermüdlichen Einsatz.
Ich bekomme leider wieder Fehlermeldung 13, die eingebaute Messagebox springt auch nicht an.
Hat es denn bei dir geklappt bzw. konntest du es nachstellen?

Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 15:20:04
Entschuldige bitte, meinte natürlich Sepp :-)

Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Sepp
Geschrieben am: 04.06.2015 15:40:26
Hallo Werner,
lade doch eine test.xlsm hoch,um den Import zu testen.

Gruß Sepp


Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 15:53:58
https://www.herber.de/bbs/user/98031.zip
Der Link führt zu einer Beispielhaften Datei deren Daten importiert werden sollen. Wenn ich den Dateipfad statisch eingebe, funktioniert alles wunderbar.

Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Sepp
Geschrieben am: 04.06.2015 15:59:53
Hallo Werner,
der Import klappt bei mir mit beiden Makros!
https://www.herber.de/bbs/user/98032.xlsm

Gruß Sepp


Bild

Betrifft: AW: Externer Datenimport von variabler xlsm-Quelle
von: Hans Werner
Geschrieben am: 04.06.2015 16:24:58
Sepp, du bist ein Held! Jetzt klappt alles genau wie gewünscht.
Das letzte Problem war, dass der Dateipfad bei mir zu lang war. Nachdem ich den gekürzt hatte, ging alles einwandfrei. GROSSES DANKESCHÖN!

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Externer Datenimport von variabler xlsm-Quelle"