excel vba trim workbook name and set as activeworkbook
Posted on 2012-09-01
I am writing an import task that I will use every week. However the file I need to copy from has a variable name (after character #37) but I need it to identify this open workbook and set it as my source workbook to import from. Any assistance would be great.
The full name of a workbook could be:
"Theoretical Ingred Usage (Excel) Site 15.xls"
where the the variable numeric value after the word "Site " would change. Everything else will be the same.
When I go to identify the workbook to use as an import - I need it to trim the name or some other way to identify it without erroring out.
It will be the only other workbook open. It will always have the extension ".xls"
The sheet in this workbook will always be "Sheet1" that I use to pull data.
How can I write my code to identify this open workbook as my source workbook to import from with a variable name and set it as a source-workbook to import into a destination-workbook?
I would like to avoid Windows(2).activate and set it from there.