Returning Workbooks Names

egibberate

Board Regular
Joined
Jul 8, 2008
Messages
112
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Quick and dirty.
Code:
Sub test()
    x = Dir("C:\VBA_Samples\*.xls")
    
    While x <> ""
        I = I + 1
        Range("A" & I) = x
        x = Dir()
    Wend
    
End Sub
 

egibberate

Board Regular
Joined
Jul 8, 2008
Messages
112
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

Board Regular
Joined
Jul 8, 2008
Messages
112
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'

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Like this perhaps?
Code:
x=Dir(ThisWorkbook.Path &"\*.xls)
And this.
Code:
Workbooks("Referencing.xls").Sheets("Sheet2").Range("A" & I) =x
 

egibberate

Board Regular
Joined
Jul 8, 2008
Messages
112
Sorry Norie,


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

syntax error?
Sorry, it's horrible being thick :eek:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Well that's just a typo.:)
Code:
x=Dir(ThisWorkbook.Path & "\*.xls")
I deliberately put in there to see if you would spot it, honest.;)
 

egibberate

Board Regular
Joined
Jul 8, 2008
Messages
112
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 :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top