Running Excel Macro in Batch File

You need to create a .vbs script file which runs VB (v similar to VBA). Why don't you create a Workbook and code the file copy in that. You can then continue with the code you need to run.

Edit this in notepad and save as a .vbs file extension. You can continue with Excels' Object Model using the instance of xlApp you have created.

Const Overwrite = True
Const SaveChanges = True
strSource = "f:\a\axys3\users\brandon\test.txt"
strDest = "C:\Users\btoney\Desktop\IMRF\test.txt"

Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile strSource, strDest , Overwrite
set xlApp = CreateObject("Excel.Application")
xlApp.visible = TRUE
xlApp.Workbooks.Open strDest
set wb = XlApp.Workbooks(1)
wb.Activesheet.Range("A1:C4").Select
xlApp.Selection.Font.Bold = True
wb.Close SaveChanges
xlApp.Quit

Added a little more code so you can see how to edit the sheet, save, close and quit the app. The font.Bold will have no effect if you save as a .txt file though, it's just there so you can see how to use the object model.

Hi,
When using vb to control Excel, enumerations (i.e. LookAt:=) and constants (i.e. xlPart) are outside the scope of the vb script, so you must be specific and use the function 'as is'. xlPart and xlByRows are actually integers, so vb must use integers when passing the code to the object model. You can declare them as constants at the beginning for readability.

Const Overwrite = True
Const xlPart = 2
Const xlByRows = 1

strSource = "f:\a\axys3\users\brandon\test.txt"
strDest = "C:\Users\btoney\Desktop\IMRF\test.txt"

Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile strSource, strDest , Overwrite
set xlApp = CreateObject("Excel.Application")
xlApp.visible = TRUE
xlApp.Workbooks.Open strDest
set wb = XlApp.Workbooks(1)
wb.Activesheet.Cells.Replace "causcash", "USD Curncy", xlPart, xlByRows, False, False, False, False

Values for constants can be found here

https://msdn.microsoft.com/en-us/lib.../ff839040.aspx

If all you are wanting to do is a text replace, you do not need Excel.

untested

strSource = "f:\a\axys3\users\brandon\test.txt"
strDest = "C:\Users\btoney\Desktop\IMRF\test.txt"
strFind = "causcash"
strReplace = "USD Curncy"
Set inputFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(strSource, 1)
strInputFile = inputFile.ReadAll
inputFile.Close
Set inputFile = Nothing
Set outputFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(strDest, 2, True)
outputFile.Write Replace(strInputFile, strFind, strReplace)
outputFile.Close
Set outputFile = Nothing

Thanks Mr. Neil
As for post #3 .. Where the created workbook saved?
How can I specify the path where I need to save?

@YasserKhalil

It defaulted as the file that was opened, so the save path would be the same

strDest = "C:\Users\btoney\Desktop\IMRF\test.txt"


If you wanted to save the text file as an Excel document before closing:-

  
wb.SaveAs  "C:\Test.xlsx"
wb.Close