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 CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Userform
BildScreenshot zu Userform Userform-Seite mit Beispielarbeitsmappe aufrufen

Macro ausführen

Betrifft: Macro ausführen von: Thomas
Geschrieben am: 11.10.2020 01:53:29

Hallo Excelfreunde,

ich habe eine Exceldatei (arbeits.xlsm) die mehrmals am Tag geöffnet wird. ( schreibgeschützt)


Einmal in der Woche soll beim öffnen der Datei erst noch ein Makro gestartet werden.


Sub Workbook_Open()

call testmacro

End Sub


Deshalb habe ich mir gedacht, ich benutze eine weitere Exceldatei in der das Datum ( letztes Ausführungsdatum vom call testmacro) in der Bezeichnung enthalten ist. z.B. (mill_20201011.xlsm) . Beim öffnen der arbeits.xlsm wird das Datum in der Bezeichnung dann geprüft ob das testmacro diese Woche schon ausgeführt wurde.


Das Macro habe ich fertig und es funktioniert schon ganz gut.


Mein Problem ist sobald wenn jemand meine Exceldatei z.B. mill_20201011.xlsm löscht funktioniert das ganze natürlich nicht mehr.


Ich schaffe es einfach nicht diesen möglichen Fehler abzufangen. Mein Versuch eine neue Exceldatei mit den Name "mill_.xls" zu erstellen funktioniert einfach nicht. Ich erhalte beim ersten Start immer den Fehler " Fehler beim Zugriff auf Datei und Pfad".


Wenn ich diesen Fehler bestätige und meine Datei das zweite mal öffne funktioniert es ohne Fehlermeldung.


Hat jemand einen Rat für mich?.



Vielleicht weiß auch jemand eine Möglichkeit dies leichter zu machen. Vielleicht mit einer Textdatei in der das ausführungsdatum protokolliert und dann verglichen wird.


mfg thomas



Sub neumappe()


Application.ScreenUpdating = False

Dim strTmpPath As String

Dim strTmpName As String


strTmpPath = "c:\test\"

strTmpName = "mill_.xls"

Workbooks.Add

ActiveWorkbook.SaveAs Filename:=strTmpPath & strTmpName

ActiveWorkbook.Close

End Sub




Private





Sub CommandButton1_Click()

Dim s$
'Const Pfad = "c:\test\"
Const Datei = "mill_*.*"

Const Pfad As String = "c:\test\"
'prüfen ob datei existieren

 s = Dir(Pfad & Datei)
         
        If Len(s) > 0 Then
        MsgBox " Datei ist da "
         s = Dir(Pfad & Datei)
         
         Else
         'falls nicht bitte erstellen
Call neumappe 


        ' MsgBox " Datei fehlt"
         
        End If


Dim DateiName As String, Endung As String, BackupDatei As String

If Weekday(Date, vbMonday) = 7 Then   'hier den tag einstellen

  ' wenn montag ist
    'dateiname ohne endung
  DateiName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1)
  Endung = ".xls" 'Mid(ThisWorkbook.Name, Len(DateiName) + 1) '".xls
  BackupDatei = DateiName & "_" & Format(Date, "YYYYMMDD") & Endung
  
  If Dir(Pfad & BackupDatei) = "" Then

'Datei umbenennen
Name s As BackupDatei

'call testmacro
  
   ' ThisWorkbook.SaveCopyAs Pfad & BackupDatei
Else
  'ThisWorkbook.SaveCopyAs Pfad & BackupDatei
    'datei schon gebackupt
  End If
  
  
End If


Betrifft: AW: Macro ausführen
von: Beverly
Geschrieben am: 11.10.2020 09:58:50

Hi,

wozu eine extra Arbeitsmappe? Schreibe das Datum doch einfach in ein Tabellenblatt, welches du ausblendest - dort kannst du es genau so gut prüfen.


GrußformelBeverly's Excel - Inn

Betrifft: AW: Macro ausführen
von: Thomas
Geschrieben am: 11.10.2020 11:21:30

Hallo Beverly,

erstmal besten dank das du dir dies mal angeschaut hast.

In dieser Datei kann ich leider nichts speichern.

Sie wird über VBS schreibgeschützt geöffnet. Anschließend wird gleich eine Userform gestartet.

Diese dient nur dazu Daten aus Access zu Holen und zu bearbeiten. Durch diese Variante können viele Benutzer gleichzeitig Daten bearbeiten. Diese Geschichte funktioniert auch schon perfekt ( aus meiner Sicht).
Der Hintergrund ist das ich in Access nun alle Daten löschen muss die älter sind als 12 Monate. ( Datenschutzregel). Ich habe auch schon versucht dies mit diesem
Sub loeschen()
 SQL = "DELETE FROM TB_Daten WHERE [Datum_Fund] < Date() - 5"
     'DoCmd.SetWarnings False
     'DoCmd.RunSQL SQL
     'DoCmd.SetWarnings False
     'dürfte schneller sein
     DBEngine(0)(0).Execute SQL
End Sub
in Access selbst umzusetzen. Aber ich habe keine Möglichkeit gefunden wie ich das dann ein mal im Monat starten kann. Die meisten Benutzer haben kein Access installiert deshalb läuft die Userform über DAO bzw. Late Binding. Aus diesem Grund wird Access selbst nie gestartet. ( außer bei Wartung )

Deshalb habe ich mir dieses Macro

