Office: (Office 2016) VBA Speichern: Warum funktioniert mein Makro nicht richtig

Hallo an alle,

ich habe bisher häufiger sehr gute Tipps im Forum gefunden. Leider habe ich aber ein Problem an dem ich z.Z. scheitere.
Seit längeren versuche ich immer wieder den Fehler in dem unten aufgeführten Makro zu finden.
Da meine VBA Kenntnisse ehr als sehr Mangelhaft zu beschreiben sind, finde ich den Fehler nicht. Daher wäre ich über einen Tipp sehr dankbar.

Ich möchte mit einem Button die Excel-Datei speichern mit "Speichern unter". Dabei soll sowohl der Pfad als auch der Dateiname aus verschiedenen Zellen zusammengefügt werden.
Bei dem Dateinamen funktioniert das super, aber leider nicht beim Pfad. Dort wird immer nur der Aktuelle Pfad vorgeschlagen...

Code:

Sub SpeichernUnter_Click()

    Dim strDateiname As String
    Dim strVerzeichnis As String

    strVerzeichnis = ThisWorkbook.Sheets("DB").Range("D57")
    strDateiname = ("Schranksystem_" & ThisWorkbook.Sheets("DB").Range("F57") & ".xlsm")
    Application.Dialogs(xlDialogSaveAs).Show (strVerzeichnis & strDateiname)

End Sub 

Denn wunsch den Pfad als als UNC-Pfad auszuführen habe ich schon aufgegeben. Alle Lösungsansetze die ich gefunden habe, führten zu noch mehr Chaos...

Viele Grüße

Holzwurm

 

Hallo,

den VBA-Code müsstest du etwas modifizieren/ergänzen (rot) zu:

Code:

Sub SpeichernUnter_Click()

    Dim strDateiname As String
    Dim strVerzeichnis As String

    With ThisWorkbook
       With .Sheets("DB")
          strVerzeichnis = .Range("D57")
          strDateiname = "Schranksystem_" & .Range("F57") & ".xlsm"
       End With
       [COLOR="#0000FF"]If Right(strVerzeichnis, 1) <> "\" Then strVerzeichnis = strVerzeichnis & "\"[/COLOR]
       [COLOR="#FF0000"].SaveAs Filename:=strVerzeichnis & strDateiname, FileFormat:=xlOpenXMLWorkbookMacroEnabled[/COLOR]
    End With
End Sub

Und der Vorsicht halber solltest du die blaue Zeile hinzufügen.

Bezüglich UNC-Pfad muss man eventuell eine Excel-Tücke beachten: So ein UNC-Pfad beginnt ja bekanntlich mit 2 Backslashes, zB. "\\Pfad\Datei.xlsm".
Du solltest überprüfen, ob eh noch beide \\ vorhanden sind oder ob dir Excel einen \ gestohlen hat, insbesondere dann, wenn du "\\Pfad" aus einer Excel-Zelle entnimmst. Du solltest dann eine weitere Zeile vor der blauen Zeile einfügen:

Code:

If Left(strVerzeichnis,2)<>"\\" then strVerzeichnis="\" & strVerzeichnis

 

Hallo Exl121150,

vielen Danke für die Modifizierung des VBA – Code.:D
Jetzt funktioniert es so wie ich mir das vorgestellt habe.

Den Befehl With kannte ich nicht. Nach etwas lesen, ist mir die Bedeutung jetzt verständlich. Ob ich beim nächsten Mal von selbst darauf komme ist aber nicht ganz sicher…

Die Blaue Zeile brauche ich eigentlich nicht, da dieses in der Zelle schon kontrolliert wurde. Aber ein Pfad ohne Backslash vor dem Dateinamen macht ein grosses Problem… Als zum Gürtel die Hosenträger.

Wenn ich die rote Zeile richtig verstehe, könnte ich statt =xlOpenXMLWorkbookMacroEnabled
auch
.Value = 52
Verwenden. Also die Kontrolle das die Datei als xlsm gespeichert wird. Oder habe ich da ein Verständnis Problem?

