TartanSpecial
New Member
- Joined
- May 29, 2005
- Messages
- 42
Continuing in a mini-series of TartanSpecial not having a clue about VBA.....
One of the reports I've inherited in my job has the code below. The code is actually in Business Object but open up a series of Excel files. The files open correctly but for some reason at the very end the file "Service_Comms_Ranking1" re-open , overwriting the changes already made.
Nothing in the Excel files seems to prompt it do so. I was wondering if anything in this code would prompt the file to be opened twice (or whether it lurks somewhere else)
As always any help is really gratefully received
Sub LaunchRankingExcel()
Const blank As String = " "
Dim retval
Dim filename, sWorkingDir, sOutputDir As String
Dim objXLS As New Excel.Application
Dim sCommandLine As String
Dim sXLInstallationDir As String
Application.Interactive = True
sWorkingDir = Application.GetInstallDirectory(boDocumentDirectory) & "\"
sOutputDir = "\\ldnroot\data\Equities\Trading\MDM\CMT_reporting\Marketing Packs\Embedded Charts DO NOT ERASE\"
Set objXLS = Excel.Application
objXLS.Visible = False
objXLS.Interactive = False
objXLS.DisplayAlerts = False
sXLInstallationDir = objXLS.Path & "\"
filename = "Service_Comms_Ranking1"
objXLS.Quit
Set objXLS = Nothing
If Application.Interactive Then
sCommandLine = sXLInstallationDir & "Excel.exe " & Chr$(34) & sOutputDir & filename & Chr$(34)
retval = Shell(sCommandLine, vbNormalFocus)
End If
End Sub
One of the reports I've inherited in my job has the code below. The code is actually in Business Object but open up a series of Excel files. The files open correctly but for some reason at the very end the file "Service_Comms_Ranking1" re-open , overwriting the changes already made.
Nothing in the Excel files seems to prompt it do so. I was wondering if anything in this code would prompt the file to be opened twice (or whether it lurks somewhere else)
As always any help is really gratefully received
Sub LaunchRankingExcel()
Const blank As String = " "
Dim retval
Dim filename, sWorkingDir, sOutputDir As String
Dim objXLS As New Excel.Application
Dim sCommandLine As String
Dim sXLInstallationDir As String
Application.Interactive = True
sWorkingDir = Application.GetInstallDirectory(boDocumentDirectory) & "\"
sOutputDir = "\\ldnroot\data\Equities\Trading\MDM\CMT_reporting\Marketing Packs\Embedded Charts DO NOT ERASE\"
Set objXLS = Excel.Application
objXLS.Visible = False
objXLS.Interactive = False
objXLS.DisplayAlerts = False
sXLInstallationDir = objXLS.Path & "\"
filename = "Service_Comms_Ranking1"
objXLS.Quit
Set objXLS = Nothing
If Application.Interactive Then
sCommandLine = sXLInstallationDir & "Excel.exe " & Chr$(34) & sOutputDir & filename & Chr$(34)
retval = Shell(sCommandLine, vbNormalFocus)
End If
End Sub