MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Activate the other file depending on the input value from the input button


Posted by pessona on July 25, 2000 8:35 PM

HI,
I think my problem here is a bit complicated. I have a sheet in a workbook where in that sheet I have created an input box function to ask for the user to enter the value they want. Then I need to check the value entered by the user with a value in one particular column in every workbook that is in the same directory.
For example,
The user has entered the value of 5,
Then i need to take this value and browse through in let say cell C3 in every files to search which cell contains the value of 5. Once found I need to activate the file to be used with the current workbook that is opened.
Hope there will be somebody that can help me.
Many thanks in advance!

-pessona-


Posted by Ivan Moala on July 27, 0100 2:09 AM

Here is a routine that will search in a user selected directory for all files (.xls) in sheet1
cell C1 (change to suit).
It will compare against your value = MyValue
variable.
Note: it stores the results in 1 cell = C1.
You will have to adapt to suit.


Option Explicit
Public i As Integer
Public Drive As String
Public Ans As Integer
Public FFiles As Integer
Public WB As Integer
Public FileNm() As String
Public K As Integer
Public temp As String
Public MyValue

Public Type BROWSEINFO
hOwner As Long
pid1Root As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Declare Function SHGetPathFromIdList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pid1 As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function GetDirectory(Optional msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

bInfo.pid1Root = 0&

If IsMissing(msg) Then
bInfo.lpszTitle = "Select a folder"
Else
bInfo.lpszTitle = msg
End If

bInfo.ulFlags = &H1

x = SHBrowseForFolder(bInfo)

path = Space$(512)
r = SHGetPathFromIdList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function


Sub OpenFile_NumberInput()

'Define the value to compare here!
MyValue = 14
Start1:
'----------------------------------------------------------------------------
Drive = GetDirectory("Select the directory of the files to look@")
If Drive = "" Then End
Ans = MsgBox(Drive, vbYesNo, "Load @ files in this Directory?")
If Ans = vbNo Then GoTo Start1
'-----------------------------------------------------------------------------

With Application.FileSearch
.NewSearch
.LookIn = Drive
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
ReDim FileNm(.FoundFiles.Count)
For i = 1 To .FoundFiles.Count
K = 0
FileNm(i) = .FoundFiles(i)
temp = ""
Do Until temp = "\"
temp = Mid(.FoundFiles(i), Len(.FoundFiles(i)) - K, 1)
K = K + 1
Loop
FileNm(i) = Right(.FoundFiles(i), K - 1)
Next
End If
If .FoundFiles.Count = 0 Then MsgBox "No files in " & Drive: End
End With

'In case sheet doesn't exist
On Error GoTo ErrH
Application.ScreenUpdating = False
Application.EnableEvents = False
'Reset original val
Range("C1").FormulaR1C1 = ""
For WB = 1 To UBound(FileNm())
Range("C1").FormulaR1C1 = "='" & Drive & "\[" & FileNm(WB) & "]" & "Sheet1'!R1C3"
Range("C1").Calculate
'In case of Type mismatch
On Error Resume Next
If Range("C1") = MyValue Then
Workbooks.Open Drive & "\" & FileNm(WB)
End If
On Error GoTo 0
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "Completed! looking @ " & WB - 1 & " Workbooks"

Exit Sub
ErrH:
If Err.Number <> 1004 Then
MsgBox "Error#:=" & Err.Number & " :=" & Err.Description
Else
'Sheet1 doesn't exist
Resume Next
End If

End Sub


HTH

Ivan

Posted by Ivan Moala on July 26, 0100 4:13 AM


You can do this without openning the files.
BUT do you want to search everysheet @ the same
location ???
The answer is a little complicated TOO.


Ivan

Posted by pessona on July 26, 0100 5:31 PM


Hi Ivan,
Thanks for your response.. Yes, I need to search for every workbook(.xls files) at the same directory. The cell would be the same cell for every workbook(for example workbook1 sheet1 C2 and workbook2 sheet1 C2).
Thanks!