#Const conLateBinding = True
Sub FilterTest()
    'Dim ws As Worksheet
    Dim strDB As String, strCon As String
    Dim strTab As String
    Dim strVorname As String
    Dim strNachname As String, strSQL As String, i As Integer
    
        
    #If conLateBinding Then ' z.Z aktiv, da Variable auf 'Trie' gesetzt
        'Late Binding - kein gesetzter Verweis erforderlich
        Dim oCon As Object
        Dim oRstFilter As Object
        'Dim oFld As Object
   
        Set oCon = CreateObject("ADODB.Connection")
        Set oRstFilter = CreateObject("ADODB.Recordset")
        'Set oFld = CreateObject("ADODB.Field")
    #Else
        'Early Binding
        'Verweis auf Microsoft ActiveX Data Objects X.Y Library notwendig
        Dim oCon As New ADODB.Connection
        Dim oRst As New ADODB.Recordset
        Dim oRstFilter As New ADODB.Recordset
    #End If
    
    
    strDB = ThisWorkbook.Path & "\FDatenbank.accdb" 'Hierhin wird exportiert
    
   
       
    'Datenverbindung
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
    


strSQL = "DELETE FROM TB_Daten WHERE [Datum_Fund] < Date() - 100"

    'Datenverbindung öffnen
    'geht
    oCon.Open strCon
    'Filter setzen
      
    
    oRstFilter.Open strSQL, oCon, 0, 3
    
    'oRstFilter.Close
    oCon.Close

    Set oCon = Nothing
    Set oRstFilter = Nothing
    'Set oFld = Nothing
    'Set ws = Nothing
    MsgBox "F e r t i g!", vbSystemModal + 64, "zur Ibnfo..."
End Sub
zum löschen der Daten zusammengestückelt. Was auch schon super funktioniert.

Nun suche ich nach einer Möglichkeit dieses Macro einmal im Monat mit dem öffnen der Excelhauptdatei zu starten.

Der oben genannte Versuch ist jetzt erstmal meine einzige Variante die ich geschafft habe umzusetzen. ( Außer die oben genannte Fehlermeldung ).

Die ideale Variante wäre bestimmt das Datum "letzte Ausführung" in eine Tabelle von Access zu schreiben und dann irgendwie zu nutzen ( dann hätte ich auch eine Historie). Aber daran habe ich mir schon seid Wochen die Zähne ausgebissen. (vielleicht versuche ich diese Idee dochnochmal)


Ich glaube meine Fehlermeldung kommt daher weil ich bei Macroausführung den Inhalt der Variabel a verliere.

Aber ich weiß einfach nicht warum.


mfg thomas

Betrifft: AW: Macro ausführen
von: Beverly
Geschrieben am: 11.10.2020 11:27:30

Hi Thomas,

Zitat: "Sie wird ... schreibgeschützt geöffnet."

Und wo ist das Problem, den Schutz aufzuheben, das Datum in das Tabellenblatt einzutragen und dann den Schutz wieder zu setzen?


GrußformelBeverly's Excel - Inn

Betrifft: AW: Macro ausführen
von: Thomas
Geschrieben am: 11.10.2020 11:54:40

Hallo Beverly,

sorry ich habe eine wichtige Information nicht geschrieben.

Die Hauptdatei wird mit VBS gestartet. Bei jedem Start wird eine neue Kopie für den jeweiligen Nutzer erstellt. Mit dieser arbeitet dann der Nutzer.

Dadurch sind alle Daten die der Nutzer erstellt hat, beim schließen der Hauptdatei (Excel Kopie) wieder aus Excel gelöscht. Die Datenhaltung wird mit der Accessdatenbank realisiert. ( LateBinding )



mfg thomas

Betrifft: AW: Macro ausführen
von: Beverly
Geschrieben am: 11.10.2020 14:24:58

Hi Thomas,

wie wird die Kopie denn erstellt - indem die Hauptdatei geöffnet und dann daraus die Kopie erstellt wird? Damit sollte es doch kein Problem sein, das Datum mit dem Workbook_Open-Ereignis der Hauptdatei in die Hauptdatei zu schreiben um es bei jedem Öffnen erneut prüfen zu können.


GrußformelBeverly's Excel - Inn

Betrifft: AW: Macro ausführen
von: ralf_b
Geschrieben am: 11.10.2020 12:51:51

zitat: Die ideale Variante wäre bestimmt das Datum "letzte Ausführung" in eine Tabelle von Access zu schreiben und dann irgendwie zu nutzen
- stimmt,denn zugriff hast du und Abfragen kannst du auch irgendwie.

zitat: Ich glaube meine Fehlermeldung kommt daher weil ich bei Macroausführung den Inhalt der Variabel a verliere. Aber ich weiß einfach nicht warum.
- wo ist die Variable a. hab sie leider nicht gefunden?

Betrifft: AW: Macro ausführen
von: Thomas
Geschrieben am: 11.10.2020 12:58:15

Hallo ralf_b,

auch dir besten dank das du dir dies mal durchgelesen hast.


Mist bin schon ganz durch den Wind.

es ist die Variable

Dim s$


Sorry das ich dich verwirrt habe.


mfg Thomas

Betrifft: AW: Macro ausführen
von: ralf_b
Geschrieben am: 11.10.2020 13:19:30

Variable s ist nur im Commandbutton1_click bekannt. wenn du ein Makro aufrufst und die Variable dort benötigst, dann übergibt sie bei Aufruf oder deklariere sie global.

Betrifft: AW: Macro ausführen
von: Thomas
Geschrieben am: 11.10.2020 20:19:56

Hallo ralf_b,

das ganze läuft jetzt.

Hab besten dank für deine Tipps.

mfg thomas

Betrifft: gern, danke für die rückmeldung owt
von: ralf_b
Geschrieben am: 11.10.2020 22:10:56