vba help - Unable to open closed workbook with wildcard

[Mallesh23]

Hi Team,

Unable to open a closed workbook with wildCard. whats wrong here. plz help.


Method 1, Which is working Code, It opens the workbook.

Dim wbk_current As Workbook
path = wsFolderPath.Range("b2").value & "\" & str_Debtor & Format(dtReportDate, "DD.MM.YYYY") & ".xlsx"    
Set wbk_current = Workbooks.Open(path, False, True)

Method 2, Unable to open a workbook, I am using wildcard for extensions.

path = wsFolderPath.Range("b2").value & "\" & str_Debtor & Format(dtReportDate, "DD.MM.YYYY") & [B]".xls*"[/B]
or 
path = wsFolderPath.Range("b2").value & "\" & str_Debtor & Format(dtReportDate, "DD.MM.YYYY") & [B]".xls"&"*"[/B]
Set wbk_current = Workbooks.Open(path, False, True)

Thanks
mg

 

[Joe4]

Try using the method shown here: open file with wildcard or partial file name

Note, you should also NOT used reserved words like "path" as a variable name. It could cause unexpected results and errors.
You should avoid using names of existing functions or properties as the name of variables, procedures, or functions.

I often preface my variables with "my" in front of them to make sure I do not accidentally use a reserved word.
So, you could do something like "myPath".

 

[Mallesh23]

Hi Joe4,

Thanks for it, I have corrected string variable from path = sPath
but still facing a problem.

I want wild card in extension .xlsx , to cover all extn .xls or xlsb or xlsx.

Dim wbk_current As Workbook
Dim spath as string.

1) Below Code not working :-
spath = wsFolderPath.Range("b2").value & "\" & str_Debtor & Format(dtReportDate, "DD.MM.YYYY") & ".xls"&"*"
Set wbk_current = Workbooks.Open(path, False, True)


2) Below code is working.
spath = wsFolderPath.Range("b2").value & "\" & str_Debtor & Format(dtReportDate, "DD.MM.YYYY") & ".xlsx"
Set wbk_current = Workbooks.Open(path, False, True)


Thanks
mg

 

[Joe4]

Thanks for it, I have corrected string variable from path = sPath
but still facing a problem.

I want wild card in extension .xlsx , to cover all extn .xls or xlsb or xlsx.

You CANNOT open the file like that.
Did you look at the link I provided? It shows you how to open a file using a wildcard in the name (it doesn't matter if the wildcard is in the file name or the extension.
HINT: You have to make use of Dir.

Try setting it up the same way as shown there.
If you cannot get it to work, post the code you can up with, using the Dir function, and we can help you clean it up.

 

[Mallesh23]

Hi Joe4,

Perfect! it worked, thanks you so much for very good information!

used below code and worked. Thanks
sFile = Dir(spath & ".xls*")
Set wbk_current = Workbooks.Open(sFile)

Thanks
mg

 

[Joe4]

You are welcome.