CODEWITHSUNDEEP
excelvbavariables
AlmostThere
AlmostThere

Reputation: 647

How to Dim and Set a variable workbook name in VBA?

I have a workbook that gets created every week that has a variable name structure. The name structure is as follows : Week of Year & Invoice & date. So a sample file might be called 1_Invoice_01052018.xlsm

I have to update the report every week. I want to declare the variable workbook name as a variable in VBA. I have another workbook that contains the output of the report that is created via VBA. In this other workbook I want to be able to call the Invoice spreadsheet but since it has a variable name, I am having issues finding it. So I put together the VBA below. 

Sub Test()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = "*Invoice*" & ".xlsm"
Set ws = Sheets("Sheet1")

wb.Activate
ws.Select

End Sub

However, this results in a "Type mismatch" error.

I also tried the following:

Sub Test2()

Windows("*Invoice*" & ".xlsm").Activate

End Sub

This also resulted in an error.

Any ideas on how to set a variable workbook name as a variable in VBA? I would only have one of these workbooks open at a time, so I wouldn't run into any issues

Upvotes: 1

Views: 118496

Answers (3)

Strawberryshrub
Strawberryshrub

Reputation: 3389

You have to set the workbook correctly:

Sub Test()

  Dim wb As Workbook
  Dim ws As Worksheet
 
  Dim FilePath As String
  FilePath = "C:\" & AnotherVariable & ".xlsx"

  Set wb = Workbooks(FilePath)
  Set ws = Sheets("Sheet1")

  wb.Activate
  ws.Select

End Sub

Upvotes: 5

AlmostThere
AlmostThere

Reputation: 647

I was able to get what I need from the following link:
excel-vba-extract-text-between-2-characters

I reviewed the link above and put together the VBA below.

Sub test2()

Dim str As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String

str = Range("b1").Value
openPos = InStr(str, "[")
closePos = InStr(str, "]")
midBit = Mid(str, openPos + 1, closePos - openPos - 1)

'MsgBox (midBit)
Windows(midBit).Activate

End Sub

I ended up creating a dynamic file path in cell B1 that contained a concatenated file path string that contained look ups to pull in the Week of Year and Date based on the Current Date. Since this path is dynamic it will always point to the right path given that I open the Invoice on the correct week. I pulling the file name from the path and opening based on the file name which is dynamic.

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

To create a new workbook you'd use Workbooks.Add. To open an existing one you'd use Workbooks.Open and then reference the worksheet within that workbook.
To change the name of the file you'd save it with a new name using the SaveAs method.

Sub Test()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim FileName As String

    Set wb = Workbooks.Add 'Creates a new workbook with default name.
    'Set wb = Workbooks.Open("<path to folder>\" & FileName & ".xlsm") 'Open an existing file.

    Set ws = wb.Worksheets("Sheet1")

    wb.SaveAs "<path to folder>\" & FileName & ".xlsm" 'Save and rename here.

    With ws
        .Range("A1") = "Adding some text to this cell"
    End With

End Sub

As a further example, the code below will create two workbooks before copying the sheet from the first workbook to the end of the second workbook. 

Sub Test1()

    Dim wb As Workbook, wb1 As Workbook
    Dim ws As Worksheet

    'Create first workbook so it contains only 1 sheet (xlWBATWorksheet)
    ', reference Sheet1 and add some data to it.
    Set wb = Workbooks.Add(xlWBATWorksheet)
    Set ws = wb.Worksheets("Sheet1")
    ws.Range("A1") = "This cell populated in first workbook."

    'Create second workbook with default number of sheets
    'and copy Sheet1 from first book to the end of this one.
    Set wb1 = Workbooks.Add
    ws.Copy After:=wb1.Sheets(wb1.Sheets.Count)

End Sub

Edit again:

To figure out the workbook name based on WeekNumber_Invoice_Date you could use: 

Sub Test2()

    Dim wb As Workbook
    Dim sPath As String
    Dim dDate As Date

    dDate = Date 'Todays date
    sPath = "C:\MyFolder\"
    sPath = sPath & _
        WorksheetFunction.WeekNum(dDate, 2) & "_Invoice_" & Format(dDate, "ddmmyyyy") & ".xlsm"

    'Open if already exists.
    'Set wb = Workbooks.Open(sPath)

    'Create and SaveAs new name.
    Set wb = Workbooks.Add
    wb.SaveAs sPath

End Sub

This would give a file path of C:\MyFolder\43_Invoice_22102018.xlsm based on todays date of 22nd October '18.

Note: The WEEKNUM function considers the week containing January 1 to be the first week of the year.

Upvotes: 2

Related Questions