I have a macro that runs from a command button in an dummy excel file. My sub uses one Excel file to gather data while a function called by my sub uses another Excel file to compare date. I have created the variables separately (and with different names), but when I run the macro, my function doesn't use either of those files. It searches the dummy file. How do I get it to search the 3rd excel file (1=dummy file, 2=gather file, 3=compare file)
I tried a Worksheets ("name").Activate after MyPhone.Open, but that gave me a compile error saying "out of range." Then I tried to do a Workbook.Activate but that didn't work either.
Here is my function code, and the first part of my sub:
Function MLSLookUp(LPS As String)
Dim MyExc As Object, MyPhone As Object
Set MyExc = CreateObject("Excel.Application")
MyExc.Visible = False
Set MyPhone = MyExc.Workbooks
MyPhone.Open "c:\phone list.xls" 'not actual reference - only one worksheet
r = 2
Do
Cells(r, 2).Select
proc = Cells(r, 2).Value
If proc <> LPS Then
r = r + 1
ElseIf proc = LPS Then
MLSName = Cells(r, 1).Value
MLSEmail = Cells(r, 3).Value
Exit Do
End If
Loop Until r = 300
If Len(MLSEmail) = 0 Then
MsgBox "Processor " & LPS & " not found!"
End If
MyPhone.Close
End Function
-------------------------------------------------
Dim MyExcel As Object, MyWbks As Object
Set MyExcel = CreateObject("Excel.Application")
MyExcel.Visible = False
Set MyWbks = MyExcel.Workbooks
MyWbks.Open "c:\Pipeline.xls"
So you can see, my workbooks and worksheets have different variable names, but I just can't get them activated right. Please Help!
Two excel files referenced, not using correct one
Graham Harris
Function MLSLookUp(LPS As String)
Dim MyExc As Object, MyPhone As Object
Set MyExc = CreateObject("Excel.Application")
MyExc.Visible = False
Set MyPhone = MyExc.Workbooks
Set MyPhone = Workbooks.Open "c:\phone list.xls" 'not actual reference - only one worksheet, Your going to need the referance.
Also I would change MyPhone As Object to MyPhone As Workbook, not that it would stop your code.
Your next issue is that your code exits in the dummy file and operations on Cells are going to reference the dummy file as a sortcut of Me.Cells or Sheet1.Cells. You have to reach outside the current sheet by name.
r = 2
You may also find this much easier to do with all your different workbooks as sheets in a single workbook, but if you must maintain them as seperate files, then this would be the way to go.Do
MyPhone.Sheet1.Cells(r, 2).Select
proc = MyPhone.Sheet1.Cells(r, 2).Value
If proc <> LPS Then
r = r + 1
ElseIf proc = LPS Then
MLSName = MyPhone.Sheet1.Cells(r, 1).Value
MLSEmail = MyPhone.Sheet1.Cells(r, 3).Value
Exit Do
End If
Loop Until r = 300
If Len(MLSEmail) = 0 Then
MsgBox "Processor " & LPS & " not found!"
End If
MyPhone.Close
End Function
Randy
nziese
Thank you so much. That is exactly what I needed.
And, suprising myself, I figured out that instead of "Sheet1" I needed to put "Sheets("Sheet Name")." This was after much debugging, and actually starting this post with the question of "Why do I keep getting an error " As an extreme novice user of VBA, I'm pretty proud of myself for that.