Macro to open another excel file

[MrKremrik]

I need to write a macro that opens another workbook when run and vlookups between two columns of data for duplicates. I have no problems with vlookups, but I don't know how to have a macro open another excel file... What would be even better is if I could have the workbook running the macro look at the information in the other workbook WITHOUT opening it up. If possible. So I'd like to be able to hit the macro, have it peer inside another file, and vlookup to find out which rows are the same.
Thanks for the help!!

 

[dmt32]

I need to write a macro that opens another workbook when run and vlookups between two columns of data for duplicates. I have no problems with vlookups, but I don't know how to have a macro open another excel file... What would be even better is if I could have the workbook running the macro look at the information in the other workbook WITHOUT opening it up. If possible. So I'd like to be able to hit the macro, have it peer inside another file, and vlookup to find out which rows are the same.
Thanks for the help!!

You can find the answer to your first question in the VBA helpfile under opening a workbook.</SPAN>

Example</SPAN>


<CODE>Sub OpenUp()</SPAN></CODE></PRE>
<CODE> Workbooks.Open("C:\MyFolder\MyBook.xls")</SPAN></CODE></PRE>
<CODE>End Sub</SPAN></CODE></PRE>
</PRE>
<CODE>Your other request can be achieved using formulas – in a macro it would be something like this:</SPAN></CODE></PRE>
</PRE>
Sub GetData()</PRE>
Dim mydata As String</PRE>
'data location & range to copy</PRE>
mydata = "='C:\[Mybook.xls]Sheet1'!$A$1:$F$12" '<< change as required</PRE>
</PRE>
'link to worksheet</PRE>
With ThisWorkbook.Worksheets(1).Range("A1:F12") '<< change as required</PRE>
.Formula = mydata</PRE>
</PRE>
'convert formula to text</PRE>
</PRE>
.Value = .Value</PRE>
</PRE>
End With</PRE>
</PRE>
End Sub</PRE>
You can then apply your Vlookup to the returned data.</PRE>
Hope helpful</PRE>
</PRE>
Dave</PRE>

 

[VoG]

The easiest thing is to open the workbook. Extracting information from closed workbooks is a pain

Dim wb As Workbook
Set wb = Workbooks.Open("C\Test\test,xls")

 

[John Davis]

This may give you some ideas? It is not tested but only provided as a starting point.

Sub MrKremrik()

Dim ws As Worksheet
Dim wb As Workbook
Dim lr As Long

Set wb = ActiveWorkbook
Set ws = ActiveSheet

Workbooks.Open Filename:="D:\common\data\CHANGE YOUR PATH\CHANGE TO FILE.xls"

lr = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Sheet1").Activate

With Range("B2:B" & lr)

    .Formula = "=VLOOKUP(A2[wb]ws'$A$2:$B$1000,2,false)"
    .Value = .Value
    .Replace what:="#N/A", replacement:=""
    
End With


End Sub

 

[MrKremrik]

Thanks everyone! I figured out exactly what I was looking for!