Open Files with partial name which is derived from a cell reference and plus undefined part

[abhay_547]

Hi,

I am trying to open a file for which I have the partial name .i.e. starting part in a excel cell defined as a string and the ending part is some unique code which keeps changing so can't be hard coded, so here is how i am trying to open the same but it's not working. can someone help

Dim Workbookpath As String
Dim Filenameknownpart As String

Workbookpath = ThisWorkbook.Sheets("Parameters").Range("A2").Value
Filenameknownpart  = ThisWorkbook.Sheets("Parameters").Range("B2").Value

Workbooks.Open Filename:= Workbookpath & "\" & Filenameknownpart & "******" & ".xls"

Note: i have used asterisk to define the unique code which is six digit as of now but may go up, that is beyond 6 digit in future so don't want to keep it limited to 6 digits, also it's unique everyday so can't be hard coded anyway.

 

[Fluff]

Try

   Dim Workbookpath As String
   Dim Filenameknownpart As String
   Dim Fname As String
   
   With ThisWorkbook.Sheets("Parameters")
      Workbookpath = .Range("A2").Value
      Filenameknownpart = .Range("B2").Value
      Fname = Dir(Workbookpath & "\" & Filenameknownpart & "*.xls*")
      Do While Len(Fname) > 0
          .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Fname
          Fname = Dir
      Loop
   End With

This will put the filename in the first blank row in col A of Parameters

 

[abhay_547]

Try

   Dim Workbookpath As String
   Dim Filenameknownpart As String
   Dim Fname As String
   
   With ThisWorkbook.Sheets("Parameters")
      Workbookpath = .Range("A2").Value
      Filenameknownpart = .Range("B2").Value
      Fname = Dir(Workbookpath & "\" & Filenameknownpart & "*.xls*")
      Do While Len(Fname) > 0
          .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Fname
          Fname = Dir
      Loop
   End With

This will put the filename in the first blank row in col A of Parameters

ok. so now i get the filename with the path in the column A so i will use the mid formula in column B and get the only filenames then define them as strings by referring to the cells in column B so that I can navigate back and forth between macro workbook and those files while copying and pasting the data from them into macro workbook. but is there a way I can directly get only filenames in the column A excluding the path so that I don't need to add the formula in Column B and get the filenames which is an additional step.

 

[Fluff]

You shouldn't get path names. When I run it i get

Excel 2013 32 bit
	A	B
2	C:\Users\Fluff\Documents\Excel files	rwc
3	RWC 2003.xls
4	RWC 2011.xls
5	RWC 2015.xls
6	RWC 2019.xls
7	RWC 2019.xlsx
8	RWC2003.xls
Parameters

 

[abhay_547]

got it. not sure how it was giving the entire path earlier. I retried and its working fine. all sorted. Thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback