Interspersed...
John said:
Dave,
I understand about posting a request for a particular person. Point taken!
I still need some help. I think I have a two fold problem. This macro
works, but what I need is to have a way that the user can type a name in the
A1 cell. THIS needs to be a name that is not a filename:
(all files are saved this way: "t1059(101904),
t1060(101904), continuing on until tommorrow, t1124(102004).)
This is my complete path:
C\My Documents\Recycling Solutions Weight Form\Inbound Scrap Form\October
2004\101904
Tommorrow the path for the current's day's files will be the same as above
except it will read "102004".
Now to my problem. On my template the user selects a customer's name from a
combobox.
Problem 1: Can there be a way that when the user selects the customer's
name from the combox that the selected name will also appear in cell G6 on
sheet 1?
If you used a combobox from the control toolbox toolbar, you can just use the
linked cell.
If you used a dropdown from the Forms toolbar, you could use the linked cell and
a formula:
with A1 as the linked cell and B1:b10 the list.
=if(a1="","",index(b1:b10,a1))
Problem2: If we can get the selected name to appear on cell G6 on sheet 1,
can we then have a macro (like the one you submitted) so that in cell A1 of
sheet 1 the user could type the name of the customer and it look through all
of the closed files preferrebly in ALL folders in the following path:
C\My Documents\Recycling Solutions Weight Form\Inbound Scrap Form\
For example the customer "Thermo King"
So you type in a value that's in G6 of Sheet1 that maybe in a workbook in that
folder?
You could use the .filesearch to get that list with a change to the code
..Filename = "*" & myCell.Value & "*.xls"
becomes:
..Filename = ".xls"
After you have the list, you have at least two choices.
Open each workbook and examine the value in that cell.
Or use a you can use a techniques at John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt
If I could go to cell A1 (on your new macro sheet) and type in Thermo King
it would be able to look at all of the closed files and then provide me with
a list of every file that contains Thermo King on cell G6 Sheet 1 of all of
my individual files.
Maybe something like:
Option Explicit
Sub testme02()
Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim myCell As Range
Dim rptWks As Worksheet
Dim oRow As Long
Set myCell = ActiveSheet.Range("a1")
If myCell.Value = "" Then
MsgBox "nothing in A1"
Exit Sub
End If
'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop
oRow = 1
If fCtr > 0 Then
Set rptWks = Worksheets.Add
For fCtr = LBound(myFiles) To UBound(myFiles)
If LCase(GetValue(myPath, myFiles(fCtr), "sheet1", "G6")) _
= LCase(myCell.Value) Then
rptWks.Cells(oRow, "A").Value = myPath & myFiles(fCtr)
oRow = oRow + 1
End If
Next fCtr
End If
End Sub
Private Function GetValue(path, file, sheet, range_ref)
'from John Walkenbach's site:
'
http://j-walk.com/ss/excel/eee/eee009.txt
'Look for either: GetDataFromClosedFile or GetValue.
'Retrieves a value from a closed workbook
Dim arg As String
'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
'Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
'Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
