Beliebt

automatische aktualisierung der links von arbeitsmappen - microsoft-excel, arbeitsblattfunktion, vba, microsoft-excel-2007, vbscript

Ich bin auf der Suche nach einer M├Âglichkeit, XLSB-Dateien einzeln in einem Ordner zu ├Âffnen und ihre Links dann in XLSB umzubenennen oder zu aktualisieren.

Wenn zum Beispiel die Datei "C: FINAL ANSWERedit.xlsb" Links wie "C: FINAL ANSWERMAIN VALUES.xlsx! L30" enth├Ąlt, dann den Code

sollte in der Lage sein, diesen Link in = "C: FINAL ANSWERMAIN VALUES.xlsb! L30" umzubenennen.

Jeder Ordner enth├Ąlt viele Arbeitsmappen mit verschiedenen Arbeitsbl├Ąttern, sodass das Makro in jeder Arbeitsmappe suchen kann und jedes Blatt mit den Links .xlsx und ersetze es durch .xlsb wie oben erkl├Ąrt.

Vielen Dank

Antworten:

0 f├╝r Antwort Ôäľ 1

Kopieren Sie folgendes in ein Modul und f├╝hren Sie das ausUpdateLinks-Makro. In einem Verzeichnisdialogfeld wird das Verzeichnis ausgew├Ąhlt, in dem die Verbindungsaktualisierungen durchgef├╝hrt werden sollen. ├ťberpr├╝fen Sie Ihr Sofortfenster auf Fehler und Statistiken. Bitte postet etwas von der Ausgabe und viel Gl├╝ck zur├╝ck!

Dies ist m├Âglicherweise nicht so zeitaufwendig wie manuellwie man meinen k├Ânnte. Sie m├╝ssen nicht jede Zelle oder sogar die Liste der benannten Bereiche bearbeiten. Jede eindeutige Datei, die Teil einer oder mehrerer externer Referenzen ist, ist ein einzelner Eintrag im Links-Manager. Daten [Tab] | Abfragen & Verbindungen [Abschnitt] | Links bearbeiten [Dialog] | Quelle ├Ąndern ... Mit [Dialog] k├Ânnen Sie jede Datei ├Ąndern, auf die verwiesen wird. Selbst wenn eine Datei in mehreren Zellen und Bl├Ąttern referenziert wird und jeder Verweis auf ein anderes Blatt und einen anderen Bereich in der referenzierten Datei verweist, werden bei einer ├änderung alle Verweise auf den neuen Dateinamen oder Speicherort aktualisiert (einschlie├člich einer Dateierweiterungs├Ąnderung).

Option Explicit
Private Const OpenFiles = "xlsb|xls|xlt|xlsx|xltx|xlsm|xltm" "single ext is OK
Private Const OldExt = "xlsx"
Private Const NewExt = "xlsb"
Sub UpdateLinks()
Dim directory, excelFiles() As String
Dim wb As Workbook
Dim app As Excel.Application
Dim totalUpdates As Integer

directory = getDirectory
excelFiles = getExcelFiles(directory)
If LBound(excelFiles) = 0 Then "empty excel file list for directory
MsgBox "Directory "" & directory & "" has no files of type *." _
& Join(Split(OpenFiles, "|"), ", *.")
End "Exit Subroutine and Execution Call Stack
End If "(Else)
Debug.Print "DIRECTORY "" & directory & "" has " _
& UBound(excelFiles) & " excel file(s)."
Set app = New Excel.Application
app.DisplayAlerts = False
app.AutomationSecurity = msoAutomationSecurityForceDisable "disable macros

totalUpdates = 0
Dim file As Variant
For Each file In excelFiles
Set wb = openWorkbook(app, directory & Application.PathSeparator & file)
If Not wb Is Nothing Then
totalUpdates = totalUpdates + updateExcelLinks(wb)
wb.Close
End If
Next file
app.Quit
Debug.Print "COMPLETE: " & totalUpdates & " link(s) updated from "" _
& OldExt & "" to "" & NewExt & ""."
End Sub
Function updateExcelLinks(ByRef wb As Workbook) As Integer
updateExcelLinks = 0
Dim links As Variant
links = wb.LinkSources(xlExcelLinks) "includes externally Named Ranges
If IsEmpty(links) Then
Debug.Print "No Excel links in "" & wb.Name & ""."
Exit Function
End If "(Else) Process Links
Dim l As Variant
For Each l In links
If StrComp(OldExt, Right(l, Len(l) - InStrRev(l, "."))) = 0 Then
wb.ChangeLink l, Left(l, InStrRev(l, ".")) & NewExt
updateExcelLinks = updateExcelLinks + 1
End If
Next l "xlExcelLinks iterator
If updateExcelLinks = 0 Then
Debug.Print "No links with "" & OldExt & "" extensions in "" & wb.Name & ""."
ElseIf wb.ReadOnly Then
Debug.Print "ERROR, cannot save "" & wb.Name & "" (opened in another app). " _
& updateExcelLinks & " link extension(s) NOT updated."
updateExcelLinks = 0
wb.Saved = True "discard unsavable workbook changes
Else
wb.Save
Debug.Print "Updated " & updateExcelLinks & " excel link extension(s) in "" & wb.Name & ""."
End If
End Function
Function openWorkbook(ByRef app As Excel.Application, ByVal fileName As String) As Workbook
Err.Clear
On Error Resume Next
Set openWorkbook = app.Workbooks.Open(fileName, 0) "0 is do not update ext refs when opening
If Not openWorkbook Is Nothing And openWorkbook.FileFormat <> xlCurrentPlatformText Then
Exit Function "Return valid workbook
End If "(Else)
"Not a valid Workbook
If Err.Number <> 0 Then
Debug.Print "ERROR: Could not open excel workbook "" & fileName & "". " _
& vbCrLf & "Error #" & Err.Number & " - " & Err.Description
Err.Clear
Else
Debug.Print "ERROR: Not a valid excel workbook "" & fileName _
& "" (opened as a text file)."
End If
If Not openWorkbook Is Nothing Then
openWorkbook.Close (False) "False is don"t save
Set openWorkbook = Nothing
End If
End Function
Function getExcelFiles(ByVal directory As String) As String()
Dim f As String
Dim fnames() As String
ReDim fnames(0) "testing has shown this is neither inefficient nor slow

f = Dir(directory & Application.PathSeparator)
Do While Len(f) > 0
If InStr("|" & OpenFiles & "|", "|" & Right(f, Len(f) - InStrRev(f, ".")) & "|") Then
If LBound(fnames) = 0 Then
ReDim fnames(1 To 1)
Else
ReDim Preserve fnames(1 To UBound(fnames) + 1) "see redim fname comment above
End If
fnames(UBound(fnames)) = f
End If
f = Dir "get next iterative file from the original Dir called with argument
Loop
getExcelFiles = fnames
End Function
Function getDirectory() As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = " Link Updater - Select Director"
.ButtonName = "Select"
.InitialFileName = CurDir " OR ActiveWorkbook.Path OR Set a Const at top of file
If .Show = -1 Then
getDirectory = .SelectedItems(1)
Else
End "Exit Subroutine and Execution Call Stack
End If
End With
End Function