open excel file with partial name

gt213

Board Regular
Joined
Jul 5, 2016
Messages
59
I'm looking to open an excel file in a folder, where i know the folder path and the first part of the file name.

The partial file name is in cell b3 of sheet "Control" on WB1 (it is generated using a formula so isnt text).

I then what to open the file and copy all worksheets over to WB1.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
say the file is robert.xls and B3 contains rob - you need a lookup table where when rob is submitted, robert is returned, then use concatenation to build the file path....
 

gt213

Board Regular
Joined
Jul 5, 2016
Messages
59
So the partial file name in B3 contains a date that changes and the rest of the file name that i want to open includes some random numbers at the end that i dont know in advance, so cant use a look up table.

I've tried the below but get an error on the file line of code - i think due to how it is referencing the *

Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook


MyFolder = "G:\In\IEIndex\Recon\File"
MyFile = Dir(MyFolder & Worksheets("Control").Range("B3").Value & "\*.xls")


Workbooks.Open Filename:=MyFolder & "" & MyFile
 
Last edited:

gt213

Board Regular
Joined
Jul 5, 2016
Messages
59
Update:

I changed the code below slightly and it now works perfectly:

Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook


MyFolder = "G:\In\IEIndex\Recon\File"
MyFile = Dir(MyFolder & Worksheets("Control").Range("B3").Value & "*.xls")


Workbooks.Open Filename:=MyFolder & "" & MyFile
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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