Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Frame
BildScreenshot zu Frame Frame-Seite mit Beispielarbeitsmappe aufrufen

Bezug zu anderen Dateien herstellen

Betrifft: Bezug zu anderen Dateien herstellen von: Barbara
Geschrieben am: 05.08.2014 00:26:18

Hallo

würde gerne Folgendes machen:
(Beispieldateien habe ich hochgeladen)

In dieser Datei für Dateneingabe
https://www.herber.de/bbs/user/91885.xlsm
werden Daten eingegeben, mit denen in einer anderen Datei für Berechnungen
https://www.herber.de/bbs/user/91884.xlsx
ein Ergebnis berechnet wird. Dieses Ergebnis soll in die Dateneingabe-Datei eingetragen werden, und zwar live, also bei jeder Änderung der Ausgangsdaten sofort neu.

Dieses Makro soll erst zur Wahl einer anderen Datei auffordern, die dann auch geöffnet wird und dann per Buttonklick wieder beendet werden können. Bis dahin soll diese Live-Beziehung bestehen bleiben.

in meinem konkreten Fall (nicht in den hochgeladenen Dateien) sind die Formeln in der Berechnungsdatei gesperrt, ich kann die Formel also nicht einsehen und auch keine Änderungen in dieser Datei durchführen, außer die bestimmten Angaben zur Berechnung. Es soll also alles von der ersten Datei gesteuert werden.

Ich weiß, es würde auch ohne Makro gehen, wenn die Pfade immer gleich wären, aber dem ist nicht so.

Würde mich über Unterstützung zu diesem schwierigen Problem freuen.

LG,
Barbara

  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: fcs
Geschrieben am: 05.08.2014 11:21:16

Hallo Barbara,

hier Makros, die du in deine Eingabedatei einbauen muss.
Die Makros zum Öffnen/Schließen der Berechnungsdatei kannst du dann Schaltflächen aus den Formular-Steuerelementen zuweisen.

Pfad und Name der jeweils geöffneten Berechnungsdatei werden in "Tabelle2" eingetragen. Wenn du diese Infos im Eingabeblatt haben willst, dann musst du den Blattnamen und die Zelladressen in allen relevanten Code-Zeilen anpassen.

Gruß
Franz

'Makros in einem allgemeinen Modul der Eingabedatei

