CODEWITHSUNDEEP
excelvbscriptvba
Luke N.
Luke N.

Reputation: 1

VB Script Keeps giving me "Macro may not be available in this workbook or all macros may be disabled"

I've been trying to make a simple VBS that just opens Excel and runs a specific macro. I swear months ago when I wrote this it had worked. I've scoured the forums for similar errors and nothing suggested seems to have worked for me.

Any ideas? 

Option Explicit
Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")

xlApp.DisplayAlerts=false
Set xlBook = xlApp.Workbooks.Open("C:\Users\lnguy\Desktop\New Chemical Receipt Form.xlsm", 0, True)

xlApp.Run "'C:\Users\lnguy\Desktop\New Chemical Receipt Form.xlsm'!Expired2"

xlBook.Close

xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit

Upvotes: 0

Views: 1459

Answers (2)

Dave
Dave

Reputation: 4356

Move the macro from Worksheet1 into its own module and ensure it's a Public Sub so it can be referenced.

Upvotes: 0

trincot
trincot

Reputation: 350365

You have a different path the second time you mention it (FTO Development\ is missing), so the macro cannot be found.

I would suggest to prevent this from happening by using a variable for the path:

Dim path
path = "C:\Users\lnguy\Desktop\FTO Development\New Chemical Receipt Form.xlsm"

' ....

Set xlBook = xlApp.Workbooks.Open(path, 0, True)

xlApp.Run "'" & path & "'!Expired2"

Upvotes: 2

Related Questions