Dateipfad aus Zelle beim übertragen von Umsätzen
28.08.2015 12:07:13
Umsätzen
Ich habe einen VBA Code geschrieben. Dieser funktionier auch soweit ganz gut und macht was er soll:
'Überträg den Umsatz aus der Datei, welche zuerst ausgewählt wird in das Budget Excel
Option Explicit
Dim Stamm As String
Dim varFile As Variant
Dim varName As Variant
Dim Blatt As String
Public Sub Umsatz_übertragen()
On Error GoTo Err
Stamm = ActiveWorkbook.Name
varFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", , "Datei mit dem _
aktuellen Umsatz Budget auswählen", False)
If TypeName(varFile) Like "Boolean" Then
MsgBox "Keine Datei gewählt!", vbInformation
Exit Sub
Else
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
varName = Right$(varFile, Len(varFile) - InStrRev(varFile, "\"))
Workbooks.Open varFile
Blatt = ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name
Workbooks(Stamm).Sheets("Umsatz").Range("E10").Value = Blatt 'schreibt den Dateipfad in _
Zelle E10
Workbooks(Stamm).Sheets("Umsatz").Range("E13:P59").FormulaR1C1 = "=SUMPRODUCT(--('[varName] _
Sales'!R6C3:R400C3=RC4),'[varName]Sales'!R6C[57]:R400C[57])"
Workbooks(varName).Close
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic 'Automatische Berechnung aktivieren
Workbooks(Stamm).Sheets("Umsatz").Range("E13:P59").Copy
Workbooks(Stamm).Sheets("Umsatz").Range("E13:P59").PasteSpecial xlPasteValues
Application.DisplayAlerts = False
Workbooks(Stamm).Sheets("Umsatz").Range("B80:P140").Copy
Range("B12:P72").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("A9").Select
Application.DisplayAlerts = True
End If
Exit Sub
Err:
Call MsgBox("Bitte überprüffen ob die Tabellen" _
& vbCrLf & "vohanden sind" _
, vbExclamation, "Fehler")
End Sub
Beim Eintragen der Formel Summenprodukt will er aufgrund des [varname] die Formel aktualisieren und findet natürlich keine Datei.
Ich habe es bereits mit Indirekt versucht, schaffte es aber nicht.
Das Makro sollte über einen Button gestartet werden und aus einer Datei die man auswählen kann, die Daten mit Summenprodukt zusammenzählen. Damit man sieht welche Datei ausgewählt wurde, wird der Pfad in Zelle E10 geschrieben. Anschliessend sollten die Formeln wieder gelöscht werden.
Das Makro funktioniert gut, jedoch muss man 2x die Datei auswählen. Gibt es eine Möglichkeit dies zu unterdrücken, so dass man nur noch einmal die Datei auswählen muss?
Gruss
Tom