Sub Berechnungsdatei_Oeffnen()
    Dim varDatei As Variant
    Dim wkb As Workbook
    Dim wksMerk As Worksheet
    'Tabellenblatt in dem Pfad und Name der geöffneten Berechnungsdatei eingetragen werden
    Set wksMerk = ThisWorkbook.Worksheets("Tabelle2")
    
    If wksMerk.Range("B3") <> "" Then
      MsgBox "Es ist evtl. noch die Datei """ & wksMerk.Range("B3") _
          & """ geöffnet, bitte erst diese Datei schliessen"
    Else
      With Application.FileDialog(msoFileDialogOpen)
        .Title = "Bitte Berechnungsdatei auswählen und öffnen"
        .AllowMultiSelect = False
        If .Show = -1 Then
          varDatei = .SelectedItems(1)
          'Berechnungsdatei schreibgeschützt öffnen
          Set wkb = Application.Workbooks.Open(Filename:=varDatei, ReadOnly:=True, _
              addtomru:=False)
          wksMerk.Range("B2") = wkb.Path
          wksMerk.Range("B3") = wkb.Name
          ThisWorkbook.Activate
        Else
          'do nothing - Dateiauswahl abgebrochen
        End If
      End With
    End If
End Sub

Sub Berechnungsdatei_Schliessen()
    Dim wkb As Workbook
    Dim wksMerk As Worksheet
    'Tabellenblatt in dem Pfad und Name der geöffneten Berechnungsdatei eingetragen werden
    Set wksMerk = ThisWorkbook.Worksheets("Tabelle2")
    If wksMerk.Range("B3") <> "" Then
      For Each wkb In Application.Workbooks
        If wkb.Name = wksMerk.Range("B3") Then
          wkb.Close savechanges:=False
          Exit For
        End If
      Next
      wksMerk.Range("B2").ClearContents
      wksMerk.Range("B3").ClearContents
    Else
      MsgBox "In Tabelle """ & wksMerk.Name _
          & """ ist in Zelle B3 kein Dateiname eingetragen"
    End If
End Sub

Sub Berechnen()
    Dim wkb As Workbook
    Dim wksMerk As Worksheet
    Dim wksEingabe As Worksheet
    Dim wksRechnen As Worksheet
    Dim strMsg As String
    'Tabellenblatt in dem Pfad und Name der geöffneten Berechnungsdatei eingetragen werden
    Set wksMerk = ThisWorkbook.Worksheets("Tabelle2")
    Set wksEingabe = ActiveSheet
    If wksMerk.Range("B3").Value <> "" Then
      'Berechnungsdatei unter den geöffneten Dateien suchen
      For Each wkb In Application.Workbooks
        If wkb.Name = wksMerk.Range("B3").Value Then
          Exit For
        End If
      Next
      If wkb Is Nothing Then
        MsgBox "Die in Tabelle """ & wksMerk.Name _
          & """ in Zelle B3 eingetragene Datei ist nicht geöffnet." _
          & "Bitte erst Berechnungsdatei öffnen"
        wksMerk.Range("B2").ClearContents
        wksMerk.Range("B3").ClearContents
      Else
        'Eingabewerte prüfen - vorhandene Werte/Wertebereiche
        With wksEingabe
          strMsg = ""
          With .Range("A2") 'Laufzeit
            If .Value = "" Then
              strMsg = strMsg & vbLf & "Es ist keine Laufzeit in A2 eingetragen"
            ElseIf .Value < 1 Or .Value > 600 Then
              strMsg = strMsg & vbLf & "Laufzeit in A2 ist außerhalb Bereich 1 bis 600"
            End If
          End With
          With .Range("A3") 'Rabatt
            If .Value = "" Then
              strMsg = strMsg & vbLf & "Es ist kein Rabatt in A3 eingetragen"
            ElseIf .Value < 0 Or .Value > 1 Then
              strMsg = strMsg & vbLf & "Rabatt in A3 muss im Bereich 0% bis 100% "
            End If
          End With
        End With
        If strMsg <> "" Then
          MsgBox "Eingabefehler:" & strMsg, vbInformation + vbOKOnly, "Prüfung Eingabewerte"
          Exit Sub
        End If
        
        Set wksRechnen = wkb.Worksheets(1)
        'Eingabe-Werte in Berechnungsblatt eintragen
        Application.Calculation = xlCalculationManual
        wksRechnen.Range("B4").Value = wksEingabe.Range("A2").Value 'Laufzeit
        wksRechnen.Range("B5").Value = wksEingabe.Range("A3").Value 'Rabatt
        
        Application.Calculate
        'Ergebnis in Eingabeblatt übernehmen
        wksEingabe.Range("A4") = wksRechnen.Range("B6").Value  'Ergebnis - Miete
        Application.Calculation = xlCalculationAutomatic
      End If
    Else
      MsgBox "In Tabelle """ & wksMerk.Name _
        & """ ist in Zelle B3 kein Dateiname eingetragen. " _
        & "Bitte erst Berechnungsdatei öffnen"
    End If
End Sub
'Code im VBA-Editor unter dem Eingabe-Tabellenblatt

Private Sub Worksheet_Change(ByVal Target As Range)
  'Startet automatisch Berechnung wenn einer der Eingabewerte geändert wird
  Select Case Target.Address(False, False, xlA1)
    Case "A2", "A3"
      If Not IsEmpty(Range("A2")) And Not IsEmpty(Range("A3")) Then
        Call Berechnen
      End If
  End Select
End Sub

'Code im VBA-Editor unter dem Eingabe-Tabellenblatt

Private Sub Worksheet_Change(ByVal Target As Range)
  'Startet automatisch Berechnung wenn einer der Eingabewerte geändert wird
  Select Case Target.Address(False, False, xlA1)
    Case "A2", "A3"
      If Not IsEmpty(Range("A2")) And Not IsEmpty(Range("A3")) Then
        Call Berechnen
      End If
  End Select
End Sub




  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: Barbara
Geschrieben am: 07.08.2014 22:19:06

Hi Franz,
danke für Dein großartiges Beispiel.

Ich habe es nun etwas umgebaut, weil es so besser meinen Bedürfnissen entspricht und das Ergebnis hier hoch geladen. Vielleicht könntest Du einen Blick darauf werden, denn ich hätte da noch ein paar Fragen.

Das ist die Eingabedatei:
https://www.herber.de/bbs/user/91960.xlsm

Das ist die Berechnungsdatei:
https://www.herber.de/bbs/user/91961.xlsm

Wie Du siehst, hat die Eingabedatei zwei Bereiche:

Der Bereich "Eingang" beinhaltet die zu übergebenen Werte und die Blätter und Zielzellen der Berechnungsdatei.

Der Bereich "Ergebnis" hat die Blätter und Rückgabezellen der Berechnungsdatei und die übernommenen Werte.

Gestartet wird mit "Berechnungsdatei_Oeffnen()".
Wenn ein Wert geändert wird, legt sofort "Berechnen()" los.
Beendet wird mit "Berechnungsdatei_Schliessen()".


Frage 1:
Ich habe für die Abfrage und Rückgabe die Bereiche um jeweils die erste Zeile reduziert.

For Each rZeile In Range("Eingang").Resize(Range("Eingang").Rows.Count - 1, Range("Eingang").Columns.Count).Offset(1, 0).Rows

Das geht, aber geht das auch eleganter und einfacher?

Frage 2:
Kann man nicht gleich den Pfad der aktuellen Datei voreinstellen?

Frage 3:
Ich habe versucht, einen Button zu erstellen, mit dem man "Berechnungsdatei_Oeffnen()" aufruft. Doch weiß ich nicht, wie das geht.

Der Buttontext soll dann während der Berechnung auch gleich den Namen der Datei haben, damit man nicht eine Zelle damit belastet.

Mit diesem Button soll man das auch wieder beenden können. Dass der Anwender die Berechnungsdatei selbst abspeichern kann, ist Absicht.

Während die Berechnung aktiv ist, soll der Button idealerweise eine andere Farben haben. Habe dazu einiges probiert, komme aber auf keinen grünen Zweig.

Entschuldige meine späte Antwort, aber in Deinem Makro habe ich so viel neue Infos gefunden, dass es etwas mehr Zeit gebraucht hat, das alles zu analysieren. Danke nochmals.

LG,
Barbara


  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: fcs
Geschrieben am: 08.08.2014 10:32:43

Hallo Barbara,

leider funktioniert der Link auf die Berechnungs-Datei nicht korrekt.
Würdest du diese Datei bite nochmals hochladen.

Gruß
Franz


  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: fcs
Geschrieben am: 08.08.2014 13:22:53

Hallo Babara,

Frage 1:
Das Makro sieht etwas einfacher aus, wenn man den Bereichen nur die Zellen der 1. Spalte der Tabellen zuweist. Blattname und Zelle kann man dann mit Offset relativ zur Zelle in der 1. Spalte ermitteln.
Hab ich mal so eingebaut.

Frage 2:
Man kann für den Dialog einen InitialFilename vorgeben, in dem der Pfad enthalten ist.
Hab ich gemacht.

Frage 3:
Du fügst im Tabellenblatt eine Form ein oder auch via Menü Entwicklertools ein Formular-Steuerelement.
Entweder wird dann direkt der Makro-Auswahldialog angezeigt oder nach Rechte-Maus-Klick auf Objekt und "Makro zuweisen...".

Ich hab mal alles in deine Datei eingebaut (Farbwechsel/Textwechsel der Form, Öffnen/Schließen über ein Element).
Zusätzlich ist unter "DieseArbeitsmappe" etwas Code zur Prüfung, ob die Berechnungsdatei wieder geschlossen wurde.

Gruß
Franz
https://www.herber.de/bbs/user/91970.xlsm


  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: Barbara
Geschrieben am: 10.08.2014 11:48:46

Hallo Franz,

hier nochmals die abrufgetestete Berechnungsdatei
https://www.herber.de/bbs/user/91997.xlsx

Zu Deinem neuen Beispiel 91970:

Frage 1 und 2: Beantwortet. Danke sehr.

Frage 3:
Deim makro entnehme ich, dass es ein "Abgerundetes Rechteck 2" gibt, das höchstwahrscheinlich das abgerundete Rechteck im Tabellenblatt ist.

Aber: Wo sehe ich, dass es das wirklich ist? Bzw: Wo kann man in Excel sehen, dass das abgerundete Rechteck diesen Namen trägt?

Neue Frage 4:
Ich habe zum Ausprobieren Berechnen() gestartet, aber nicht beendet, und mich dann gewundert, dass so einiges nicht geht. Bis ich drauf gekommen bin, woran es liegt. Daher meine Frage: Zu "Application.EnableEvents": Kann man das auch händisch in Excel steuern?

LG,
Barbara


  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: fcs
Geschrieben am: 11.08.2014 07:52:41

Hallo Barbara,

Frage 3: Name Form/Shape
Bei Formen, denen ein Makro zugewiesen ist: Wenn du mit Rechte-Maus-Klick auf die Form klicks, dann wird in dem Feld, in dem normalerweise die Adresse der aktiven Zelle angezeigt wird, der Name der selektierten Form angezeigt.
Ohne zugewiesenes Makro reicht die Selektion mit einem einfachen Klick.


Frage 4: EnableEvents
Standardmäßig sind die Ereignismakros in Excel aktiv, es sei denn eine Datei wird ohne Aktivieren der Makros geöffnet.

Steuern kannst du dies nur per Makro. Wenn du öfters zwischen "aktiv" und "inaktiv" umschalten möchtest, dann solltest du dir in der persönlichen Makro-Arbeitsmappe 2 entsprechende Makros anlegen und diese dann per Schaltflächen in einem persönlichen Menüband oder der Symbolleiste für den Schnellzugriff starten.

Gruß
Franz

Sub Anw_Events_Ein()
  'Ereignismakros aktivieren
  Application.EnableEvents = True
End Sub

Sub Anw_Events_Aus()
  'Ereignismakros deaktivieren
  Application.EnableEvents = False
End Sub



  

Betrifft: AW: Bezug zu anderen Dateien herstellen von: Barbara
Geschrieben am: 14.08.2014 15:19:56

Hallo Franz,
danke, ist nun klar.

Noch einige Fragen zu Deiner hochgeladenen Datei
https://www.herber.de/bbs/user/91970.xlsm

1.) Im Makro Berechnen steht:

With objShape
.Fill.ForeColor.RGB = RGB(Red:=255, Green:=255, Blue:=0) 'gelb
End With

Hat das einen bestimmten Grund? Wäre nicht

objShape.Fill.ForeColor.RGB = RGB(Red:=255, Green:=255, Blue:=0) einfacher? (Kann ja sein, dass das einen bestimmten Grund hat)

2.) Im Makro Berechnen steht:

strDatei = objShape.TextFrame.Characters.Text

Da wollte ich mir objShape.TextFrame im Überwachungsfenster näher ansehen. Aber da wird Characters
nicht angeboten. Hat das einen bestimmten Grund? Ich würde nie auf die Idee kommen, Characters zu schreiben, wenn es nicht irgend wo ein Hinweis darauf gibt.

LG,
Barbara


 

Beiträge aus den Excel-Beispielen zum Thema "Bezug zu anderen Dateien herstellen"