Office: Datenabfrage aus Excel File aber Daten aus Zelle

Hallo Zusammen,

habe folgendes Makro aufgezeichnet und komme nicht weiter, das ganze variabel aus der Zelle zu holen.



Sub Makro7()
'
' Makro7 Makro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=D:\zMichael Work\Versuch1\- 27.08.13 Di\blabla.xlsx;DefaultDir=D:\zM" _
), Array( _
"ichael Work\Versuch1\- 27.08.13 Di;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT Reihe1$`.Reihe2, `Reihe3$`.Reihe4, `Reihe5$`.Reihe6, `Reihe7$`......
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Tabelle_Abfrage_von_Excel_Files99"
.Refresh BackgroundQuery:=False
End With
Range("E9").Select
ActiveSheet.ListObjects("Tabelle_Abfrage_von_Excel_Files99").Unlist
Range("A1").Select
ActiveWindow.SmallScroll Down:=-2
End Sub




hier mein kläglicher Versuch:




Sub Makro7()
'
' Makro7 Makro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="Worksheets(""Tabelle1"").Range(""D1"").Value;DefaultDir=Worksheets(""Tabelle1"").Range(""D8"").Value;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Worksheets("Tabelle1").Range("D4").Value
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Tabelle_Abfrage_von_Excel_Files99"
.Refresh BackgroundQuery:=False
End With
Range("E9").Select
ActiveSheet.ListObjects("Tabelle_Abfrage_von_Excel_Files99").Unlist
Range("A1").Select
ActiveWindow.SmallScroll Down:=-2
End Sub



Kann mir da jemand bitte helfen?


Danke & Gruß

Michi