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.

 

[pvr928]

Hi abhay_547

My approach in this situation is to check the output of the Filepath and Filename.

Insert:

Debug.Print Workbookpath & "\" & Filenameknownpart & "******" & ".xls"

and see whether this output matches the actual Filepath & Filename (including the file extension).

I didn't encounter any errors with your code using parameters unique to me.

Cheers

pvr928

 

[abhay_547]

I have already checked it but it reads the asterisk part as asterisk only and that's reason it is unable to find the file on the declared path, it shows asterisk in debug print instead of reading the actual unique 6 digit code which is the last part of the filename.

 

[pvr928]

Ok - and whereas I thought the asterisks were pseudo code for the actual unique 6 digits - if you actually have the asterisks in the formula, Excel is doing exactly what you are telling it to do.

Consequently, you need a mechanism to substitute the actual code for the asterisks.

Something like:

Option Explicit

Sub TEST()

Dim Workbookpath As String
Dim Filenameknownpart As String
[B]Dim UniqueSixDigitCode As Integer
[/B]
Workbookpath = ThisWorkbook.Sheets("Parameters").Range("A2").Value
Filenameknownpart = ThisWorkbook.Sheets("Parameters").Range("B2").Value
[B]UniqueSixDigitCode = ThisWorkbook.Sheets("Parameters").Range("C2").Value[/B]

Debug.Print Workbookpath & "\" & Filenameknownpart [B]& UniqueSixDigitCode &[/B] ".xls"

Workbooks.Open Filename:=Workbookpath & "\" & Filenameknownpart [B]& UniqueSixDigitCode &[/B] ".xls"

End Sub

If you have more than one file and you want to open them all at once, consider creating a loop to achieve this.

Cheers

pvr928

 

[abhay_547]

You are asking me to add the Unique Six digit code in the cell C2 of Worksheet give reference to the same as a string but since there are multiple such files with different unique code (changing every day), I can't keep entering the unique code values in the column C for all those files. I want to keep it undefined, basically the macro should identify and open the file based on just the start Filenameknownpart. can you please advise how the same can be achieved.

 

[pvr928]

Hi abhay_547

I'm not asking you to do anything - I'm providing advice to you.

If you are saying you want Excel to open a file, but the filename contains a component that the code does not have and cannot otherwise identify, you are asking Excel to do something beyond its capabilities.

Cheers

pvr928

 

[abhay_547]

As far as I know if we have partname of the file which we are trying to open, then excel VBA can open the file but there is a certain way in which it can be achieved, which i am unable to find hence asking for help. Below is the thread which I have come across on this portal but again the partname of the file and path is hardcorded in the same where else I am trying to take that filepath and partname of file from the worksheet cell in my code, that's the only difference.

https://www.mrexcel.com/forum/excel-questions/541281-vba-open-workbook-part-file-name.html

 

[abhay_547]

I have cross posted it now on the VBforum. Below is the link:

http://www.vbforums.com/showthread.php?858501-Open-Files-with-just-partname&p=5258273#post5258273

 

[Fluff]

How about

Dim Workbookpath As String
Dim Filenameknownpart As String
Dim Fname As String

Workbookpath = ThisWorkbook.Sheets("Parameters").Range("A2").Value
Filenameknownpart = ThisWorkbook.Sheets("Parameters").Range("B2").Value
Fname = Dir(Workbookpath & Filenameknownpart & "*.xls")
Workbooks.Open Workbookpath & Fname

 

[abhay_547]

How about

Dim Workbookpath As String
Dim Filenameknownpart As String
Dim Fname As String

Workbookpath = ThisWorkbook.Sheets("Parameters").Range("A2").Value
Filenameknownpart = ThisWorkbook.Sheets("Parameters").Range("B2").Value
Fname = Dir(Workbookpath & Filenameknownpart & "*.xls")
Workbooks.Open Workbookpath & Fname

Great. this does work but opens only last modified file. the problem is that if i have 2 or more files with same partname and the only unique thing in the naming convention of those files is the 6 digit code which we wouldn't know since it keeps changing everyday, would it be possible to open all those (i mean 2 or more) files at one go and get the names of the same in the macro workbook worksheet in a specific range e.g. open 2 files with same name and then get the opened file names in my macro workbook worksheet, lets say sheet1 in range A1 and A2 and so on.