Excel VBA Selecting Original Workbook

Question

I am working on a Excel VBA script that requires me to copy and paste different details from the original excel workbook into 5 new excel workbooks in an orderly manner. Also, I am adding the Macros into a Add-in so that it can be added into another computer. My only problem is that I am unable to reference the original workbook because:

1) The original workbook's file name will always be changing, therefore I am unable to pull out the workbook based on its name/assign the workbook name to a variable.

2) Excel VBA have ActiveWorkbook/ThisWorkbook property but it does not ActiveWorkbook Property will always be changing, depending on the current workbook that is active. That being said, ActiveWorkbook will not work for me as the creation of new workbooks will overwrite the original workbook. ThisWorkbook property does not work for me because the VBA codes will be stored in an Add-in, but I want to save the new files in the original workbook's location instead (This is when I use ThisWorkbook.Path to identify the location).

Does anyone know if I can make Excel VBA read the original file's location and lock that value into a variable such that I can always call it back again when I need to reference it again?

Below is a sample code that I have. Any help will be greatly appreciated. Thank you!

Option Explicit

Sub new()

    Dim createWb As Workbook
    Dim Originalwks As Worksheet
    Dim createWbName As String

    Set Originalwks = ActiveWorkbook.ActiveSheet

    createWbName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
    Set createWb = Workbooks.Add
    createWb.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & createWbName
    createWb.Sheets("Sheet1").Name = createWbName

    Originalwks.UsedRange.Copy
    createWb.Worksheets(createWbName).Range("A1").PasteSpecial xlValues

    'I will still be adding new workbooks by copying values from the original workbook. 
    'But how do I pull out the original workbook again? 

    createWb.Close SaveChanges:=True

End Sub

If Originalwks is the Activesheet in ActiveWorkbook, then can't you just do Dim OriginalWb as Workbook and Set OriginalWb = ActiveWorkbook? — BigBen
That means if I am on my new workbook, the ActiveWorkbook will change to the new workbook. How do I call VBA to pull out the original workbook again? — Jing Yi
You'll have a variable OriginalWb - reference that. Although the ActiveWorkbook will change, that variable will always refer to the same workbook - the first one that was active. OriginalWks works the same way - you have a reference to the first active sheet even if ActiveSheet is changing. — BigBen
ThisWorkbook is always referring to the workbook that's running the VBA code. If that's an Excel add-in, then ThisWorkbook is the hidden workbook for that Excel add-in. — Mathieu Guindon
Personally, I try to avoid using ActiveWorkbook because it can change at unexpected times. There are several functions within Excel that create new workbooks on the fly which become the ActiveWorkbook and using ActiveWorkbook can create confusion about which workbook you are really using. Use variables to hold the workbook you are accessing so that you KNOW you have the right one. — Frank Ball

Ibo Ibo · Accepted Answer · 2018-08-22T18:44:54

You are confused about how to use ActiveWorkbook and ThisWorkbook objects. The best approach is always to take the control of all of the elements you are dealing with in your process, in this case you have a workbook that is your original workbook and you want to use its data and path later in your routine. It does not matter if you run your routine from an add-in or from the original workbook itself, or even from another workbook. Your original workbook has a path that you can define it in your code either dynamically or just hard coded. So here are some options you can use:

If the original workbook is open and active then do this to always refer to it and retrieve its path:

Dim WB_Orig As Workbook
Set WB_Orig = ActiveWorkbook

if activeworkbook changes it will not cause a problem. Also, regardless of running the code from add-in or the original workbook itself, activewokbook will be that one (the one you see on your screen), if you run it from add-in ThisWorkbook will refer to the add-in workbook and NOT your original workbook.

If you run the code from the add-in, I would try to locate the original workbook and open it like this and set it to an object at the same time:

Set WB_Orig = Application.Workbooks.Open("filename") 'filename is the path to your original workbook

also, there could be a case that the workbook is open so it is better to check all the open workbook names before trying to open it. You can iterate over the open workbooks and check their names and if you had a match simply assign it as the WB_Orig:

Function GetOriginalWorkbook(sFilename As String)
    Dim WB As Workbook
    For Each WB In Application.Workbooks
        If WB.Name = sFilename Then
            Set GetOriginalWorkbook = WB
            Exit For
        End If
    Next
End Function

using the above function in your sub you can easily do:

Set WB_Orig = GetOriginalWorkbook("Book1") 'you need to change Book1 with the name of your origianl workbook.

If Original workbook is open then you have it as an object already assigned to WB_Orig otherwise it will be Nothing and you should open it:

Set WB_Orig = GetOriginalWorkbook("Book1")

if WB_Orig is Nothing then
     Set WB_Orig = Application.Workbooks.Open("filename")
end if

Another trick would be setting on error to resume next before trying to open the original workbook and thensetting it back to goto 0. This way if the original workbook was not open, Excel will open it, if it was open, an error should be thrown, but since you told excel to ignore it, it will resume executing the code and you should be fine. Using on error resume next is tricky and as a good practice you should always try to foresee and control the errors not ignore them.

Excel VBA Selecting Original Workbook

1 Answers