vba help - Unable to open closed workbook with wildcard

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
844
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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.
VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
844
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
844
Office Version
  1. 2010
Platform
  1. Windows
Hi Joe4,

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

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

Thanks
mg
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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