VBA Lookup from closed workbook

kerstine03 · Mar 20th 2019

Hi All!

Would like to ask for help on what I'm currently working.

Sales_Conso.xlsm is my consolidating workbook. Range D8 to last column, contains the file names of the workbook where my table array will be.
What I wanted to achieve is:
If range D11 to last row is blank, it will open the workbook specified based on the file name on D8 and will apply the vlookup formula.
Lookup_Value = Sales_Conso.xlsm range B8 to last row
Table_Array = Filename on D8 to last column, range B7 to last cell with value
Col_index_num = 10

I've attached the sample files for everyone's reference.

Hope someone could help.
January 2019.xlsm.xlsx Sales_Conso.xlsx

Maqbool · Mar 21st 2019

Hi

Try the following. I have noticed an issues with your file extension (why you do have January 2010.xlsm.xlsx) . This needs to be corrected. Or change the file names on row 8.

Code

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String


Set wbSls = ThisWorkbook
strpath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"


With wbSls
    Set wsSls = .Sheets("Sales")
End With


With wsSls
    x = .Range("D8").End(xlToRight).Column
    
    For x = 4 To x
        strfile = .Cells(8, x)
        strlkparray = strpath & strfile & "]" & strSht
        Debug.Print strlkparray
        .Cells(11, x).Value = "=VLOOKUP($B11," & strlkparray
    Next
End With


End Sub

Display More

Regards

Maqbool

kerstine03 · Mar 21st 2019

Hi Maqbool!

The code works! Thank you very much.

However, its only applying on row 11. Can I also apply the same on the rows below?

Appreciate your help on this.

Maqbool · Mar 22nd 2019

Hi

Change the code as below:

Code

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim rngFmula As Range


Set wbSls = ThisWorkbook
strpath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"


With wbSls
    
    Set wsSls = .Sheets("Sales")
  
End With
n = 11


With wsSls
    Do Until n = lstRw
        x = .Range("D8").End(xlToRight).Column
        lstRw = .Rows.Count
        lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
   
        For x = 4 To x
            strfile = .Cells(8, x)
            strlkparray = strpath & strfile & "]" & strSht
           .Cells(n, x).Value = "=VLOOKUP($B11," & strlkparray
            
        Next
        n = n + 1
    Loop
End With


End Sub

Display More

Regards

Maqbool

kerstine03 · Mar 22nd 2019

Hi Maqbool!

The code is now working but I noticed the following:

strSht = "Master Data'!$B$7:$K$33,10,FALSE)" - is there a way that $B$7:$K$33 be dynamic? Like $B7 to lastrow?
.Cells(n, x).Value = "=VLOOKUP($B11," & strlkparray - would also need $B11 to be dynamic. The lookup_array should be like B11, B12, B13.. and so on Thank you very much for your help.

Maqbool · Mar 24th 2019

Hi

Code below will solve both issues:

Please leave blank row 1 on consolidation workbook.

Code

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim strLkpArra As String




Set wbSls = ThisWorkbook
strPath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!R7C2:R"


With wbSls
    Set wsSls = .Sheets("Sales")
End With


n = 11


With wsSls
    Do Until n = lstRw
        lstRwInClsd = .Range("D4").Value + 6
        x = .Range("D8").End(xlToRight).Column
        lstRw = .Rows.Count
        lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
   
        For x = 4 To x
            strfile = .Cells(8, x)
            strLkpArray = strPath & strfile & "]" & strSht
            'levae row 1 empty on consolidated workbook - this helper row to get the last row in closed workbook
            .Cells(1, x).Value = "=COUNTA('" & strPath & strfile & "]Master Data'!$B$8:$B$10000)"
            lstRwInClsd = .Cells(1, x).Value + 6 '(6 rows blank on the monthly workbook - may need to adjust accordingly)
            .Cells(n, x).Value = "=VLOOKUP(RC2," & "'" & strLkpArray & lstRwInClsd & "C11,10,FALSE)"
            .Cells(1, x).Value = ""
        Next
        n = n + 1
    Loop
End With


End Sub

Display More

Regards

Maqbool

VBA Lookup from closed workbook

Participate now!

Share