referencing external workbook using variables

[aherrera]

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.


Dim l As String
Dim pathname
Dim ThisSheet
Dim WKBookName


Public Sub main()

Call fileselect
Call info_processor

End Sub

Private Sub fileselect()

'get the current worksheet and workbook name
ThisSheet = ActiveWorkbook.ActiveSheet.Name
WKBookName = ThisWorkbook.Name

'*******set the inputList path*******
pathname = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
1, "Select the input list", "select", False)

End Sub


Private Sub info_processor()

Set r = Workbooks(WKBookName).Worksheets(ThisSheet).Range("r6:aq6")

For Each cell In r

cell.FormulaR1C1 = _
"=COUNTIF([" & pathname & "]Offene!C6,""be"")"

Next cell
End Sub

 

[keepitcool]

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 >