Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Makro Datei speichern als ohne Abfrage und mit Zellbezug
#1
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
Antwortento top
#2
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


Angehängte Dateien
.xlsm   Vorlage_Schichtplan.xlsm (Größe: 14,26 KB / Downloads: 3)
Der Künstler lebt auch vom Applaus
[-] Folgende(r) 1 Benutzer sagt Danke an Guschti für diesen Beitrag:
  • Bloody_Evil
Antwortento top
#3
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
Schöne Grüße
Berni
[-] Folgende(r) 1 Benutzer sagt Danke an MisterBurns für diesen Beitrag:
  • Bloody_Evil
Antwortento top
#4
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?
[-] Folgende(r) 1 Benutzer sagt Danke an losgehts für diesen Beitrag:
  • Bloody_Evil
Antwortento top
#5
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
Antwortento top
#6
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.

Zitat: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
Antwortento top
#7
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
[-] Folgende(r) 2 Benutzer sagt Danke an losgehts für diesen Beitrag:
  • Kuwer, Bloody_Evil
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste