Add OLEObjects work inside macro and fail if executed as vbs script in cmd

Question

code executed inside the excel file (xlsm) work perfectly

 Sub insertObject()
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("YourPath\Datasheet.xlsx")
    objExcel.Application.Visible = True
    objWorkbook.ActiveSheet.OLEObjects.Add(Filename:="YourPath\Placeholder.txt", _
            Link:=False, DisplayAsIcon:=True, IconFileName:= _
            "C:\Windows\system32\packager.dll", IconIndex:=0, IconLabel:= _
            "C:\Placeholder.txt").Select
    objWorkbook.SaveAs "yourPath\test.xlsx"
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
    WScript.Quit
    End Sub

this is the code i try to execute using vbs script

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("YourPath\Datasheet.xlsx")
objExcel.Application.Visible = True
ActiveSheet.OLEObjects.Add(Filename:="YourPath\Placeholder.txt", _
        Link:=False, DisplayAsIcon:=True, IconFileName:= _
        "C:\Windows\system32\packager.dll", IconIndex:=0, IconLabel:= _
        "C:\Placeholder.txt").Select
objWorkbook.SaveAs "YourPath\test.xlsx"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Quit

i replace the paths with the real ones of course the code work perfectly inside the macro but failed inside vbs script when i try to run it using the cmd command.

Failed in what way? What happended? Did you get error messages/codes? — QHarr
– QHarr, Commented Jul 25, 2018 at 11:39

TAOUFIK BEZZOT · Accepted Answer · 2018-07-25 14:20:23Z

2

I get the same thing when I run your code. I tried a few different tweaks and it works: you simply forgot the first parameter of the add function of OLEobject and dont forget to remove the parameters names :

 Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("YourPath\Datasheet.xlsx")
    objExcel.Application.Visible = false
    Set ob = objWorkbook.ActiveSheet.OLEObjects
    Set t = ob.Add (,"YourPath\Placeholder.txt",False, True,    "C:\Windows\system32\packager.dll", 0,"C:\Placeholder.txt")

answered Jul 25, 2018 at 14:20

TAOUFIK BEZZOT

712 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add OLEObjects work inside macro and fail if executed as vbs script in cmd

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related