Closing Excel After using it from Word

[fredlo2010]

Hello guys,

I am working on a function that will open an excel document input some values and then retrieve some information back. The code works as intended but when I was going to program an error handler in case the file is missing, I realized I had several instances of excel open.

How can I close them I Have tried myExcel.quit but it does not work.

This is my Function code:

Public Function GetTransportPrice(Miles As Double) As String

Dim xlApp As Object
Dim xlWB As Object
Dim price As String


Const strFileFullName = "C:\Users\Alfred\Desktop\Miles.xlsx"
Const intPad As Integer = 30


Set xlApp = CreateObject("Excel.Application")
On Error Resume Next
Set xlWB = xlApp.Workbooks.Open(strFileFullName)


'Pass the value to the workbook and get the results
xlWB.Sheets("Data").Range("A2").Value = Round(Miles)
xlWB.Application.Calculate
xlWB.Save
price = xlWB.Sheets("Data").Range("B2").Value


On Error GoTo 0


'If the price is zero then make it TBD
If price = "0" Then price = "TBD"


'format asn currency and add padding with dots
price = Format(price, "$ #,##0.00")
price = Right(String(intPad, ".") & price, intPad)


'set variables to nothing to free memory
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing




'Return the value
GetTransportPrice = price


End Function

This is my task manager showing the file being used.

10931


Thanks

[macropod]

You need some error-trapping. For example:

Const strFileFullName = "C:\Users\Alfred\Desktop\Miles.xlsx"
Const intPad As Integer = 30
If Dir(strFileFullName) = "" Then
MsgBox "File not found:" & vbCr & strFileFullName, vbCritical
GetTransportPrice = 0
Exit Function
End If
Set xlApp = CreateObject("Excel.Application")

[fredlo2010]

Hello,

Thanks for the response.

this is the final function with error-trapping:




Public Function GetTransportPrice(Miles As Double) As String


Dim xlApp As Object
Dim xlWB As Object
Dim price As String


Const strFileFullName = "C:\Users\Alfred\Desktop\Miles.xlsx"
Const intPad As Integer = 30


Set xlApp = CreateObject("Excel.Application")
On Error Resume Next
Set xlWB = xlApp.Workbooks.Open(strFileFullName)


'Pass the value to the workbook and get the results
xlWB.Sheets("Data").Range("A2").Value = Round(Miles)
xlWB.Application.Calculate
xlWB.Save
price = xlWB.Sheets("Data").Range("B2").Value


On Error GoTo 0


'If the price is zero, or if the file was not found then make it TBD
If price = vbNullString Then
price = "TBD"
MsgBox "The transport Excel File is missing.", vbInformation + vbOKOnly, _
"Missing Transport File"
ElseIf price = 0 Then
price = "TBD"
End If


'format asn currency and add padding with dots
price = Format(price, "$ #,##0.00")
price = Right(String(intPad, ".") & price, intPad)


'set variables to nothing to free memory
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing




'Return the value
GetTransportPrice = price


End Function

The main issue is still the instance of excel that remains open.

[macropod]

But you're not actually handling the error; all you're doing is ignoring it and its consequences. In the code I posted, if the file doesn't exist, Excel never gets started. That is quite a different approach to opening Excel anyway then leaving it with an unhandled error.

[fredlo2010]

Thanks a lot Macropod, you are totally right now I see what I was doing. I was creating the application and then trying to close it but I could not because nothing was assigned to it.

Lesson of the day: always check if your file is missing before doing anything else :)

This is my final Function:

Public Function GetTransportPrice(Miles As Double) As String
Dim xlApp As Object
Dim xlWB As Object
Dim price As String

Const strFileFullName = "C:\Users\Alfred\Desktop\Miles.xlsx"
Const intPad As Integer = 30

If Dir(strFileFullName) = "" Then
MsgBox "File not found:" & vbCr & strFileFullName, vbCritical
GetTransportPrice = 0
Exit Function
End If


Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(strFileFullName)


'Pass the value to the workbook and get the results
xlWB.Sheets("Data").Range("A2").Value = Round(Miles)
xlWB.Application.Calculate
xlWB.Save
price = xlWB.Sheets("Data").Range("B2").Value


'If the price is zero then make it TBD
If price = "0" Then price = "TBD"

'format asn currency and add padding with dots
price = Format(price, "$ #,##0.00")
price = Right(String(intPad, ".") & price, intPad)

'set variables to nothing to free memory
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing

'Return the value
GetTransportPrice = price

End Function

By the way this is an old idea I had in my head for a long time so I decided to implement it today. Turns out I know how to use other VBA features now so using excel for this is not convenient. This is my final simpler function (that does not have to worry about any external file), the cons its hard coded :

Public Function GetTransportPrice2(strMiles As Double) As String

Const intPad As Integer = 50
Dim strPrice As String


Select Case CInt(strMiles)
Case Is = 0
strPrice = "TBD"
Case 1 To 29
strPrice = 345
Case 30 To 59
strPrice = 405
Case 60 To 99
strPrice = 485
Case 100 To 139
strPrice = 530
Case 140 To 179
strPrice = 550
Case 180 To 219
strPrice = 640
Case 220 To 259
strPrice = 670
Case 260 To 299
strPrice = 700
Case 300 To 339
strPrice = 820
Case 340 To 449
strPrice = 830
Case 450 To 549
strPrice = 1020
Case 550 To 649
strPrice = 1155
Case 650 To 759
strPrice = 1420
Case Is >= 760
strPrice = "TBD"
End Select


'format asn currency and add padding with dots
strPrice = Format(strPrice, "$ #,##0.00")
strPrice = Right(String(intPad, ".") & strPrice, intPad)

GetTransportPrice = strPrice

End Function

Thanks a lot for your help :)

[macropod]

FWIW, where I coded:
GetTransportPrice = 0
you might actually want to use:
GetTransportPrice = "TBD"

You might also want to add some error handling for:
1. The file being in use (especially as you're not opening it in read-only mode)
2. The 'Data' sheet not being found.

[fredlo2010]

Thank you, yes I changed the 0 for "TBD"

Yes I would definitely look into that in the future. In this case would be an overkill. I have the file in the server in a hidden folder. No one knows that's there besides me ;)

Once again Macropod you are the Word Macro star of this forum ! ;)