Trouble with types...

[Dave]

Im trying to write a macro to pull data out of another workbook, and have
written a function GetValue that does this, but since im relatively new to
vba I keep getting type mismatches with whatever values i put into it. Thanks
in advance.

Sub Update()
Sheets(ActiveSheet).Visible = True
pathToFolder= Sheets("Combined Results").Range("C3")
file = ActiveSheet.Range(ActiveCell)
sheet = "CQR"

Application.ScreenUpdating = False
Sheets("Call Quality Results").Visible = True
reference = Cells(C, 21).Address
ActiveCell.Offset(0, 1) = GetValue(pathToFolder, file, sheet, reference)
Application.ScreenUpdating = True

End Sub

Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function

 

[Rowan Drummond]

Hi Dave

Your first type mismatch error is coming from the line
Sheets(ActiveSheet).Visible = True
This should read
Activesheet.Visible = True
although to be honest I am not sure how it came to be active if it is
not visible.

In the Visual Basic Editor click on Tools>Options>Editor and check
Require Variable Decleration. This will put the statement "Option
Explicit" at the top of any new module created which in turn will force
you to declare any variable before you can use it. See VBA help on
variable declarations.

Finally see Ron de Bruin's method for copying a value from a closed
workbook at:
http://www.rondebruin.nl/ado.htm

Hope this helps
Rowan