Excel – “The visual basic for applications (vba) macros in this workbook are corrupted and have been deleted”

For Excel, your macros should be stored in the Personal Macro Workbook (PERSONAL.XLSB), which is by default hidden. Word has a Normal template which (IIRC) can store macros. Outlook has a similar thing, but I can't remember what it's called. PowerPoint is the anomaly and as far as I remember doesn't seem to have a place to store global macros.

I use SyncBackSE to sync my Excel macro workbook between computers. I don't update Outlook macros frequently enough to need a way of syncing them, but you could probably do something similar. Syncing can only be done when Excel is closed, as it locks the workbook. I don't know how you'd implement version control.

You are on the right track. What you need first is to set up a new variable for the Excel.Application using late binding. It's under that object where all the normal excel VBA goodies hide. Below is an example vbs script that will open a new workbook, add a new sheet, name it "TEST", and add some content into cell A1 of that new sheet. When it's done, vbs will echo "Finished"

Sub createWB()

  'Set the excel application variable
  set xlApp = CreateObject("Excel.Application")

  'Make it visible, or hide it in the background
  xlApp.Application.Visible = true

  'Open a new workbook, for instance
  set xlWB = xlApp.Workbooks.Add()
  xlWB.Activate

  'Add a sheet, name it test, bring it front and center
  set xlSheet = xlWB.Worksheets.Add()
  xlSheet.Name = "TEST"
  xlSheet.Activate

  'Put something in cell A1
  xlSheet.Cells(1,1).value = "TEST CELL CONTENT"

End Sub

'Call the subroutine above
call createWB

WScript.Echo "Finished."
WScript.Quit

You may have to monkey around a bit with your existing VBA code to make vbs happy, but you'll find that writing your macros in vbs, is just as functional as writing them in vba.