AW: Datenübername aus anderer Datei
11.05.2018 18:00:14
Sepp
Hallo Patrick,
probier mal.
Modul Modul1
Option Explicit
Sub getData()
Dim objWB As Workbook, objRange As Object, bolAlreadyOpen As Boolean
Const cstrFilePath As String = "D:\Forum\test.xlsx" 'Datei aus der die Daten geholt werden - ANPASSEN!
Const cstrTargetTabel As String = "Tabelle1" 'Tabellenname in der Zieldatei - ANPASSEN!
On Error GoTo ErrorHandler
With Application
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
For Each objWB In Application.Workbooks
If objWB.FullName = cstrFilePath Then bolAlreadyOpen = True: Exit For
Next
If objWB Is Nothing Then Set objWB = Workbooks.Open(cstrFilePath)
With ThisWorkbook.Sheets(cstrTargetTabel)
On Error Resume Next
Set objRange = Application.InputBox("Bitte Zeile auswählen!", "Daten kopieren", ActiveCell.Address, Type:=8)
Err.Clear: On Error GoTo ErrorHandler
If Not objRange Is Nothing Then
Set objRange = objRange.Cells(1, 1).EntireRow
.Range("F11") = objRange.Cells(1, 1).Value
.Range("K11") = objRange.Cells(1, 2).Value
.Range("T7") = objRange.Cells(1, 4).Value
.Range("K7") = objRange.Cells(1, 5).Value
.Range("F13") = objRange.Cells(1, 9).Value
.Range("F9") = objRange.Cells(1, 11).Value
.Range("K9") = objRange.Cells(1, 12).Value
End If
End With
If bolAlreadyOpen Then objWB.Close False
ErrorHandler:
If Err.Number <> 0 Then
MsgBox "Fehler in Modul1" & vbLf & vbLf & "Prozedur:" & vbTab & "getData" & vbLf & _
"Nummer:" & vbTab & Err.Number & vbLf & "Meldung:" & vbTab & Err.Description & vbLf & _
IIf(Erl, "Zeile:" & vbTab & Erl, ""), vbExclamation, "Fehler!"
Err.Clear
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
Set objRange = Nothing
Set objWB = Nothing
End Sub
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0