Returning Workbooks Names

[egibberate]

Hi,
I have a folder called 'VBA_Samples'

How do I write the code that will return the names of the Workbooks contained in the folder to Sheet2 of 'Referencing' Workbook

Thank you for your time and expertise.

 

[Norie]

Quick and dirty.

Sub test()
    x = Dir("C:\VBA_Samples\*.xls")
    
    While x <> ""
        I = I + 1
        Range("A" & I) = x
        x = Dir()
    Wend
    
End Sub

 

[egibberate]

Thanks Norie
Is there a way I can automate the first line.
If the folder was copied to another computer it would be helpful if the code changed it to suit.
Thanks again for your help

 

[egibberate]

Hi,
I've got this far and I'm stumped (newbie I'm afaid)
So how do I get the list of files in to appear in Sheet2 of 'Referencing.xls'

Function fnstrwkbName() As String
'GET THIS FIRST OPENED WORKBOOK NAME
fnstrwkbName = Workbooks(1).Name
                                                                 
End Function
Function fnstrwkbPath() As String
'GET THIS FIRST OPENED WORKBOOK PATH
fnstrwkbPath = Workbooks(fnstrwkbName).Path
                                                                 
End Function
Function fnstrwkbFullName() As String
'GET THIS FIRST OPENED WORKBOOK FULL NAME
fnstrwkbFullName = Workbooks(fnstrwkbPath).FullName
                                                                 
End Function
Private Sub Worksheet_Activate()
x = Dir("fnstrwkbPath*.xls")

    While x <> ""
        I = I + 1
        .Range("A" & I) = x
        x = Dir()
     
    Wend
End Sub

 

[Norie]

Like this perhaps?

x=Dir(ThisWorkbook.Path &"\*.xls)

And this.

Workbooks("Referencing.xls").Sheets("Sheet2").Range("A" & I) =x

 

[egibberate]

Sorry Norie,

x=Dir(ThisWorkbook.Path &"\*.xls)

syntax error?
Sorry, it's horrible being thick

 

[Norie]

Well that's just a typo.

x=Dir(ThisWorkbook.Path & "\*.xls")

I deliberately put in there to see if you would spot it, honest.

 

[egibberate]

Norie, you are my hero.
I'm gonna toast your good health with a few beers.
Thank you very much for your help.
I'm very much obliged