CODEWITHSUNDEEP
vbaexcelbatch-filevbscript
Sowjanya Somanchi
Sowjanya Somanchi

Reputation: 1

Fetch Data into Excel from batch file using vbs script

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.

Upvotes: 0

Views: 1024

Answers (1)

AnalystCave.com
AnalystCave.com

Reputation: 4974

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

Upvotes: 1

Related Questions