Closing a CSV file with VBA

Ian_h13

New Member
Joined
Mar 13, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Ian_h13

New Member
Joined
Mar 13, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,494
Messages
5,897,744
Members
434,675
Latest member
AmitSatnalika

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top