Posts by ipi99

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    Hello Everyone



    Good day,



    I'm using below VBA code for last 1 years and today I discovered the small error in VBA code .



    When I write minus -10,000 it shows on "Only Hundred Ten Thousand"

    When I write positive 10,000 it shows on "Only Ten Thousand" works fine


    Can someone please correct my VBA code. You great help will be highly appreciated

    Below example:

    (10,000.99) Only Hundred Ten Thousand & 99/100 Riyals

    10,000.99 Only Ten Thousand & 99/100 Riyals

    (10,000,000.99) Only Hundred Ten Million & 99/100 Riyals

    10,000,000.99 Only Ten Million & 99/100 Riyals

    (10,000,000,000.99) Only Hundred Ten Billion & 99/100 Riyals

    10,000,000,000.99 Only Ten Billion & 99/100 Riyals

    (10,000,000,000,000.90) Only Hundred Ten Trillion & 90/100 Riyals

    10,000,000,000,000.90 Only Ten Trillion & 90/100 Riyals

    Formula =spellbilling

    MY VBA CODE:

    Mr. john, send me this code I hope this will work


    Option Explicit


    Public Sub Main_List_Folders_LB()


    Dim startFolderPath As String

    Dim startCell As Range, r As Long


    startFolderPath = "C:\Users\D\Desktop\TEST\CR"


    With ActiveSheet

    .Cells.Clear

    Set startCell = .Range("A1")

    End With


    r = List_Folders(startFolderPath, 1, startCell)

    Debug.Print startCell.Offset(r).Address


    End Sub



    Private Function List_Folders(folderPath As String, level As Long, destCell As Range) As Long


    Static FSO As Object

    Dim thisFolder As Object, subfolder As Object, jobSubfolder As Object

    Dim n As Long, c As Long


    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")


    Set thisFolder = FSO.GetFolder(folderPath)


    'Add hyperlink for this folder


    Debug.Print thisFolder.Path, destCell.Address

    destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=IIf(Left(thisFolder.Name, 1) = "@", "'", "") & thisFolder.Name


    'List subfolders in this folder


    n = 0

    For Each subfolder In thisFolder.SubFolders

    If level <= 2 Then

    n = n + 1 + List_Folders(subfolder.Path, level + 1, destCell.Offset(n + 1, 1))

    Else

    'Add hyperlinks for job folder and its subfolders

    n = n + 1

    c = 1

    destCell.Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, c), Address:=subfolder.Path, TextToDisplay:=IIf(Left(subfolder.Name, 1) = "@", "'", "") & subfolder.Name

    For Each jobSubfolder In subfolder.SubFolders

    c = c + 1

    destCell.Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, c), Address:=jobSubfolder.Path, TextToDisplay:=IIf(Left(jobSubfolder.Name, 1) = "@", "'", "") & jobSubfolder.Name

    Next

    End If

    Next


    List_Folders = n


    End Function

    Hello Everyone,


    I’m trying to sort out below VBA code that is working in VERTICAL Sorting Order of SUB-FOLDERS

    Please help me to work this VBA code to work in HORIZONTAL Sorting Order of SUB-FOLDERS. I attached two photos please someone help me I really appreciate your great efforts and time.


    Thank You J


    Vertical sorting


    Image



    Horizontal sorting


    Image



    VBA code


    Public Sub Hierarchical_Folders_and_Files_Listing2()



    Dim startFolderPath As String

    Dim startCell As Range

    Dim n As Long


    startFolderPath = "Z:\Central Region (Riyadh)"


    With Sheets("Sheet1")

    .Cells.Clear

    .Activate

    Set startCell = .Range("A1")

    End With


    n = List_Folders_and_Files2(startFolderPath, startCell)



    End Sub



    Private Function List_Folders_and_Files2(folderPath As String, destCell As Range) As Long



    Static FSO As Object

    Dim thisFolder As Object, subfolder As Object

    Dim fileItem As Object

    Dim n As Long


    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")


    Set thisFolder = FSO.GetFolder(folderPath)


    'Add hyperlink for this folder


    destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=thisFolder.Name



    'List subfolders in this folder


    n = 0

    For Each subfolder In thisFolder.Subfolders

    n = n + 1 + List_Folders_and_Files2(subfolder.Path, destCell.Offset(n + 1, 1))

    Next



    List_Folders_and_Files2 = n



    End Function