Opening Excel Workbook with unknown or changing extention.

[hangten4881]

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.

 

[Andrew Poulsom]

If you know the name of the file you can use Dir with wildcards to return the name including the extension.

 

[hangten4881]

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]

Try (untested):

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]

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

 

[Andrew Poulsom]

What is assigned to FName when you get that error (hover over it with your mouse to see).

 

[hangten4881]

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

 

[Andrew Poulsom]

Can you open that workbook manually?

 

[hangten4881]

Yes. I pulled it upmanually and it worked fine.

 

[Andrew Poulsom]

If you record a macro while opening it manually what VBA code do you get?