CODEWITHSUNDEEP
vbaexcelsave
Duffleberry
Duffleberry

Reputation: 1

Open an Excel file, run a macro, save it (overwrite)

I'm currently trying to create a .vbs which

  1. opens Excel
  2. runs macro
  3. saves it with new information
  4. quits

This is what I have so far:

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False

Set xlBook = xlApp.Workbooks.Open("C:\test\test.xlsm", 0, True) 

xlApp.Run "test"
xlbook.Save
xlBook.Close False
set xlBook = Nothing

xlApp.Quit
Set xlApp = Nothing

WScript.Echo "Upload is Finished"
WScript.Quit

I get the 'Upload is finished', but the macro is not saved. What am I doing wrong?

The macro is simply putting something from one worksheet to another:

Sub test()
'
' test Macro
'
    Sheets("Sheet2").Select
    Range("A1:A10").Select
    Range("A10").Activate
    Selection.Cut
    ActiveSheet.Previous.Select
    Selection.End(xlToLeft).Select
    Range("A2").Select
    ActiveSheet.Paste
End Sub

Upvotes: 0

Views: 3199

Answers (2)

Tim Williams
Tim Williams

Reputation: 166241

xlBook.Close False

means "close and don't save changes". Maybe you want

xlBook.Close SaveChanges:=True

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6206

Out of curiosity why a macro in Excel? If you are controlling Excel via the object, why not have the macro code in there also?

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False

Set xlBook = xlApp.Workbooks.Open("C:\test\test.xlsm", 0, True) 

xlbook.Sheets("Sheet2").Range("A1:A10").Cut
xlbook.Sheets("Sheet2").Previous.Select
xlbook.Range("A2").Paste
xlbook.Save
xlBook.Close False
set xlBook = Nothing

xlApp.Quit
Set xlApp = Nothing

WScript.Echo "Upload is Finished"
WScript.Quit

Upvotes: 2

Related Questions