Fetch Data into Excel from batch file using vbs script

Question

I have a batch file that run set of SQL queries and loads data into a table.

I have written VBA script for button click in excel to retrieve the data from the above table.

However now my requirement has changed to populate data into excel without button click. Also I do not want my code in workbook open event.

I have to change my vba code to .vbs script so that I can call it from batch file. Please help me. Correct me if I am wrong with my approach.

Why don't you use an Excel Data Table instead: excelusergroup.org/blogs/nickhodge/archive/2008/11/04/… — Jobbo
– Jobbo, Commented Oct 17, 2014 at 8:15

AnalystCave.com · Accepted Answer · 2014-10-17 08:54:58Z

1

Write a VBscript that:

Executes the bat

shell.Run """C:\...\my.bat"""

Then executes the macro (example below)

    RunMacro
     Sub RunMacro()
     Dim xl
     Dim xlBook      
     Dim sCurPath
     path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
     Set xl = CreateObject("Excel.application")
     Set xlBook = xl.Workbooks.Open(path & "\Workbook.xlsm", 0, True)      
     xl.Application.Visible = False
     xl.DisplayAlerts = False    
     xl.Application.run "Workbook.xlsm!Module.RunMacro"
     xl.ActiveWindow.close
     xl.Quit

     Set xlBook = Nothing
     Set xl = Nothing
 End Sub

answered Oct 17, 2014 at 8:54

AnalystCave.com

5,1242 gold badges25 silver badges31 bronze badges

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

6 Comments

Sowjanya Somanchi Over a year ago

My excel is not getting updated with data. However the macro is running correctly. I called the vbs from bat file.

AnalystCave.com Over a year ago

You need to add a line to save the Excel file -msdn.microsoft.com/en-us/library/office/… The code above just executes the macro

Sowjanya Somanchi Over a year ago

I saved my excel file as .xlsm and everytime I open it, i have to enable the content. Is this the issue when opening the file through script also?

AnalystCave.com Over a year ago

Try SaveAs instead and maybe increase the Trust level so the warning (enable macros) should not appear - not sure about this one but worth a try to make sure.

AnalystCave.com Over a year ago

Did saveas work or did you have to change the trust level too?

|

Collectives™ on Stack Overflow

Fetch Data into Excel from batch file using vbs script

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related