Office: (Office 2010) VBA makro Umwandlung von XLS zu XLSX

Nach dem Update aus Office 2010 von Office 2003 wünscht ein Kunde die gesamte Konvertierung seiner Excel-Dateien aus dem Format Excel 2003 (XLS) in das neue 2010 Format (XLSX). Dies lässt sich ihm leider noch nicht ausreden.

Bei den Überlegungen entstanden bereits wichtige Punkte auf die geachtet werden müssen:

- Hyperlinks von einer Datei in eine andere dürfen nicht zerstört werden, auch auf XLSX anpassen
- Verknüpfungen in Zellen anderer Dateien muss gewährleistet bleiben
- Passwortgeschützte Dateien müssen auch behandelt oder zumindest vermieden und vermerkt werden

Ich habe bereits ein Makro programmiert das diese Schritte durchführt nur machen mir passwortgeschützte Dateien arghe Kopfzerbrechen da das Makro spätestens nach der zweiten Passwortabfrage abstürzt.
Da das Makro sehr Umfangreich ist und hier kaum zur Lösung beitragen wird kurze Info:

Ich benutze bereits On Error Goto Anweisungen und übergebe bereits ein Standardpasswort (irgendeins damit die meldung automatisch ausgefüllt wird)
Ich möchte nur das die Dateien ignoriert werden und das Makro weiterläuft.

:)

 

Hallo depetsys,

die aktivierte Fehlerbehandlung mit On-Error-Anweisung hast Du qualifiziert beendet? Oder lässt Du das Makro mit einem unbehandelten Fehler munter weiterrennen? Dann ist es klar, dass beim zweiten Mal das Makro aussteigt.

Wer hier Hilfe erwartet, darf ruhig auch etwas von seinem Wissen preisgeben (das tun wir ja schließlich auch). Der Code könnte für einige hier schon interessant sein. Also: Geben und Nehmen?

Grüße
EarlFred

 

Soeben hat sich eine Lösung auf dieses Problem gefunden:

Das öffnen der Datei muss in eine Sub, danke trotzdem für die Aufmerksamkeit *wink.gif*

Sub Test()
Const Path = "Z:\"
Dim FName As String
Dim WB As Workbook
FName = Dir(Path & "*.xls")
Do
Set WB = OpenWB(Path & FName)
If Not WB Is Nothing Then
WB.Close
End If
FName = Dir
Loop Until FName = ""
End Sub

Function OpenWB(FName As String) As Workbook
On Error Resume Next
Set OpenWB = Workbooks.Open(FName, False, False, Password:="")
End Function

 

Hallo zusammen,

das Makro macht wiedermal Probleme, nachdem das Projekt lange brach lag hat ein kürzlicher Testlauf folgendes Problem aufgebracht:

Es gibt in vielen Dateien Formeln die auf andere Dateien verlinken.
Diese Links sollen, da sie bisher auf "xls" Zeigen nun auf "xlsm" umgebogen werden.

Vorweg: dieser Lösung funktioniert bestens, solange manuell die Meldungen "weggedrückt werden" um welche Meldungen es sich handelt folgt nun.

Existiert die Datei nicht (da zum Beispiel der Konverter noch nicht bei der verlinkten Datei angelangt ist), wird dank des "DisplayAlerts = False" die Meldung ignoriert und es geht weiter.

Existiert die Datei aber das Worksheet existiert nichtmehr bekomme ich ein Fenster "Blatt wählen":




Wird dieses mit Abbrechen "bestätigt" läuft das Makro weiter, bei über 6000 Dateien eine zumutung *Smilie

Diese Meldung "poppt" genau in dem moment auf, wenn die Suchen & Ersetzen Funktion das ".rxls" ersetzt.

Wenn jemand Tipps hat bitte her damit!

Folgend ein erweiterter Codeauszug:
Code:

 

Hallo,

ich würd stattdessen die LinkSources mit ChangeLink aktualisieren. Neben Formeln können sich Verlinkungen ja z. B. auch in Namen verbergen. Der Beispielcode in der OH sollte genug Anhaltspunkte geben.

Dann öffne doch erstmal alle Dateien und prüfe die Verlinkungen, bevor Du durch die Neuverlinkung und Konvertierung vollständiges Chaos anrichtest...

Mit DisplayAlerts = False jede Rückmeldung totzuschlagen ist halt nicht immer klug. Das gilt es, mit Bedacht einzusetzen und nicht als "Generalklausel".

Grüße
EarlFred

 

Hey Danke für den schnellen Rat!

LinkSources und ChangeLink war mir gänzlich unbekannt, ein schneller Test ergab allerdings, dass ich anhand dieser Funktionen sehr viel effektiver arbeiten und protokollieren kann. Danke!

Ich verstehe das DisplayAlerts = False keine Generallösung ist. In diesem Fall halte ich es jedoch für sinnvoll, da alle verlinkten Dateien in einem Verzeichnis (und Unterverzeichnis) liegen und dieses Makro durch alle Dateien wandert.
Ergo, am Ende sind alle Dateien .xlsm Dateien.