Die Problematik mit dem Backslash Hinsichtlich Excel und UNC-Pfad war mir so nicht bewusst. Aber das erklärt einiges an den vielen "? " die sich mir gestellt haben.
Ich werde mich mit dem UNC-Pfad morgen beschäftigen

Holzwurm

 

Hallo,

du hast richtig verstanden, dass der Parameterwert "xlOpenXMLWorkbookMacroEnabled" nötig ist, um die Speicherung als xlsm-Datei zu steuern, wobei diese Parameterkonstante den Wert 52 hat. Man kann also beides verwenden, solange die Excel-Objektklasse im Projekt eingebunden ist - und das ist sie beim Einsatz innerhalb einer Excel-Anwendung automatisch.
Allerdings bin ich mit der Formulierung ".Value = 52" bzw. ".Value = xlOpenXMLWorkbookMacroEnabled" nicht einverstanden:

1. Der Punkt vor "Value" deutet darauf hin, dass "Value" eine Eigenschaft (property) einer Objektinstanz ist, die innerhalb eines "With Objektinstanz ... End With"-Blockes eingesetzt wird. Das ist hier aber nicht der Fall.
2. Ich bin auch mit "Value" nicht zufrieden, denn der benannte Parameter der Objektmethode "ThisWorkbook.SaveAs" heißt "FileFormat:=...", also in diesem Fall "FileFormat:=52" bzw. "FileFormat:=xlOpenXMLWorkbookMacroEnabled".
3. Die Objektinstanz "ThisWorkbook" hat eine Methode "SaveAs", welche mit "ThisWorkbook.SaveAs [Parameterliste]" aufgerufen wird. Innerhalb eines "With ThisWorkbook: ... End With"-Blockes kann sie dann auch nur als ".SaveAs [Parameterliste]" geschrieben werden, denn der führende Punkt vor "SaveAs" verweist dann auf den umgebenden With-Block (wo ja auf die Objektinstanz "ThisWorkbook" verwiesen wird).
   Die "SaveAs"-Methode besitzt eine Parameterliste, mit der der Methode weitere Werte (sogenannte aktuelle Parameter) übergeben werden (können), um sie präziser steuern zu können. Das sind in diesem Fall lauter optionale Parameter, dh. diese können auch (alle) weggelassen werden - nur muss man dann wissen, was die Methode bei fehlenden Parametern tut. Jeder Parameter besitzt einen Namen, der vor dem ":=" steht. Dieser kann auch weggelassen werden zusammen mit dem ":=".
   Also sind folgende Methodenaufrufe legitim:
   ThisWorkbook.SaveAs Filename:="Pfad+Dateiname", FileFormat:=52
   ThisWorkbook.SaveAs "Pfad+Dateiname", 52
   ThisWorkbook.SaveAs "Pfad+Dateiname", xlOpenXMLWorkbookMacroEnabled
   
   Die vollständige Beschreibung der .SaveAs-Methode und ihrer (optionalen) Parameter findest du bei Microsoft im Link:
   https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

Eine solche Pfadangabe lautet ja im Prinzip:
\\UNC-Pfad\DateiPfad\Dateiname.Dateierweiterung
Man kann natürlich diese Bestandteile in einem Excel-Arbeitsblatt in Zellen speichern und sie dann per VBA zusammensetzen. Der doppelte Backslash am Beginn des UNC-Pfades muss natürlich schlussendlich bei Ausführung der .SaveAs-Methode mit Parameter FileName vorhanden sein.
Die Excel-Tücke besteht darin, dass der erste \ in einer Zelle verschluckt werden kann, weil er dann zur Zellformatierung verwendet wird.
Das Makro muss daher überprüfen, ob der UNC-Pfad eh mit diesen beiden \\ beginnt, ansonsten er entsprechend ergänzt werden muss vor dem Einsatz in der .SaveAs-Methode der ThisWorkbook-Objektinstanz.

 

Hallo Exl121150,

vielen Dank für die sehr Ausführlichen Erklärungen..
Jetzt "lichtet" sich bei der Thematik der Nebel ein wenig aber es ergeben viele neue Fragen. Aber das ist ja immer so, wenn man sich in neue Themengebiete vortastet.

Holzwurm