Solved: Vlookup from data in unopened file?

[marshybid]

hi All,

I have a macro that xld very kindly helped me out with, works really well.

I have just been advised that certain values in the data need to be confirmed against a disparate data set prior to completing the macro.

I can write the code to identify which cells need to be confirmed, but what I would like to do is include a formula which is essentially;



=VLOOKUP(AX2,'data!'$B$2:$C$50,2,FALSE)


I don't want to have file 'data' open though, I would like to incorporate a method of accessing the range for the Vlookup without having to open the file??

Can anyone help?

Thanks,

Marshybid

[grichey]

I'm sure someone will step in with the official excel terminology but from my experience you can't do any of the more tricky formulas to any external workbooks without opening them. Last week I tried to do a countif to a closed workbook and couldn't get it to work unless it was open. I will be surprised if vba helps in this endeavor as it appears to be a basic limitation of excel. But what do I know!

[Hoopsah]

Kind of agree with Gavin here, however, you could incorporate into your macro oa basic open and close workbook statement. i.e.

Workbooks.Open Filename:= _
"C:\data.xls
ActiveWindow.Close

This would open the workbook and close it immedietly, giving your program the time it needs to update.

[marshybid]

Kind of agree with Gavin here, however, you could incorporate into your macro oa basic open and close workbook statement. i.e.

Workbooks.Open Filename:= _
"C:\data.xls
ActiveWindow.Close

This would open the workbook and close it immedietly, giving your program the time it needs to update.

OK, so I guess I would have the code above at the point in my macro where I want to run the Vlookup formula, run the formula then close the file at the end of the macro??

Thanks for this, I'll give it a go.

Marshybid :hi:

[marshybid]

Sorry, another question on this...

If I use



Workbooks.Open Filename:= _
"C:\data.xls
ActiveWindow.Close


Does this mean that the file will be opened, but that this file will not be considered the active workbook??

Also, I assume I need to add the following code at the end of my macro to close the file again



Workbooks.Open Filename:= _
"C:\data.xls


is that correct??

Thanks,

Marshybid

Thanks,

[mdmackillop]

You could add links to the closed book eg ='C:\AAA\[Book1.xls]Sheet1'!A1 either manually or by macro, and run VLOOKUP on the linked cells.

[marshybid]

You could add links to the closed book eg ='C:\AAA\[Book1.xls]Sheet1'!A1 either manually or by macro, and run VLOOKUP on the linked cells.

Hi MD,

So do you mean that I could possible do the following (not 100% sure on the syntax);




Dim DataFile As String

DataFile = "C:\MyDocs\data.xls"

rest of code

=VLOOKUP(A1,[DataFile]Sheet1!$B$2:$B$50,2,FALSE)


Is that what you maen by linking??

Thanks,

Marshybid

[mdmackillop]

Sub Macro1()
For i = 1 To 3
For k = 1 To 10
Cells(k, i).FormulaR1C1 = "='C:\MyDocs\[data.xls]Sheet1'!R" & k + 1 & "C" & i + 1
Next
Next
End Sub

[marshybid]

Sub Macro1()
For i = 1 To 3
For k = 1 To 10
Cells(k, i).FormulaR1C1 = "='C:\MyDocs\[data.xls]Sheet1'!R" & k + 1 & "C" & i + 1
Next
Next
End Sub


Hi MD,

Sincere apologies, but I don't understand the references to k & i in the code above. How does this enable me to include the unopened file in my Vlookup formula. Doh!!! :dunno

Thanks,

Marshybid

[grichey]

I believe he's saying if you're doing a look up in array A1:B20 of your closed wkbk, then in your open one (the one the code is for), just link a 2x20 range of cells somewhere else in your workbook to the closed workbook range then do the lookup on the linked range of your open wkbk.

Clear as mud?

You can always just do this in a far out row or column and hide it.

[mdmackillop]

Thanks Gavin, that is what I meant.

[marshybid]

I believe he's saying if you're doing a look up in array A1:B20 of your closed wkbk, then in your open one (the one the code is for), just link a 2x20 range of cells somewhere else in your workbook to the closed workbook range then do the lookup on the linked range of your open wkbk.

Clear as mud?

You can always just do this in a far out row or column and hide it.

The mist has cleared and the light can now been seen :rotlaugh:

OK, so now I understand what the code kindly provided by MD is doing.... Took a while for me to get it :whistle:

Based on that I have been able to include the following code in my macro;



Range("AX2").Select

For i = 1 To 2
For k = 2 To 125
Cells(k, i).FormulaR1C1 = "='C:\Documents and Settings\francri\My Documents\Motorola\Billibg Reports\Timesheet Consolidation 19 May 2008\[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i
Next
Next

Range("AW2").Select
ActiveCell.FormulaR1C1= "=VLOOKUP(""AV2"",""$AX$2:$AY$126"",2,FALSE)


This then gets copied down the column.

MD thanks so much for the code. :bow:
Gavin, thanks for giving me the idiot's guide to how it works. :bug:

