Situation:
I have an Excel workbook with many worksheets. Some cells within the workbook link to another Excel file (called MasterData) above vlookup.
Some cells inside one worksheet (let's call it Worksheet A) refer to other cells of another worksheet (call it Worksheet B). And the cells in the Worksheet Blink MasterData.
In the third sheet, Worksheet Csome cells refer directly to MasterData.
My Task is to find the dependency structure. So, for the above example, it should give:
Worksheet A -> Worksheet B -> MasterData
Worksheet C -> MasterData
And, of course, for higher levels of binding (e.g. Worksheet D→ Worksheet E→ Worksheet F→ →MasterData
What i have done so far:
I repeat all sheets, and then above the cells in the sheet. Inside the iteration, I check if the cell has a formula, and if the formula contains MasterData, I know that this table refers to MasterData.
So, I already got the first level.
Problem:
Now I have cells like: (let's say I'm in Worksheet1in a cell B2)
=Worksheet2!A1
And cell A1in Worksheet2looks like this:
='X:\[MasterData.xslm]FZE'!A8
Therefore, when I process the cell Worksheet1!B2, I would like to follow the link to Worksheet2!A1, and then see that these are links MasterData. How can i achieve this?
application
I provide the code that I have written so far. But it contains more than I explained (it is looking for a specific worksheet in MasterData).
Sub Verknuepfungen_zwischen_Sheets_und_Masterdata()
' Zeigt auf, mit welchem Sheet aus der Masterdata ein Sheet der Planung verknüpft ist
Dim referenceToMaster As String
referenceToMaster = "MASTERDATA-Sep2014.xlsm]"
' schreibe Ausgabe in Analyse-Blatt
Dim analysisSheet As Worksheet
' finde dazu ein eventuell vorhandenes Analyse-Blatt
If (SheetExists("Analyse-Blatt")) Then
Set analysisSheet = sheets("Analyse-Blatt")
Else
Set analysisSheet = sheets.Add(before:=sheets(1))
analysisSheet.Name = "Analyse-Blatt"
End If
worksheetCount = ActiveWorkbook.Worksheets.Count
currentRowIndex = 1
' Nun gehe jedes WorkSheet durch
Dim sheetsInMaster As Collection
Dim currentSheet As Worksheet
For c = 2 To worksheetCount
Set currentSheet = sheets(c)
' nur sichtbare durchschauen
If currentSheet.Visible = xlSheetVisible Then
' nur die durchschauen, welche nicht schon Analyse-Blätter sind
If (InStr(currentSheet.Name, "Formeln_") = 0) Then
Set sheetsInMaster = New Collection
Set r1 = currentSheet.Range("a1", currentSheet.Range("a1").SpecialCells(xlLastCell))
For Each cell In r1.Cells
' schaue ob die Zelle eine Formel enthält
If cell.HasFormula Then
' schaue ob Formel eine Verweis auf die Masterplanung enthält
If InStr(cell.formula, referenceToMaster) > 0 Then
' füge den Bereich in der Masterplanung den sheetsInMaster hinzu
AddMasterSheets cell.formula, sheetsInMaster
End If
End If
Next cell
' Ausgabe in Analyse-Blatt
If sheetsInMaster.Count > 0 Then
analysisSheet.Cells(currentRowIndex, 1) = currentSheet.Name
For Each sheetInMaster In sheetsInMaster
analysisSheet.Cells(currentRowIndex, 2) = sheetInMaster
currentRowIndex = currentRowIndex + 1
Next sheetInMaster
End If
End If
End If
Next c
End Sub
Sub AddMasterSheets(ByVal formula As String, sheetsInMaster As Collection)
' Fügt der Collection "sheetsInMaster" die Namen der Arbeitsblätter der Masterplanung hinzu,
' auf welche in der "formula" verwiesen wird
Dim referenceToMaster As String
referenceToMaster = "MASTERDATA-Sep2014.xlsm]"
Dim currentIndexOfReferenceToMaster As Integer
Dim currentIndexOfPrime As Integer
currentIndexOfReferenceToMaster = InStr(formula, referenceToMaster)
Do While currentIndexOfReferenceToMaster <> 0
currentIndexOfPrime = InStr(currentIndexOfReferenceToMaster, formula, "'")
currentStart = currentIndexOfReferenceToMaster + Len(referenceToMaster)
sheetInMaster = Mid(formula, currentStart, currentIndexOfPrime - currentStart)
On Error Resume Next
sheetsInMaster.Add sheetInMaster, CStr(sheetInMaster)
On Error GoTo 0
currentIndexOfReferenceToMaster = InStr(currentIndexOfPrime, formula, referenceToMaster)
Loop
End Sub
Function SheetExists(sheetName As String) As Boolean
' Gibt zurück, ob ein Arbeitsblatt mit dem Namen existiert
SheetExists = False
For Each ws In Worksheets
If sheetName = ws.Name Then
SheetExists = True
Exit Function
End If
Next ws
End Function
, "" "", "":
A1: =SVERWEIS($E4;'X:\[MASTERDATA-Sep2014.xlsm]Departments'!$G:$CF;AF$1238;FALSCH)
A2: =AF4*'X:\[MASTERDATA-Sep2014.xlsm]Stammdaten'!AG$2*('X:\[MASTERDATA-Sep2014.xlsm]Stammdaten'!AG$15+'X:\[MASTERDATA-Sep2014.xlsm]Stammdaten'!AG$19)/60+(AF11*AF4)
A3: =SVERWEIS($D4;'X:\[MASTERDATA-Sep2014.xlsm]Stammdaten'!$E$262:$CE$337;AF$1239;FALSCH)*8*AF4
A4: =SVERWEIS($E4;'X:\[MASTERDATA-Sep2014.xlsm]Machinery'!$G:$CF;AF$1238;FALSCH)
"PlanningB":
A1: =WENNFEHLER(SVERWEIS($E10;Werkebereich;BE$10000;FALSCH)*WVERWEIS($F10;'X:\[MASTERDATA-Sep2014.xlsm]FZE'!$3:$520;Montage!$D10-2;FALSCH);0)+WENNFEHLER(SVERWEIS($E10;Kitbereich;BE$10000;FALSCH)*WVERWEIS($F10;'X:\[MASTERDATA-Sep2014.xlsm]FZE'!$3:$520;Montage!$D10-2;FALSCH);0)
A2: =SVERWEIS($E4;'X:\[MASTERDATA-Sep2014.xlsm]LKZ-Part'!$G:$CF;AF$1238;FALSCH)
"Analyze-Blatt", :
|A |B
1|PlanningA |Departments
2| |Stammdaten
3| |Machinery
4|PlanningB |FZE
5| |LKZ-Part
, , PlanningA Departments MasterData. , , A1 PlanningB VLookUp Werkebereich. Werkebereich Employees MasterData. , :
|A |B |C
1|PlanningA |Departments |
2| |Stammdaten |
3| |Machinery |
4|PlanningB |Werkebreich | Employees
5| |FZE |
6| |LKZ-Part |
, , , , :
VLOOKUP VBA?