Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with types...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with types...
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 ToolsOptionsEditor 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 Dave wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error bar types | Charts and Charting in Excel | |||
How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Types in Add-In | Excel Programming | |||
Variable Types | Excel Programming |