VBScript - Effecient Close Of Excel Workbook and Application

[TDC21]

Hello all,

I am running a VBS macro in our ERP that opens a target Excel Document and populates the form with data from a workbook. The program works but the execution time is roughley 1 and a half minutes. I placed some MsgBox's throughout the script to see where the lag was comming from, and the entire script executes in about 4 to 5 seconds, but when watching active processes in Task Manager, I can see that Execl remains open for roughley 1 and a half minutes, and when it does close, the ERP application refreshes and populates with the data.

I think I may be using some inefficent code in closing Excel. I know this is a VBS question, not VBA but I hope this is still an adaquate forum as it pertains to Excel. Any help will be appreciated, I have placed my code below.

Dim WshShell 
Set WshShell = CreateObject("WScript.Shell")


Dim yr
yr = InputBox("Please enter the payroll year")


Dim wk
wk = InputBox("Please enter the payroll week")


Dim path
path = "K:\Staff Accountant 2\3. Payroll\1. Payroll JE's\" & yr & "\Hourly\WK " & wk & "\RZU Payroll Template.xlsx"


Function FileExists(FilePath)
     Set fso = CreateObject("Scripting.FileSystemObject")
     If fso.FileExists(FilePath) Then
          FileExists=CBool(1)
     Else
          FileExists=Cbool(0)
     End If
End Function


If FileExists(path) Then
     Dim objExcel, objWorkbook, objSheet
     Set objExcel = CreateObject("Excel.Application")
     Set objWorkbook = objExcel.Workbooks.Open(path)
     Set objSheet = objExcel.ActiveWorkbook.Worksheets(3)


     Dim row
     row = 3


     Do While row <=28 
          val1 = objSheet.Cells(row,2).value
          val2 = objSheet.Cells(row,4).value
          val3 = objSheet.Cells(row,5).value
          WshShell.SendKeys "{INSERT}"
          WshShell.SendKeys val1
          WshShell.SendKeys "{TAB}"
          WshShell.SendKeys val2
          WshShell.SendKeys "{TAB}"
          WshShell.SendKeys val3
          row = row + 1
     Loop


     objExcel.ActiveWorkbook.Close
     objExcel.Workbooks.Close
     objExcel.Application.Quit
     Set objExcel = Nothing
Else
     MsgBox("Your entries resulted in an invalid File Path.  Please check the file location and try again")
End If


MsgBox(val1)

 

[NdNoviceHlp]

I'm guessing, but U named your function "FileExists" which is a VB term (which U also use)… this is bad. Not sure why you need send keys? HTH. Dave

 

[TDC21]

Thanks for the feedback Dave,

1) Send Keys, our ERP is an Infor product so I dont have access to the application code, dont think I would be brave enough to use it even if I did lol. The decision was to use a macro inside the application to retreive data instead of scripting some VBA to directly insert data from the workbook into the database. The Send Keys are used to insert new lines, navigate data entry fields inside the application, and to populate those fields. Based on what I found in some Infor documentation, I believe this is the correct approach.

2) FileExists, I dont know much VBS to be perfectly honest, but it is the language that is required. This code is an edit of an example that I found online.
a) The point regarding naming conventions is a good one but I am a little unclear, are you also saying that there is a portion of the code that is VB.NET or VBA, and by extension not VBS? If so could you let me know exactly which part, I can research some alternitives and revise if you think it could improve the execution time.