Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
referencing external workbook using variables
i've restructured..
Note: instrREV is EXCEL2000+ you'll need the [] around the file excluding the preceding path you'll need single quotes for path/filenames with spaces. the formula can be set for the entire range. you'll get prompted (by excel) if sheet "Offene" doenst exists in file selected by user. cheerz! Option Explicit Private Function fileselect() 'get the current worksheet and workbook name '*******set the inputList path******* fileselect = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _ 1, "Select the input list", "select", False) End Function Private Sub main() Dim sFormula As String Dim sPath As String Dim iPos As Integer sPath = fileselect If sPath = "" Then Exit Sub iPos = InStrRev(sPath, "\") sPath = Mid(sPath, 1, iPos) & "[" & Mid(sPath, iPos + 1) & "]" sFormula = "=COUNTIF('" & sPath & "Offene'!C6,""be"")" On Error Resume Next ActiveSheet.Range("r6:aq6").FormulaR1C1 = sFormula End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool aherrera wrote: Hello all, I am working with two worksheets, and I need to use the formula COUNTIF referencing another workbook. Unfortunately the name of the second workbook changes so I have written a routine that asks the user to point to that other workbook and thus save the path and file name. However, when I try to enter the pathname as a variable in the formula, I get an error. I have the following code (the problem is at the end of info_processor() ) I appreciate any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (2003) crashes when referencing to external workbook in freezeframe | Excel Worksheet Functions | |||
Help! Need to lookup referencing 2 variables | Excel Worksheet Functions | |||
referencing a value from a chart with 3 variables | New Users to Excel | |||
Variables in links and external file references | Links and Linking in Excel | |||
Referencing Variables | Excel Worksheet Functions |