VBA called outside

Hi,

I have an excel sheet with a macro that merges two other sheets into one.  I would like to take that VBA and call it from a .cmd or something like that.   I need an external ETL tool to be able to call it and the ETL tool can call javascript or .cmd


Can you call VBA outside excel.  Excuse my ignorance but I know very little on the subject.

Thanks
Coco BeansDesignerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
I'm pretty sure you can't start Excel from a command line and on that command line specify a VBA procedure to run.

What you can do though is place code in the Excel file to run a procedure every time the workbook opens, see link below.

The other approach could bee to crate a small VBS script that uses automation to open the excel sheet and then execute the VBA code.



»bp
0
NorieAnalyst Assistant Commented:
What will the ETL tool do other than run the VBA code?
0
Coco BeansDesignerAuthor Commented:
Mostly database work.  Moving data between databases.  The excel sheet is a very small part.

You can't execute VBA in a .VBS?

Thanks
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewIT / Software Engineering ConsultantCommented:
No, you can't execute VBA in a VBS.  VBS code is similar to VBA code, but VBA runs inside an Office Application (Excel, Wrod, Powerpoint, ...) and allows access to the data in the application.

You may be able to convert the VBA to VBS if you are not doing anything too complex, what is the purpose of the VBA in the ETL process?


»bp
0
Coco BeansDesignerAuthor Commented:
Merges two excel sheets into one and removes extra formatting and worksheets.
0
Bill PrewIT / Software Engineering ConsultantCommented:
If it's not too big you could probably do all of that from VBS.  Here's an example from a prior question I worked, just giving you an idea how VBS can access a workbook, and then process it using automation.

' Constants
Const xlUp = -4162

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

' Instantiate Excel, and open the specified excel file
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open(objFSO.GetAbsolutePathName(strInFile), False, False)
objExcel.Visible = False
Set objSheet1 = objBook.Sheets(1)
Set objSheet2 = objBook.Sheets.Add

' Add header rown to new sheet
intRow2 = 1
objSheet2.Cells(intRow2, "A").Value = "Date"
objSheet2.Cells(intRow2, "B").Value = "Reference"
objSheet2.Cells(intRow2, "C").Value = "Description"
objSheet2.Cells(intRow2, "D").Value = "Amount"

' Process all rows in first sheet, building data into new sheet as desired
For intRow1 = 2 To objSheet1.Cells(65536, "A").End(xlUp).Row
   ' First line for this row in new sheet
   intRow2 = intRow2 + 1
   objSheet2.Cells(intRow2, "A").Value = objSheet1.Cells(intRow1, "A").Value
   objSheet2.Cells(intRow2, "B").Value = objSheet1.Cells(intRow1, "C").Value
   objSheet2.Cells(intRow2, "C").Value = objSheet1.Cells(intRow1, "D").Value
   objSheet2.Cells(intRow2, "D").Value = objSheet1.Cells(intRow1, "E").Value
   ' Second line for this row in new sheet
   intRow2 = intRow2 + 1
   objSheet2.Cells(intRow2, "A").Value = objSheet1.Cells(intRow1, "A").Value
   objSheet2.Cells(intRow2, "B").Value = objSheet1.Cells(intRow1, "C").Value
   objSheet2.Cells(intRow2, "C").Value = objSheet1.Cells(intRow1, "D").Value
   objSheet2.Cells(intRow2, "D").Value = objSheet1.Cells(intRow1, "F").Value + objSheet1.Cells(intRow1, "G").Value + objSheet1.Cel
Next

' Save changes and close Excel
objBook.Save
objBook.Close
objExcel.Quit

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coco BeansDesignerAuthor Commented:
THanks Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.