|
Excel VBA read data from closed workbook with ADODB ginge vermutlich schneller, aber Walkenbach tut es auch
Sub altWerteHolen()
Dim strPfad As String
Dim strFileName As String
Dim strSheetName As String
Dim strZellAdresse As String
'
Dim lngZeile As Long
'
Dim arrWerte()
Dim ax As Long
Dim c As Range
'Pfad
strPfad = "E:\VBA\" 'anpassen
'Filename
strFileName = "Mappe2.xlsx" 'ditto
'Blattname
strSheetName = "Sheet1"
'Bereich A2 - An
With ThisWorkbook.Sheets("Artikelstammdaten")
lngZeile = .Cells(.Rows.Count, 1).End(xlUp).Row
'Array einrichten und mit Walkenbach - Abfrage füllen
ReDim arrWerte(1 To lngZeile - 1, 2)
For ax = LBound(arrWerte, 1) To UBound(arrWerte, 1)
arrWerte(ax, 1) = GetValue(strPfad, strFileName, strSheetName, "C" & ax)
arrWerte(ax, 2) = GetValue(strPfad, strFileName, strSheetName, "K" & ax)
Next ax
'Array auswerten
For ax = LBound(arrWerte, 1) To UBound(arrWerte, 1)
Set c = .Columns(1).Find(What:=arrWerte(ax, 1), LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then c.Offset(, 2).Value = arrWerte(ax, 2)
Next ax
End With
End Sub
|