Introduction Path from Excel File Name from Excel Create an Index Sheet for an Excel Workbook (VBA) DEC2BIN() (32 Bit) References

Introduction

This page provides information related to Microsoft Excel

Path from Excel

=MID(CELL("filename",A1),1,FIND("[",CELL("filename",A1))-1)

File Name from Excel

=MID(CELL("filename" ,A1),FIND("[" ,CELL("filename" ,A1))+1,FIND("]" ,CELL("filename" ,A1))-FIND("[" ,CELL("filename",A1))-1)

Create an Index Sheet for an Excel Workbook (VBA)

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
   

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet

End Sub

DEC2BIN() (32 Bit)

The standard Excel DEC2BIN() only functions up to 10bits.

The below allows up to 6, 8 bit bytes to be displayed.

=DEC2BIN(MOD(QUOTIENT(A1,256^3),256),8)&"."&DEC2BIN(MOD(QUOTIENT(A1,256^2),256),8)&"."&DEC2BIN(MOD(QUOTIENT(A1,256^1),256),8)&"."&DEC2BIN(MOD(QUOTIENT(A1,256^0),256),8)

References

http://www.dbforums.com/microsoft-excel/1647278-function-get-current-path-directory.html

http://www.ozgrid.com/VBA/sheet-index.htm

Views: