CODEWITHSUNDEEP
excelvba
brendan wong
brendan wong

Reputation: 31

Can You Reference/Set Variable To a Workbook Without the Extension?

I've tried to find the answer but didn't seem like there was one online.

Example of code:

dim wb_name as string
wb_name = range("A1").value & ".xlsx"
set wb = workbooks(wb_name)

Upvotes: 3

Views: 132

Answers (3)

0m3r
0m3r

Reputation: 12499

You can use select case to determine the Excel version and file extension/format

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
        Select Case wb.FileFormat
        Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
        Case 52:
            If .HasVBProject Then
                FileExtStr = ".xlsm": FileFormatNum = 52
            Else
                FileExtStr = ".xlsx": FileFormatNum = 51
            End If
        Case 56: FileExtStr = ".xls": FileFormatNum = 56
        Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
        End Select
    End If

Here is 2 Examples

Use VBA SaveAs in Excel 2007-2016 - Ron de Bruin Excel Automation

https://stackoverflow.com/a/30393989/4539709

Upvotes: 0

wallyeye
wallyeye

Reputation: 116

You could loop through the open workbooks:

Public sub GetWB(byval wb_name as string) as excel.workbook
Dim wbkCurr as excel.workbook 

Set getwb=nothing 
For each wbkCurr in application.workbooks
    If lower(left(wbkCurr.name, instr(wbkCurr.name,”.”))) = lcase(wb_name) then
        Set getwb = wbkCurr
    Endif
Next wbkCurr 

End function

Call it with set wb =getwb(wb_name)

Just check that wb isn’t still nothing before you proceed

Upvotes: 1

urdearboy
urdearboy

Reputation: 14590

You can take a wild card approach. 

Dim wb_name as String
wb_name = Range("A1").Value & ".****"
Set wb = Workbooks(wb_name)

Upvotes: 1

Related Questions