dave5m
asked on
Matching a cell and column two different workbooks using macros
I have a workbook named Indy.xls, another one named Book1.xls, and another one named Customer.xls. They are all saved in a folder marked C:\Users\Owner\Desktop\Rea
Sub Matcher3()
ChDir "C:\Users\Owner\Desktop\Re
Workbooks.Open Filename:="C:\Users\Owner\
If WorksheetFunction.CountIf(
Workbooks("Customer.xls").
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK
You can use the ExecuteExcel4Macro method of the Application object to return a result from a closed workbook. For this you need to know
1. The path of the destination workbook (eg "C:\Users\Owner\Desktop\Re al Indy\")
2. The name of the destination workbook (eg "Customer.xls")
3. The sheet name of the destination workbook (eg "Customer")
4. The range you want to target (in R1C1 style notation)
Converting the formula I originally gave you get this:
Dim varTemp As Variant 'variable to hold result
varTemp = Application.ExecuteExcel4M acro("VLOO KUP(" & Range("A1").Value & ",'C:\Users\Owner\Desktop\ Real Indy\[Customer.xls]Custome r'!C2:C3,2 ,0)")
If TypeName(varTemp) <> "Error" Then
Range("B1") = varTemp
Else
Range("B1").Value = Range("A1").Value
End If
Make sense?
Richard
You can use the ExecuteExcel4Macro method of the Application object to return a result from a closed workbook. For this you need to know
1. The path of the destination workbook (eg "C:\Users\Owner\Desktop\Re
2. The name of the destination workbook (eg "Customer.xls")
3. The sheet name of the destination workbook (eg "Customer")
4. The range you want to target (in R1C1 style notation)
Converting the formula I originally gave you get this:
Dim varTemp As Variant 'variable to hold result
varTemp = Application.ExecuteExcel4M
If TypeName(varTemp) <> "Error" Then
Range("B1") = varTemp
Else
Range("B1").Value = Range("A1").Value
End If
Make sense?
Richard
ASKER
Huh? i tried to look up ExecuteExcel4Macro method this weekend but i think i am more lost than when i started?
ASKER
dont sweat it- i took your formula and changed it to =IF(ISNA(MATCH(A2,'C:\User s\Owner\De sktop\Real Indy\[Customer.xls]Custome r'!$B:$B,0 )),"NA",VL OOKUP(A2,' C:\Users\O wner\Deskt op\Real Indy\[Customer.xls]Custome r'!$B:$C,2 ,0))
in cell a3 oif book 1 and thgan deleted the indy folder and than wrote this macro and it works pretty good-i would love to learn the ExecuteExcel4Macro method (and when you look at my macro you might think i'm retarded) but right now i am glad i got this to work. thank you very much!!
in cell a3 oif book 1 and thgan deleted the indy folder and than wrote this macro and it works pretty good-i would love to learn the ExecuteExcel4Macro method (and when you look at my macro you might think i'm retarded) but right now i am glad i got this to work. thank you very much!!
ASKER
i'm sorry here's the new macro if your interested:
Sub InvoicetoIndy()
'
' InvoicetoIndy Macro
'
'
Workbooks.Open Filename:="C:\Users\Owner\ Desktop\Re al Indy\Book1.xlsx"
With Selection.Characters(Start :=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Sheets("Sheet1").Select
Range("a1").Select
ActiveCell.FormulaR1C1 = "PLATE NUMBER"
Range("A2").Select
ActiveCell.FormulaR1C1 = InputBox("PLATE NUMBER")
Range("A3").Select
Range("A4").Select
End With
If Sheets("Sheet1").Range("A3 ").Value = "NA" Then
With Selection.Characters(Start :=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Sheets("Sheet1").Select
Range("a4").Select
ActiveCell.FormulaR1C1 = "YEAR"
Range("A5").Select
ActiveCell.FormulaR1C1 = InputBox("YEAR")
Do Until ActiveCell > 1960
If Range("a5").Select < 1960 Then MsgBox ("iNVALID YEAR ENTER CORRECT yEAR") Else
ActiveCell.FormulaR1C1 = "YEAR"
Range("A5").Select
ActiveCell.FormulaR1C1 = InputBox("YEAR")
Loop
Range("A6").Select
Range("A7").Select
ActiveCell.FormulaR1C1 = "MAKE"
Range("A8").Select
ActiveCell.FormulaR1C1 = InputBox("MAKE")
Range("A9").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = "MODEL"
Range("A11").Select
ActiveCell.FormulaR1C1 = InputBox("MODEL")
Range("A12").Select
Range("A13").Select
ActiveCell.FormulaR1C1 = "ENGINE SIZE"
Range("A14").Select
ActiveCell.FormulaR1C1 = InputBox("ENGINE SIZE")
Range("A15").Select
End With
End If
End Sub
Sub InvoicetoIndy()
'
' InvoicetoIndy Macro
'
'
Workbooks.Open Filename:="C:\Users\Owner\
With Selection.Characters(Start
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Sheets("Sheet1").Select
Range("a1").Select
ActiveCell.FormulaR1C1 = "PLATE NUMBER"
Range("A2").Select
ActiveCell.FormulaR1C1 = InputBox("PLATE NUMBER")
Range("A3").Select
Range("A4").Select
End With
If Sheets("Sheet1").Range("A3
With Selection.Characters(Start
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Sheets("Sheet1").Select
Range("a4").Select
ActiveCell.FormulaR1C1 = "YEAR"
Range("A5").Select
ActiveCell.FormulaR1C1 = InputBox("YEAR")
Do Until ActiveCell > 1960
If Range("a5").Select < 1960 Then MsgBox ("iNVALID YEAR ENTER CORRECT yEAR") Else
ActiveCell.FormulaR1C1 = "YEAR"
Range("A5").Select
ActiveCell.FormulaR1C1 = InputBox("YEAR")
Loop
Range("A6").Select
Range("A7").Select
ActiveCell.FormulaR1C1 = "MAKE"
Range("A8").Select
ActiveCell.FormulaR1C1 = InputBox("MAKE")
Range("A9").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = "MODEL"
Range("A11").Select
ActiveCell.FormulaR1C1 = InputBox("MODEL")
Range("A12").Select
Range("A13").Select
ActiveCell.FormulaR1C1 = "ENGINE SIZE"
Range("A14").Select
ActiveCell.FormulaR1C1 = InputBox("ENGINE SIZE")
Range("A15").Select
End With
End If
End Sub
ASKER
i wrote two more threads i dont know if you can read them but my main problem was that i am very new to this and have no clue, so when you wrote the last comment with ExecuteExcel4Macro i was lost. i have been working on this for 2 months so when you gave me the formula i knew i was close but both answers were not exact so i did not grade an a but within a week of constantly tweeking i got what i needed (which is soo much faster than 2 months). i am very appreciative though. thgank you.
ASKER
Sub Invoice_Click()
'
' Invoice_Click Macro
' Macro recorded 3/18/2009 by
'
'
ActiveSheet.Shapes("Button
Selection.Characters.Text = "Invoice"
With Selection.Characters(Start
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Sheets("Invoice").Select
Range("a1").Select
ActiveCell.FormulaR1C1 = "PLATE NUMBER"
Range("A2").Select
ActiveCell.FormulaR1C1 = InputBox("PLATE NUMBER")
Range("A3").Select
Range("A4").Select
++++++++++++++++++++++++++
Sheets("Invoice").Select
ActiveCell.FormulaR1C1 = "YEAR"
Range("A5").Select
ActiveCell.FormulaR1C1 = InputBox("Make")
Range("A6").Select
Range("A7").Select
where i put the "+" signs, i was hoping to enter the origiginal macro i wanted. i have modified the formula above to enter "NA" if a match hasn't been found instead of the unfound plate Number and to take the input from workbook "Indy" cell a2 and enter it in cell a1 workbook "Book1". so using the macro and formula above, what i want to do is to eneter a plate number. if the plate number is found to have a match with Workbook "Customer" column b to enter the info from "Customer" column c to cell A1 book1 and to open Book1 and end the macro. which i can do. But if there is no match (which i set up to say "NA") i want it to enter the input data from InputBox("PLATE NUMBER") in cell A1 Book1 and than got to next line of macro and enter the input data from InputBox("Make") in cell A2 Book1, and than run a match from about 30 folders saved in folder ChDir "C:\Users\Owner\Desktop\Ma
Any help is tremendously appreciated.