Link to home
Start Free TrialLog in
Avatar of kosenrufu
kosenrufu

asked on

How can this be fixed?

I have the following VBA script:

Set wstmp = Sheets("Temp")
Set wsstitmlnk = Sheets("Stat_ITEM_LNKS")

I get an error when I have the cursor in another workbook not which is not the file this script is in. How can I ensure the correct workbook is activated so this script works?

Thanks
Avatar of Jignesh Thar
Jignesh Thar
Flag of India image

you can add Windows("Book2.xlsx") before Sheets() statement to refer to open window with given file
Of course replace "Book2.xls" with filename of file for which this code is written
You can switch workbooks like this.

   Application.Workbooks("book1.xls").Activate

Scot
Avatar of kosenrufu
kosenrufu

ASKER

p912s: Is there a way to detect the current workbook's filename in which the script is in?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of anavit
anavit
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
kosenrufu - Did you mean to find current workbook in which this script is running? If yes, ActiveWorkbook.Name can return you name of it.
This will extract the path from the filename.

Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "\"))

Scot
If you wish to refer to sheets in the workbook containing the code, then you don't really need its name:

With ThisWorkbook
' note the periods before both Sheets calls.
Set wstmp = .Sheets("Temp")
Set wsstitmlnk = .Sheets("Stat_ITEM_LNKS")
End With

Open in new window