Das Problem mit der Verlinkung besteht leider eher darin, dass die explizit verlinkten "Blätter" gar nicht (bzw. nicht mehr) vorhanden sind (schon vor der Umwandlung). Ich möchte diese Meldung nicht mit DisplayAlerts=False ignorieren, eher in einem Vorlauf prüfen ob die Verlinkung in "fehlende" Worksheets besteht oder nicht und dies protokollieren, damit der Kunde diese Fehler bereinigen kann.

Leider funktioniert selbst DisplayAlerts=False nicht um diese Warnung auszumerzen.
Code:

Allen erstmal ein schönes Wochenende!!
>dpetsys

 

Hallo dpetsys,

ich meine das auch anders: Prüfe zuerst die Mappen auf ungültige Verlinkungen. Beziehen sie diese auf nichtmehr vorhandene Blätter, müssen die Tabellen eh angepasst werden oder sind nutzlos und können in den Müll.

Wenn das bereinigt ist, konvertiere.

Grüße
EarlFred

 

Hallo Earl,

zurück aus dem Wochenende und aus dem Krank sein.....

Leider steht dies nicht zur Option *frown.gif*, liefert mir LinkSources bzw. eine andere Funktion den, die Möglichkeit dies zu prüfen? (Auch auf gültige Arbeitsblätter?)

Meinem Kunden ist bewusst, dass er fehlerhafte Verknüpfungen hat, diese sind ihm jedoch "teils" egal, bzw. werden diese verknüpfungen einfach nicht mehr verwendet. Ob dies in Absprache repariert werden kann wenn ich eine ausreichende Dokumentation vorliege sei erstmal dahin gestellt.


Mit freundlichem Gruße
dpetsys!

 

Hallo Liebes Forum,

die Umwandlung ist durchgeführt, der Kunde ist "glücklich" *biggrin.gif*
Also ich ziehe daraus folgendes Fazit:

Dank LinkSources, LinkState, ChangeLink , sehr vielen On Error Anweisungen, viel Dokumentation (dateien öffnen / schliessen / speichern / verlinkungen etc) und viel Geduld hat die Umwandlung hervorragend funktioniert.

Mein vorgehen in Einzelschritten:

• Lese alle Dateien inkl. Unterverzeichnisse aus / Hierzu wurde eine Rekursive Funktion erstellt in der mittels "Dir" die Ordner ausgelesen werden
• Laufe durch alle Dateien in der Dateiliste und erkenne den aktuellen Datentyp anhand der Dateiendung, prüfe ob bereits eine XLSM-Datei besteht und handle dementsprechend.
• Ist alles OK dann wird die Datei geöffnet und mittels Hyperlinks und LinkSources ermittelt ob und wieviele Links in andere Dateien in den Sheets vorliegen.
• Schlussendlich wird die Datei geöffnet und als XLSM Datei wieder abgespeichert.
• Nur wenn während des gesamten Prozesses keine Fehler auftauchen wird die Datei ".xls" gelöscht!
• Es wird alles dokumentiert, wann wo welche Datei geöffnet wurde, ob Fehler dabei auftauchten usw.
• Nun lesen wir den Ordner erneut aus und gehen parallel durch die Dokumentation um zu prüfen ob die Datei schon Probleme gemacht UND ob es überhaupt Links gibt die ersetzt werden müssen.
• Sollte dies der Fall sein, öffne die Datei erneut und ersetze mittels ChangeLink und Hyperlinks.Address die Verlinkungen.
• Jeder Linkpfad wird vor der Umwandlung geprüft und dokumentiert.
• Die Linkendungen werden auf XMLS geändert, außerdem musste der korrekte Pfad angepasst werden. (H:/Umwandlung/Excel -> H:/Daten/Excel/), damit diese nicht fälschlicherweise auf das Umwandlungsverzeichnis zeigen.
• Dies hat den Vorteil das ein Großteil der xlsm Dateien im Ursprungsverzeichnis natürlich nicht existieren und somit auch mein Problem der Fehlermeldung bei der Nutzung von ChangeLink auf Arbeitsblätter die nicht mehr existieren lösen. Es war keine manuelle Eingabe bei der Änderung der Links mehr nötig!
• Auch hier wurde extra darauf geachtet so viel wie möglich zu dokumentieren, gab es Fehler, konnte etwas nicht ersetzt werden etc.

Zum Schluss bekam der Kunde eine Einführung was sich geändert hat (alle Links müssen beim öffnen min. 1 mal manuell aktualisiert werden). Dies ist etwas Mühselig wenn viele Dateien verstrickt verknüpft wurden aber machbar.

Die Dokumentation der Fehlerhaften Dateien und Verlinkungen bekam der Kunde ausgehändigt um seinen Datenbestand zu säubern.


Vielen Dank an EarlFred für die Hilfe! Und ich hoffe das ich anderen mit dem selben Problem eine kleine Hilfe sein konnte!

Mit freundlichem Gruß ins Wochenende
Dpetsys!