Grab data from closed workbook

[YasserKhalil]

Hello everyone
I have an attachment with two files inside
One workbook named "Main" and in this workbook I need a macro that grab specific information from the other workbook named "Vacations"
There are the expected results in range("A3:AG7") which may be "S" letter or "D" Letter" depending on the other workbook named "Vacations"

In "Vacations" wb:
---------------------
In column A the ID number of each worker and it may be repeated.
In Column E this is the target column which should be grabbed.
In Columns F and G there is the start date and end date

Hope it is clear
Thanks advanced for offering help

 

[Deepak]

Hi,

I did something here to get data from closed wb.

http://forum.chandoo.org/threads/vba-code-to-format-the-report-and-copy-paste.19337/

let me know if it any changes/support is req from my side.

 

[YasserKhalil]

The main problem is not to open closed workbook and deal with it in fact
The problem is how grab the data according to dates (between start date and end date)
I have no idea how to deal with that point

 

[Deepak]

T..
The problem is how grab the data according to dates (between start date and end date)
I have no idea how to deal with that point

Oops... Something strange.

 

[Deepak]

Oops... Something strange.

I think it would be simple with VBA.

 

[YasserKhalil]

May be simple for you ..
But I'm just a learner and I couln't get it
I will be grateful if you help me with that thread

 

[Deepak]

Hi,

Check this!!

Put both wb in same folder.

Sub get_data()
Dim mywb As String, wb As Workbook, lookup_rng As Range
Dim rng As Range, myno As Integer
Dim main_sheet As Worksheet, date_rng As Range, myd As Integer

Application.ScreenUpdating = False

Set main_sheet = ThisWorkbook.ActiveSheet
   
    mywb = ThisWorkbook.Path & "\" & "Vacations.xlsx"
        Set wb = Workbooks.Open(mywb)
                Set lookup_rng = wb.ActiveSheet.Range("A7:A13")
               
For Each rng In lookup_rng
    With rng
        myno = Application.Match(.Value, main_sheet.Columns(1), 0)
            Set date_rng = main_sheet.Cells(myno, Day(.Offset(, 5).Value) + 3)
                myd = .Offset(, 3).Value
                    date_rng.Resize(, myd).Value = .Offset(, 4).Value
    End With
Next

wb.Close False
Application.ScreenUpdating = True
End Sub

 

[YasserKhalil]

Mr. Deepak
Thanks a lot for this great and wonderful help
It works like charm.. you are very helpful

You see that code is simple (It is not simple for me at all ..)
It may take me days and weeks to get it only
Thanks for help

 

[Deepak]

Mr. Deepak
Thanks a lot for this great and wonderful help
It works like charm.. you are very helpful

You see that code is simple (It is not simple for me at all ..)
It may take me days and weeks to get it only
Thanks for help

Let me know if something to assist.

 

[YasserKhalil]

Thank you very much for your kindness
I will tell you of course if I have any problem