Thread will be marked as solved.

As ever, this really is the best place for help, guidance and support.

Marshybid :hi:

[mdmackillop]

Please remember the linebreaks!

[marshybid]

Please remember the linebreaks!

Hi MD,

I tried linebreaks

[code]
Cells(k, i).FormulaR1C1 = "='C:\Documents and Settings\francri\
_
My Documents\Motorola\Billibg Reports\Timesheet Consolidation 19 May 2008\ _
[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i
Next
Next
{/code]

It just kept returning an error??

Range("AW2").Select
ActiveCell.FormulaR1C1= "=VLOOKUP(""AV2"",""$AX$2:$AY$126"",2,FALSE)

[mdmackillop]

For k = 2 To 125
Cells(k, i).FormulaR1C1 = "='C:\Documents and Settings\francri\" & _
"My Documents\Motorola\Billibg Reports\Timesheet Consolidation 19 May 2008\" & _
"[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i
Next

[marshybid]

Thanks MD,

I completely forgot about the &....

Slight issue, I have added the code as Private Sub AddVlookup ().

In my macro I get to a certain point then Call AddVLookup

However, now when I run this I get a 'Compile Error - Variable not defined' at For i = 1 to 2??

Any thoughts

Marshybid

[mdmackillop]

Have you Dimmed i?

[marshybid]

Have you Dimmed i?

Doh!!!!!

So, I should add the lines



Dim i As Range
Dim k As Range


??

Thanks,

Marshybid

[grichey]

dim i as characters
dim k as characters

[mdmackillop]

i and k should be Long

[grichey]

my bad -- was testing w/ A1 :-p

[marshybid]

i and k should be Long

Hi MD, Thanks for this. I had tried Dim As Long, but actually nothing seems to work at present. I'll explain, I have also attached a zip file with the 2 workbooks for reference (example.xls and Vlookup Data.xls)

I currently run the following code as the first step in the macro (code very kindly cleaned up and improved by xld recently):



Sub Timesheets1()
'
' Timesheets Macro
' Timesheet Filter 4/22/2008 by Richard Francis

Const FormulaColY As String = _
"=IF(RC[-1]=""<Null>"","" "",RC[-1]+CHOOSE(WEEKDAY(RC[-1]),0,6,5,4,3,2,1))"
Const FormulaColAs As String = _
"=IF(RC[-1]=""<Null>"","" "",RC[-1]+CHOOSE(WEEKDAY(RC[-1]),0,6,5,4,3,2,1))"
Const FormulaColAT As String = _
"=SUM(RC[-14]*RC[-31])+(RC[-13]*RC[-30])+(RC[-12]*RC[-29])"
Const FormulaColAV As String = _
"=IF(RIGHT(RC[-8],1)="")"",--(MID(RC[-8],LEN(RC[-8])-9,6)),--(LEFT(RC[-8],6)))"
Const FormulaColAW As String = _
"=IF(RIGHT(RC[-9],1)="")"",LEFT(RC[-9],5),RIGHT(RC[-9],5))"

Const FormatColAT As String = _
"_-[$?-809]* #,##0.00_-;-[$?-809]* #,##0.00_-;_-[$?-809]* ""-""??_-;_-@_-"


'Application.ScreenUpdating = False
'// Add required columns and formulas

ActiveSheet.Name = "Timesheet Details"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Order ID"
Range("A1:AR1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.AutoFilter
Columns("Y:Y").Insert Shift:=xlToRight
Range("Y1").Value = "Timesheet For Week Ending"
Range("Y2").FormulaR1C1 = FormulaColY
Range("Y2").Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
Columns("AS:AS").Insert Shift:=xlToRight
Range("AS1").Value = "Approved in Week Ending"
Range("AS2").Value = FormulaColAs
Range("AS2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("AT:AT").Insert Shift:=xlToRight
Range("AT1").Value = "Total Amount"
Range("AT2").FormulaR1C1 = FormulaColAT
Columns("AT:AT").NumberFormat = FormatColAT
Range("AT2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AV1").Value = "Legal Entity"
Range("AV2").FormulaR1C1 = FormulaColAV
Range("AV2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW1").Value = "CCentre"
Range("AW2").FormulaR1C1 = FormulaColAW
Range("AW2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AZ1").Select
Call AddVlookUp


When I call AddVlookUp, it is using the code that you kindly provided yesterday, and I have added some additional tasks following that:



Private Sub AddVlookUp()
Dim i As Long
Dim k As Long

For i = 1 To 3
For k = 1 To 125
Cells(k, i).FormulaR1C1 = "='C:\Documents and Settings\francri\" & _
"My Documents\Motorola\Billibg Reports\Timesheet Consolidation 19 May 2008\" & _
"[Confirmation Required Final.xls]Vlookup Data'!R" & k + 1 & "C" & i + 1
Next
Next

Range("AX1").Select
ActiveCell.FormulaR1C1 = "Maryellen"
Range("AX2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""B2"",""$AZ$2:$BA$126"",2,FALSE)"
Range("AX2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AY1").Select
ActiveCell.FormulaR1C1 = "Same"
Range("AY2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(AX2),"" "",IF(AX2="" "","" "",IF(AW2=AX2,"" "",""N"")))"
Range("AY2").Select
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


The issue is that when I run this it selects cell AZ1, but does not pull any of the VlookUp data through to populate the range, it then creates the formulas in the correct columns, but of-course there is no data for these to use?? I have been scratching my head for a while now.

Any guidance would be greatly appreciated.

Thanks in advance,

Marshybid :help

[mdmackillop]

On holiday with no Excel access. You'll have to drum up someone else to assist.

[marshybid]

On holiday with no Excel access. You'll have to drum up someone else to assist.

Enjoy and relax :rotlaugh:

I thinks I have found a way around the problem I was facing.

What I have done is add a sheet to my workbook called Vlookup.

Then I just amended your code as follows:



Private Sub AddVlookUp()

Dim i As Long
Dim k As Long
Sheets("Vlookup").Select
For i = 1 To 3
For k = 1 To 125
Cells(k, i).FormulaR1C1 = "='C:\Documents and Settings\francri\" & _
"My Documents\Motorola\Billibg Reports\Timesheet Consolidation 19 May 2008\" & _
"[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i

Next
Next
Sheets("Timesheet Details").Select
Range("AX1").Select
ActiveCell.FormulaR1C1 = "Maryellen"
Range("AX2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-48],Vlookup!R2C1:R125C2,2,FALSE)"
Range("AX2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AY1").Select
ActiveCell.FormulaR1C1 = "Same"
Range("AY2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(RC[-1]),"" "",IF(RC[-1]="" "","" "",IF(RC[-2]=RC[-1],"" "",""N"")))"
Range("AY2").Select
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub


This now works fine, it grabs the data from the unopened file for the vlookup, then I have added the 2 seperate fromulas that I need.

Thanks for all of your help.

Marshybid :hi:

[Bob Phillips]

You can simplify that code,and no Selects!



Private Sub AddVlookUp()
Const FILEPATH As String = _
"C:\Documents and Settings\francri\My Documents\Motorola\" & _
"Billibg Reports\Timesheet Consolidation 19 May 2008\"
Dim i As Long
Dim k As Long
Dim LastRow As Long

With Sheets("Vlookup")
For i = 1 To 3

For k = 1 To 125

.Cells(k, i).FormulaR1C1 = "='" & FILEPATH & _
"[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i
Next
Next
End With

With Sheets("Timesheet Details")

LastRow = .Range("AX2").Offset(0, -1).End(xlDown).Offset(0, 1).Row

.Range("AX1").Value = "Maryellen"
.Range("AX2").Resize(LastRow - 1).FormulaR1C1 = _
"=VLOOKUP(RC[-48],Vlookup!R2C1:R125C2,2,FALSE)"

.Range("AY1").Value = "Same"
.Range("AY2").Resize(LastRow - 1).FormulaR1C1 = _
"=IF(ISNA(RC[-1]),"" "",IF(RC[-1]="" "","" "",IF(RC[-2]=RC[-1],"" "",""N"")))"
End With
End Sub

[marshybid]

You can simplify that code,and no Selects!



Private Sub AddVlookUp()
Const FILEPATH As String = _
"C:\Documents and Settings\francri\My Documents\Motorola\" & _
"Billibg Reports\Timesheet Consolidation 19 May 2008\"
Dim i As Long
Dim k As Long
Dim LastRow As Long

With Sheets("Vlookup")
For i = 1 To 3

For k = 1 To 125

.Cells(k, i).FormulaR1C1 = "='" & FILEPATH & _
"[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i
Next
Next
End With

With Sheets("Timesheet Details")

LastRow = .Range("AX2").Offset(0, -1).End(xlDown).Offset(0, 1).Row

.Range("AX1").Value = "Maryellen"
.Range("AX2").Resize(LastRow - 1).FormulaR1C1 = _
"=VLOOKUP(RC[-48],Vlookup!R2C1:R125C2,2,FALSE)"

.Range("AY1").Value = "Same"
.Range("AY2").Resize(LastRow - 1).FormulaR1C1 = _
"=IF(ISNA(RC[-1]),"" "",IF(RC[-1]="" "","" "",IF(RC[-2]=RC[-1],"" "",""N"")))"
End With
End Sub


Thanks xld, I did try to do something similar myself, but it kept crashing out, so I shelved that for another day. You make it look way too easy :bow:

I have posted http://www.vbaexpress.com/forum/showthread.php?t=20208

Which is essentially an addition to the above. Once the '=IF(ISNA...' formula is run I am left with a number of rows that contain "N" in that column.

What I then need to do is use your filtering code to identify rows containing "N" in that column then change the value of the cell 2 to the left (RC[-2]) to equal the value of the cell 1 to the left (RC[-1])

As ever, though, I am struggling.

Thanks for the great advice.

Marshybid :doh: