Returning a value from another application

[Rafi]

I am calling an Excel module from Access and would like to know if the Excel
module produces a certain result (Warning = True). How do i return the value
of the variable warning from the excel module to Access

Code in Access Module

Private Sub Cmd1_Click()
Dim XL As Object
Dim fName As String
Set XL = CreateObject("Excel.Application")
XL.Visible = False
fName = "C:\Documents and Settings\595930.CORP\My Documents\SDI\Andy\Sample
Dirt Template 1-4-07.xls"
XL.Workbooks.Open fName
XL.Run "Validation.Validation"
XL.ActiveWorkbook.Close
XL.Quit
Set XL = Nothing
End Sub

Code In Excel

Sub Validation()
Dim LastRow As Long
Dim Msg1 As String
Dim Msg2 As String
Dim Msg3 As String
Dim Title1 As String
Dim RCOUNT As Long
Dim CCount As Long
Dim wb As Workbook
Dim fName As String
Dim Warning As Boolean
Msg1 = "This file is empty. The validation process will terminate!"
Msg2 = "End of Validation"
Msg3 = "The input file contains errors, see highlighted cells"
Title1 = "VALIDATION ERROR"
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

If LastRow < 2 Then
MsgBox Msg1, vbOKOnly, Title1
End If

Application.ScreenUpdating = False
RCOUNT = 2
CCount = 4
Cells(RCOUNT, CCount).Select
For CCount = CCount To 10

For RCOUNT = RCOUNT To LastRow
If Not (WorksheetFunction.IsNumber(ActiveCell.Value)) Then
Warning = True
ActiveCell.Interior.ColorIndex = 27
End If
ActiveCell.Offset(1, 0).Select
Next

RCOUNT = 2
Cells(2, CCount + 1).Select
Next
MsgBox Msg2, vbOKOnly
Set wb = ActiveWorkbook
With wb
fName = .Path & "\" & .Name
.SaveAs fName
End With
If Warning Then
Application.Visible = True
MsgBox Msg3
End If
End Sub

 

[PieterLinden]

I am calling an Excel module from Access and would like to know if the Excel
module produces a certain result (Warning = True).  How do i return the value
of the variable warning from the excel module to Access

Code in Access Module

Private Sub Cmd1_Click()
Dim XL As Object
Dim fName As String
Set XL = CreateObject("Excel.Application")
XL.Visible = False
fName = "C:\Documents and Settings\595930.CORP\My Documents\SDI\Andy\Sample
Dirt Template 1-4-07.xls"
XL.Workbooks.Open fName
XL.Run "Validation.Validation"
XL.ActiveWorkbook.Close
XL.Quit
Set XL = Nothing
End Sub

Code In Excel

Sub Validation()
Dim LastRow As Long
Dim Msg1 As String
Dim Msg2 As String
Dim Msg3 As String
Dim Title1 As String
Dim RCOUNT As Long
Dim CCount As Long
Dim wb As Workbook
Dim fName As String
Dim Warning As Boolean
Msg1 = "This file is empty. The validation process will terminate!"
Msg2 = "End of Validation"
Msg3 = "The input file contains errors, see highlighted cells"
Title1 = "VALIDATION ERROR"
LastRow = Cells.Find(What:="*", After:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row

If LastRow < 2 Then
    MsgBox Msg1, vbOKOnly, Title1
End If

Application.ScreenUpdating = False
RCOUNT = 2
CCount = 4
Cells(RCOUNT, CCount).Select
For CCount = CCount To 10

    For RCOUNT = RCOUNT To LastRow
    If Not (WorksheetFunction.IsNumber(ActiveCell.Value)) Then
    Warning = True
    ActiveCell.Interior.ColorIndex = 27
    End If
    ActiveCell.Offset(1, 0).Select
    Next

RCOUNT = 2
Cells(2, CCount + 1).Select
Next
MsgBox Msg2, vbOKOnly
Set wb = ActiveWorkbook
With wb
    fName = .Path & "\" & .Name
    .SaveAs fName
End With
If Warning Then
Application.Visible = True
MsgBox Msg3
End If
End Sub

Then why not change the routine from a Subroutine, which normally
cannot return a value, to a Function, which can. Just assign the
value of the warning to the function name, and you should be good to
go. You might want to declare your function return type as variant
until you figure out what kind of value is being returned. It's
probably a Long Integer, but I know next to nothing about Excel,
so ... want salt with that?

hope this helps.

Pieter

 

[Rafi]

Pieter,

I am a newcomer to VBA so please excuse the question....Do you mean change
Sub Validation() to Function Validation(x) where x is defined in both the
Access and Excel modules? Something like Dim x as Boolean?

Also, if that is the case, do I change the calling statement (in the Access
module) to: XL.Run "Validation.Validation(x)?

Thanks again for the help

I am calling an Excel module from Access and would like to know if the Excel
module produces a certain result (Warning = True). How do i return the value
of the variable warning from the excel module to Access

Code in Access Module

Private Sub Cmd1_Click()
Dim XL As Object
Dim fName As String
Set XL = CreateObject("Excel.Application")
XL.Visible = False
fName = "C:\Documents and Settings\595930.CORP\My Documents\SDI\Andy\Sample
Dirt Template 1-4-07.xls"
XL.Workbooks.Open fName
XL.Run "Validation.Validation"
XL.ActiveWorkbook.Close
XL.Quit
Set XL = Nothing
End Sub

Code In Excel

Sub Validation()
Dim LastRow As Long
Dim Msg1 As String
Dim Msg2 As String
Dim Msg3 As String
Dim Title1 As String
Dim RCOUNT As Long
Dim CCount As Long
Dim wb As Workbook
Dim fName As String
Dim Warning As Boolean
Msg1 = "This file is empty. The validation process will terminate!"
Msg2 = "End of Validation"
Msg3 = "The input file contains errors, see highlighted cells"
Title1 = "VALIDATION ERROR"
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

If LastRow < 2 Then
MsgBox Msg1, vbOKOnly, Title1
End If

Application.ScreenUpdating = False
RCOUNT = 2
CCount = 4
Cells(RCOUNT, CCount).Select
For CCount = CCount To 10

For RCOUNT = RCOUNT To LastRow
If Not (WorksheetFunction.IsNumber(ActiveCell.Value)) Then
Warning = True
ActiveCell.Interior.ColorIndex = 27
End If
ActiveCell.Offset(1, 0).Select
Next

RCOUNT = 2
Cells(2, CCount + 1).Select
Next
MsgBox Msg2, vbOKOnly
Set wb = ActiveWorkbook
With wb
fName = .Path & "\" & .Name
.SaveAs fName
End With
If Warning Then
Application.Visible = True
MsgBox Msg3
End If
End Sub

Then why not change the routine from a Subroutine, which normally
cannot return a value, to a Function, which can. Just assign the
value of the warning to the function name, and you should be good to
go. You might want to declare your function return type as variant
until you figure out what kind of value is being returned. It's
probably a Long Integer, but I know next to nothing about Excel,
so ... want salt with that?

hope this helps.

Pieter