Datenabfrage Excel File aus Zelle
21.12.2013 14:23:45
Silent_Warrior5
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