need filename to be from list, GetDataClosedBook VBA

[jneuberg]

i am super new to VBA so be gentle...
I copied this from youtube and it works great if the filename is fixed. The path will always be the same, just the file name would be one of the following (which i have the user select from a dropdown... (or can be another solution if easier)

File names:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]IGN[/TD]
[/TR]
[TR]
[TD]auto[/TD]
[/TR]
[TR]
[TD]bank[/TD]
[/TR]
[TR]
[TD]capgd[/TD]
[/TR]
[TR]
[TD]comsvc[/TD]
[/TR]
[TR]
[TD]consdur[/TD]
[/TR]
[TR]
[TD]conssvc[/TD]
[/TR]
[TR]
[TD]divfin[/TD]
[/TR]
[TR]
[TD]energy[/TD]
[/TR]
[TR]
[TD]foodstp[/TD]
[/TR]
[TR]
[TD]foodbev[/TD]
[/TR]
[TR]
[TD]hlthcr[/TD]
[/TR]
[TR]
[TD]hhpp[/TD]
[/TR]
[TR]
[TD]insur[/TD]
[/TR]
[TR]
[TD]mats[/TD]
[/TR]
[TR]
[TD]media[/TD]
[/TR]
[TR]
[TD]pharma[/TD]
[/TR]
[TR]
[TD]RE[/TD]
[/TR]
[TR]
[TD]retail[/TD]
[/TR]
[TR]
[TD]semis[/TD]
[/TR]
[TR]
[TD]software[/TD]
[/TR]
[TR]
[TD]tech[/TD]
[/TR]
[TR]
[TD]tele[/TD]
[/TR]
[TR]
[TD]transp[/TD]
[/TR]
[TR]
[TD]utils[/TD]
[/TR]
</tbody>[/TABLE]



code:

Sub GetDataClosedBook()


Dim src As Workbook
'location of the file and data to copy


Set src = Workbooks.Open("S:\Attribution\REPORTING DB\industrygroupbreakdown\autos.xls", True, True)
'bring to this workbook


ThisWorkbook.Activate


Worksheets("data").Range("A1:Q50000").Formula = src.Worksheets("autos").Range("A1:Q50000").Formula


End Sub

 

[nemmi69]

Try

[COLOR=#333333]Dim src As Workbook
[/COLOR]Dim FName as string
[COLOR=#333333]'location of the file and data to copy[/COLOR]
[COLOR=#333333]
'populate filename from somewhere such as cell "A1"
FName = [/COLOR]Worksheets("Sheet1").Range("A1").value[COLOR=#333333]

Set src = Workbooks.Open("S:\Attribution\REPORTING DB\industrygroupbreakdown\" & FName, True, True)[/COLOR]
[COLOR=#333333]'bring to this workbook[/COLOR]