Hi
I have a VBS script which instructs MS Money to run and export two reports and save the data to CSV files.
I have Office Home 365 on a modern Windows 10 PC.
It ran fine until a recent update of Excel but now the SendKeys command to 'Save and Close' the CSV file is not recognized so the script gets lost sending the subsequent commands to the unclosed file.
The previous command I was using to close the CSV file was on line 96 was:
wShell.SendKeys "%{F4}", True 'Closes the Excel application with CSV file
This no longer close the file.
The same command is used on line 112 to close the second report.
I can upload the full script if further context is required but am not sure how to attach as this is my first post here.
Any help on what ammended instruction is required for the latest version of Excel would be greatly apppreciated.
Kind regards
Ian
Here is the script with the non functioning commands in Red.
Option Explicit
Dim StockQuotesPath, StockQuotesXLSX, StockQuotesXLSXFullName, StockQuotesCSV, StockQuotesCSVFullName, StockQuotesShtName
Dim CommandLine1, CommandLine2, wShell, MSMoneyQuoteAppFullName, MSMoneyAppPath, MSMoneyAppName, MSMoneyAppFullName, ExportPath
MSMoneyAppPath = "C:\Program Files (x86)\Microsoft Money 2005\MNYCoreFiles"
MSMoneyAppName = "msmoney.exe"
MSMoneyAppFullName = MSMoneyAppPath & "\" & MSMoneyAppName
MSMoneyQuoteAppFullName = "D:\Users\hanna\Google Drive\Backups\Money Executable\MSMoneyQuotes.exe"
ExportPath = "D:\Users\hanna\Google Drive\Power BI\MS Money Data\Net Worth"
StockQuotesPath = "D:\Users\hanna\Google Drive\Power BI"
StockQuotesXLSX = "Stock Quotes.xlsx"
StockQuotesCSV = "Stock Quotes.csv"
StockQuotesShtName = "Data"
StockQuotesXLSXFullName = StockQuotesPath & "\" & StockQuotesXLSX
StockQuotesCSVFullName = StockQuotesPath & "\" & StockQuotesCSV
'-------------------------Delete Stock Quotes file it it exits-----------------------
Dim FSObj
Set FSObj = CreateObject("Scripting.FileSystemObject") 'Calls the File System Object
If FSObj.FileExists(StockQuotesCSVFullName) Then
FSObj.DeleteFile(StockQuotesCSVFullName) 'Deletes the file throught the DeleteFile function
End If
'-------------------------Open Stock Quotes XLSX and save as CSV file ---------------
Dim objXLApp, objXLWb, objXLWs
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True
Set objXLWb = objXLApp.Workbooks.Open(StockQuotesXLSXFullName)
Set objXLWs = objXLWb.Sheets(StockQuotesShtName)
objXLWb.RefreshAll
wscript.Sleep 10000
'Number formatting of the data
With objXLWs
.Columns("C:C").NumberFormat = "#0.0"
.Columns("E:E").NumberFormat = "dd/mm/yyyy"
.Columns("I:I").NumberFormat = "#0.0"
.Columns("J:J").NumberFormat = "dd/mm/yyyy HH:MM:SS"
End With
objXLWb.SaveAs StockQuotesCSVFullName, 6 'The number 6 is for xlCSV file format
objXLWb.Close (False)
Set objXLWs = Nothing
Set objXLWb = Nothing
objXLApp.Quit
Set objXLApp = Nothing
'---------------------------Import stock quotes-----------------------------------
set wShell = wscript.createobject("wscript.shell")
CommandLine1 = """" & MSMoneyQuoteAppFullName & """ -c -i """ & StockQuotesCSVFullName & """"
'msgbox CommandLine1
Call wShell.Run (CommandLine1, 8, false)
wscript.Sleep 30000
'---------------------------Start MS Money----------------------------------------
dim Rpt1ExportFileName, Rpt2ExportFileName, MSMoneyObj,MSMoneyPID
dim dtsnow, dd, mm, yy, hh, nn, ss
dim IfMSMoneyRunningPID
dtsnow = NOW()
'Individual date components
dd = Right("00" & Day(dtsnow), 2)
mm = Right("00" & Month(dtsnow), 2)
yy = Year(dtsnow)
hh = Right("00" & Hour(dtsnow), 2)
nn = Right("00" & Minute(dtsnow), 2)
ss = Right("00" & Second(dtsnow), 2)
Rpt1ExportFileName = "MSM Accounts_" & yy & "_" & mm & "_" & dd & "_" & hh & nn & ss & ".csv"
Rpt2ExportFileName = "Port_brief_" & yy & "_" & mm & "_" & dd & "_" & hh & nn & ss & ".csv"
IfMSMoneyRunningPID = IfProcessRunningThenPID("GardenOffice", MSMoneyAppName)
if IfMSMoneyRunningPID <> 0 Then
wShell.Run ("taskkill /t /f /pid " & IfMSMoneyRunningPID)
wscript.Sleep 5000
end if
CommandLine2 = """" & MSMoneyAppFullName & """"
Set MSMoneyObj = wShell.Exec(CommandLine2)
MSMoneyPID = MSMoneyObj.ProcessID
wShell.appactivate(MSMoneyPID)
wscript.Sleep 10000
'----Exporting first specified report to Excel format
wShell.SendKeys "%AOR"
wShell.SendKeys "{DOWN 26}~"
wscript.Sleep 5000
wShell.SendKeys "{TAB 2}{DOWN 4}~" 'On 'Net Worth Over Time - Power BI' report we need to press tab twice to go to the left pane
wscript.Sleep 5000
wShell.SendKeys "%N"
wscript.Sleep 2000
wShell.SendKeys ExportPath & "\" & Rpt1ExportFileName
wscript.Sleep 2000
wShell.SendKeys "~"
wscript.Sleep 10000
wShell.SendKeys "%{F4}", True 'Closes the Excel application with CSV file
'----Exporting second specified report to Excel format
wscript.Sleep 5000
wShell.SendKeys "%AOR"
wShell.SendKeys "{DOWN 28}~"
wscript.Sleep 5000
wShell.SendKeys "{TAB 2}{DOWN 4}~" 'On second report we need to press tab twice to go to the left pane
wscript.Sleep 5000
wShell.SendKeys "%N"
wscript.Sleep 2000
wShell.SendKeys ExportPath & "\" & Rpt2ExportFileName
wscript.Sleep 2000
wShell.SendKeys "~"
wscript.Sleep 5000
wShell.SendKeys "%{F4}" 'Closes the Excel application with CSV file
wscript.Sleep 10000
wShell.SendKeys "%{F4}" 'Closes MS Money
set MSMoneyObj = Nothing
Set wShell = Nothing
Function IfProcessRunningThenPID( strComputer, strProcess )
Dim Process, strObject
IfProcessRunningThenPID = 0
strObject = "winmgmts://" & strComputer
For Each Process in GetObject( strObject ).InstancesOf( "win32_process" )
If UCase( Process.name ) = UCase( strProcess ) Then
IfProcessRunningThenPID = Process.ProcessID
Exit Function
End If
Next
End Function
