Opening Excel Workbook with unknown or changing extention.

hangten4881

New Member
Joined
Feb 20, 2009
Messages
8
I was wondering if it is possible to open a workbook from a macro (VB) with an unknown extention (.xls, xlsx, xlsm, etc). Could anyone help me out. I am using Microsoft Excel 2010. Thank you.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you know the name of the file you can use Dir with wildcards to return the name including the extension.
 

hangten4881

New Member
Joined
Feb 20, 2009
Messages
8
I tried something like this but I got a RunTime Error 1004 Method "open" failed. Am I missing something? Sorry I am a bit of a noob.

Workbooks.Open Filename:=Dir("C:\Users\VBACOBRAIOM\Desktop\CLCW_SUMMARY_EXPORT" & ".****"
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try (untested):

Code:
Dim FName as String
FName = Dir("C:\Users\VBACOBRAIOM\Desktop\CLCW_SUMMARY_EXPORT.xl*")
If FName <> "" Then
    Workbooks.Open "C:\Users\VBACOBRAIOM\Desktop\" & FName
End If
 

hangten4881

New Member
Joined
Feb 20, 2009
Messages
8

ADVERTISEMENT

Tried the above code. I get the same error. Method 'open' of Object 'workbooks' failed.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What is assigned to FName when you get that error (hover over it with your mouse to see).
 

hangten4881

New Member
Joined
Feb 20, 2009
Messages
8

ADVERTISEMENT

"CLCW_SUMMARY_EXPORT.XLS" It's in quotes. Does that have anything to do with the error?
 

Forum statistics

Threads
1,136,435
Messages
5,675,848
Members
419,586
Latest member
RoteichA

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