reference workbooks() with a workbook that has spaces in between its file name

Question

I work with multiple workbooks open at the same time to track articles that I write. Both workbooks has respective macros and user forms that edit its data, one of which has a modeless userform. How can I make sure that the modeless userform do changes on the right workbook. I've tried referencing, Workbooks("Published Articles Tracker v3.0.xlsm").Sheets(Sheet2).Activate, but it always return an error. I would like to keep referencing my workbook using workbooks() method but I think the problem is the format of my filename, but if possible I'd like to keep it this way. Any thoughts?

Is Sheet2 a variable which holds the Sheet Name? Otherwise it should be surrounded with double quotes like this: `Workbooks("Published Articles Tracker v3.0.xlsm").Sheets("Sheet2").Activate` — Subodh Tiwari sktneer, Sep 19 '18 at 06:33
Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Luuklag, Sep 19 '18 at 06:43

score 0 · Answer 1 · answered Sep 19 '18 at 13:12

Use variables to point to your workbooks, and your sheets - it's faster, easier to read, and less of a pain to change if the name has to alter.

Global ThatWorkbook as Workbook
'NB ThisWorkbook is predefined by VB to refer to whichever 
 'workbook is hosting the code


Sub Disambiguate()
dim wb as workbook

for each wb in workbooks
    if wb.name =""Published Articles Tracker v3.0.xlsm" then
         Set ThatWorkbook = wb
         Exit For
     End If
 Next ws
 If ThatWorkbook is Nothing then 
    MSGBOX "File Not Found",vbOkOnly,"No Workbook"
 End IF
 End sub

reference workbooks() with a workbook that has spaces in between its file name

1 Answers1