Makro Datei speichern als ohne Abfrage und mit Zellbezug

[Bloody_Evil]

Hallo ...

Ich bin ziemlich neu auf dem Gebiet VBA/Makros. Das heißt, ich kenne mich nicht so aus, was das Erstellen selbiger betrifft.

Kommen wir zu meinem Problem: 
Ich bin dabei, eine Datei zu erstellen, die Automatisch nach Eingabe der Jahreszahl (und 2 anderen Parametern) einen vordefinierten Schichtplan aufs Jahr ausrollt.
Mittels eines Formulars habe ich eine Maske zum Eingeben des Jahres und der 2 Paramter erstellt, welche per Button in die jeweiligen Zeilen übertragen werden. Das klappt auch wunderbar.

Die Datei ist allerdings nicht nur für mich, was einige "Sicherheitsvorkehrungen" und vereinfachte Anwendungsprozesse erfordert, zum Beispiel wird die Ausgangsdatei schreibgeschützt geöffnet.  

Das eigentliche Problem:

Ich versuche seit 2 Wochen die Einzelteile für ein Makro zu finden, welches folgendes tut:

- die geöffnete Datei mit neuem Dateinamen speichern (Inhalt Zelle A1 (da steht das Jahr drin) und dem Zusatz " - Schichtplan)
         Es wird also eine Datei erstellt, welche z.B. "2019 - Schichtplan.xlsm" heißt
- die neue Datei wird ohne fragen als xlsm gespeichert
- die neue Datei wird ohne zu fragen im gleichem Ordner gespeichert, wie die geöffnete Ausgangsdatei
- sollte eine Datei mit gleichem Namen existent sein, Abfrage, ob überschrieben werden soll, bei "NEIN" wird der Dateiname um einen Zähler erweitert
- anschließend informiert eine Message-Box über diese Aktion "Schichtplan für XXXX (Jahr) erstellt".
- die neue Datei bleibt geöffnet
- die Ausgangsdatei wird ohne Speicherung der Änderung geschlossen.

Ist das machbar?

Vielen Dank schonmal für die Hilfe

LG
Joe

[Guschti]

Hallo Joe

Hier mal ein Beispiel.
Beim öffnen der Vorlage erscheint eine Inputbox. Hier musst du nur die Jahreszahl eintragen.

Code:

Private Sub Workbook_Open()
Dim xDatei As String, yDatei As String, xPfad As String
    xPfad = ThisWorkbook.Path & "\"
        xDatei = ThisWorkbook.Name
            If xDatei <> "Vorlage_Schichtplan.xlsm" Then Exit Sub
                yDatei = InputBox("Bitte Jahr (4-stellig) eingeben")
            If Len(yDatei) <> 4 Then Exit Sub
        If Dir(xPfad & yDatei & "-Schichtplan.xlsm") <> "" Then Exit Sub
    ActiveWorkbook.SaveAs xPfad & yDatei & "-Schichtplan.xlsm"
End Sub

Mfg Guschti

[MisterBurns]

Hallo Joe,

interessante Aufgabe, das mit dem Zähler. Probier mal das:

Code:

Sub Schichtplan()
Dim Pfad As String
Dim Dateiname As String
Dim Typ As String
Dim Zaehler As Integer
Dim Suche As String
Dim Ergebnis As String


Pfad = ActiveWorkbook.Path & "\"
Typ = ".xlsm"
Dateiname = Range("A1") & " - Schichtplan"
Suche = Dateiname & "*" & Typ
Ergebnis = Dir(Pfad & Suche)

If Dir(Pfad & Dateiname & Typ) <> "" Then         'Prüfung, ob Datei schon vorhanden

   If MsgBox("Datei existiert bereits! Möchten Sie diese überschreiben?", vbYesNo) = vbYes Then
   'Wenn vorhanden und MsgBox = Ja, dann überschreiben
       
       Application.DisplayAlerts = False
       ActiveWorkbook.SaveAs Filename:=Pfad & Dateiname & Typ, _
         FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
         CreateBackup:=False
       Application.DisplayAlerts = True
       
   Else
   'Wenn vorhanden und MsgBox = Nein, dann mit Zähler speichern
   
       Do While Ergebnis <> ""
           Zaehler = Zaehler + 1
           Ergebnis = Dir
       Loop
       
       ActiveWorkbook.SaveAs Filename:=Pfad & Dateiname & "_" & Zaehler & Typ, _
         FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
         CreateBackup:=False
       MsgBox "Schichtplan für das Jahr " & Range("A1") & " mit Zähler " & Zaehler & " erstellt."
       Exit Sub
       
   End If

GoTo Ende

End If

'Wenn noch nicht vorhanden, dann neu speichern
   ActiveWorkbook.SaveAs Filename:=Pfad & Dateiname & Typ, _
     FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
     CreateBackup:=False
   

Ende:
MsgBox "Schichtplan für das Jahr " & Range("A1") & " erstellt."

End Sub

[losgehts]

Hallo allerseits,

bitte bei Workbook.SaveAs immer auch den Parameter "FileFormat" mit angeben. Das führt sonst (unter bestimmten Umständen) zu korrupten Dateien.
Hat man den Parameter angegeben, bestimmt Excel selbstständig die richtige Dateiendung - diese kann man (und sollte man meiner Meinung nach) also weglassen.

Daher sieht mein Vorschlag folgendermaßen aus:

Code:

Sub MeinVorschlag()
Dim strFileName As String
Dim strPath As String
Dim lCount As Long

'- die neue Datei wird ohne zu fragen im gleichem Ordner gespeichert, wie die geöffnete Ausgangsdatei
strPath = ActiveWorkbook.Path & "\"
strFileName = strPath & ActiveSheet.Range("A1").Text & " - Schichtplan"

If Dir(strFileName & ".xlsm") <> "" Then
    If Not MsgBox("Die Datei" & vbLf & strFileName & ".xlsm" & vbLf & "existiert bereits." & vbLf & vbLf & _
              "Soll sie überschrieben werden?", vbYesNo) = vbYes Then
        Do
            lCount = lCount + 1
        Loop While Dir(strFileName & "_" & lCount & ".xlsm") <> ""
        strFileName = strFileName & "_" & lCount
    End If
End If

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strFileName, FileFormat:=52
Application.DisplayAlerts = True

MsgBox "Schichtplan für " & ActiveSheet.Range("A1").Text & " erstellt."

End Sub

Ich habe hier mit "ActiveWorkbook" gearbeitet. Evtl. kommt auch "ThisWorkbook" in Frage. Was besser ist, ist abhängig von der Situation, und musst deswegen du, Bloody_Evil entscheiden.

Grüße, Ulrich

PS @Guschti und so ein Makro möchtest du bei jedem Öffnen der Datei und ihrer ganzen Kopien ausgeführt haben?

[Bloody_Evil]

Guten Abend ...

zuerst muss ich um Verzeihung bitte, dass ich erst jetzt reagiere. Ich war im Urlaub und das gänzlich ohne Laptop oder Tablet.

Und jetzt 1000 und einen Dank für Euro Hilfe.
Ich habe erstmal die Version von @MisterBurns eingebaut und erste Tests sind offenbar gänzlich genau das, was ich wollte.
@losgehts: Deine Lösung schaue ich mir die Tage an bzw werde es einbauen und ebenfalls testen. 
@ Guschti: Deine Lösung ist auch eine gute Variante, allerdings korelliert diese nicht mit meinem bereits erstelltem Formular, welches sich lediglich auf Knopfdruck öffnet. Allerdings habe ich Deine Möglichkeit in einem anderen Projekt leicht angepasst verwenden können.

Und wie ich sehe, bin ich hier gut aufgehoben auf meinem Weg in die Tiefen des Exceluniversums  :100:
In diesem Sinne, bis zum nächsten Problem (werde natürlich regelmäßig hier mitlesen).

Liebe Grüße

[Rabe]

Hi,

generell speichere ich Excel-Dateien (besonders solche mit Makros) als XLSB (binäre Exel-Datei; FileFormat := 50) ab, dann ist sie nochmal ein Stück kleiner als xlsm, und ich muß mir keine Gedanken machen, welchen Typ ich jetzt beim Abspeichern einstellen muß.

In all den Jahren bisher konnte ich noch von keinen negativen Ergebnissen dieser Vorgehensweise erfahren.

These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

siehe: https://www.rondebruin.nl/win/s5/win001.htm

[losgehts]

Hallo Rabe,

ich kann deine Motivation verstehen. Bei mir ist das ein wenig anders. Gerade heutzutage, wo Speicherplatz relativ billig ist, habe ich meine Prioritäten (insbesondere in der Kommunikation mit anderen) anders gesetzt:

1) Sicherheit
Ganz oben stehen für mich Sicherheitsaspekte. Einer der Vorteile der OpenXML-Dateiformate (ich meine *.xlsm) ist, dass man automatisiert den VBA-Code aus einer Datei entfernen kann (z.B. für größere Firmen wichtig), ohne diese mit Excel öffnen zu müssen. Der Inhalt (Tabellenblätter) ist dann trotzdem noch sichtbar.
Und ich finde es extrem vorteilhaft, einer Exceldatei vor dem Öffnen anzusehen, ob sie potentiell Makros beeinhaltet (eine *.xlsx öffne ich einfach so, ohne nachzudenken, bei einer *.xlsm oder *.xlsb verhalte ich mich anders).
Ich würde also niemals meinem Kommunikationspartner eine *.xlsm oder *.xlsb "zumuten", ohne dass da VBA-Code drin ist. Eben weil ich von jedem verantwortungsvollen Menschen, der weiß, was die Dateiformate bedeuten, erwarte, dass bei ihm/ihr die Alarmglocken schrillen.
Außerdem erhoffe ich mir, mehr Chancen, durch den Spamfilter des anderen zu kommen mit einer xlsx-Datei, als mit einer xlsb-Datei.

2) Inhaltliche Klarheit

3) ....

7) Archiv
Ich glaube, dass ich noch lange software finden werde, die mir Dateien entzippt und andere, mit der ich Textdateien lesen kann. Daher glaube ich, dass xlsx und xlsm langfristig die "sichereren" Dateiformate sind, als xlsb (im Sinne von "ich kann die Inhalte auf den Bildschirm bekommen).

8) Speicherbedarf

So mach ich das eben.

Grüße,
Ulrich