Run Macro When Formula Result Changes

[redbaron06]

Hi everyone, I would really appreciate some VBA help.

I would like the macro below to run automatically if the cell "BO1" Changes from its previous value. Cell "BO1" is determinded by drop down menus on another worksheet. Any help would be greatly appreciated.

-Terry

___________________________

Sub DoGetData()
Dim strFile As String
Dim strRange As String
strFile = Range("BO1").Value
GetDataFromClosedWorkbook strFile, "A8:N200", ActiveCell, False
End Sub
'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", _
' "A1:B21", Active - Cell, False
' GetDataFromClosedWorkbook "U:\MPLS\KAZ\306_KAZAKHSTAN__PSC_20100602.xls", "A1:J200", "A10", False
'

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("BO1").Value <> OldVal Then
    OldVal = Range("BO1").Value
    Call DoGetData
End If
End Sub

 

[redbaron06]

Thanks VoG, this seems like it should work, but it forces the "The source file or source range is invalid!" prompt from the original code.

I do not know if this makes a difference, but the value in BO1 is not a number, rather the path [also here strFile = Range("BO1").Value] of the file. Essentially I tried to create a variable file name. Augh.

 

[VoG]

The data type in the cell shouldn't make a difference. You'll need to troubleshoot your original code

 

[redbaron06]

Ill tamper with it, thanks so much for the help. I am pretty sure the problem lies in the fact that when the selection is made from the drop down list there is no active cell, as the drop down list that generates "BO1" is in another worksheet, I am going to try tomorrow when I am back at my computer:

GetDataFromClosedWorkbook strFile, "A8:N200", Range("A10"), True

I will get back to you on the result.

Cheers,

Terry

 

[redbaron06]

So it works! But I ran into a final problem, I am trying to get the macro to clear the cells and then write the data into cells and I am not sure how to tell Excel to ClearCell FIRST and then DOGETDATA SECOND and then stop. Any suggestions?

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("B42").Value <> OldVal Then
OldVal = Range("B42").Value
Call ClearCell
End If
If Range("B43").Value <> OldVal Then
OldVal = Range("B43").Value
Call ClearCell2
End If
If Range("B42").Value <> OldVal Then
OldVal = Range("B42").Value
Call DoGetData
End If
If Range("B43").Value <> OldVal Then
OldVal = Range("B43").Value
Call DoGetData2
End If
End Sub


Thanks,
Terry

 

[VoG]

Perhaps

Private Sub Worksheet_Calculate()
Static OldVal1 As Variant, OldVal2 As Variant
If Range("B42").Value <> OldVal1 Then
    OldVal1 = Range("B42").Value
    Call ClearCell
    Call DoGetData
End If
If Range("B43").Value <> OldVal2 Then
    OldVal2 = Range("B43").Value
    Call ClearCell2
    Call DoGetData2
End If
End Sub

 

[redbaron06]

Thanks for the suggestions, but I have tried that as well, and it only runs Call ClearCell and errors out after that.

I was thinking maybe a delay on Call DoGetData? I have no idea how to do that.

 

[VoG]

What error and on which line of code?

 

[redbaron06]

No, just the "The source file or source range is invalid" built into GoGetData.

Okay so I thought about it more, do you think it would be possible to build the command:

Range("A45:N300").Clear

into the worksheet code (Below) to eliminate the use of a seperate call / module? It would then still be subject to the If/Then statement, but I would not know where to place the line in the code.

Sub DoGetData()
Dim strFile As String
Dim strRange As String
strFile = Range("B42").Value
GetDataFromClosedWorkbook strFile, "A8:N200", Range("A45"), True
End Sub
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub