Macro to import data from other file

[Elliot]

Hi guys

I need a macro to import data from "data_base" file to book.xlsm file.
i explain as it should be on book.xlsm

Thanks for your support.

 

[Monty]

Hello Elliot.

Hope you are doing good.

Can i have more information to help you..am unable to unzip your files.

Thank you.

 

[Monty]

Hello Elliot.

Got the file opened now.

i have taken less data to test it and not from other workbook but within workbook only.

We have Summary sheet and Data sheet.

Click on Run button in Summary sheet which will fetch data from Data sheet to summary..if it works for you, then i can import data from one book to other..Please be advised this is only for test purpose taken only two dates, please find attached file.

Let me know any challenges..Happy to help you.

Let me know if i have understood your requirment..

 

[Elliot]

Hi Monty,
I really appreciate you support
It does not work, i got an error ( shot attached).
I want as i show on book.xlsm ,i put a date and after i click on GETDATA and the macro will import all data from "DATA_BASE.xlsx" to another worksheet on " book ", and the goal is fill vertically all cells DT1 DT2 DT3 with data on that specific date as i show on "Book".
thanks one more time

 

[Monty]

Elliot

if you put the data vertically problem would be, when data is more it will overlap other tables so better output should be in column wise as i shown.
let me work on your error.

Let me know if am wrong.

 

[Monty]

Elliot

Just run by clicking on "RUN" button without any changes, don't add your data.. wanted you to check if the output is correct.

Later we can change as per your requirment...I know it is working within sheet rather then going to another book and getting data.

New​

Elliot​

 

[Elliot]

i didnt add data, i ran your macro, but i still got an error as shown on attached

 

[Monty]

Strange but works perfectly on my system no issues.

 

[Elliot]

i use MO 2013:

 

[Monty]

Version should not be a problem am also using 2013.

 

[Elliot]

So i dont know whats happend. But when i click on "get data" got an error
and the datasheet becomes as shown on pic with a filter.

 

[Elliot]

My issue persists , anyway i would like to thank you Monty all the effort that you put in.

 

[Elliot]

i use MO 2013:

Hi Monty,

i found the issue, had to do with date format, now its work but if i put a different date that dont exist on data i got an error as i shown on pic

 

[Marc L]

Hi !

import all data from "DATA_BASE.xlsx" to another worksheet on "book"
and the goal is fill vertically all cells DT1 DT2 DT3
with data on that specific date as i show on "Book".

According to your original attachment
with vertical layout as expected (no overlap issue),
whatever if "DATA_BASE" workbook is closed or opened,
nothing to mod and no error in case of a non existing date :​

Private Sub CommandButton1_Click()
       Const DB = "DATA_BASE.xlsx"
         Dim Ws As Worksheet
    If Evaluate("ISREF('[" & DB & "]Details'!A1)") Then
         Set Ws = Workbooks(DB).Worksheets(1)
    Else
         If Dir(ThisWorkbook.Path & "\" & DB) = "" Then Beep: Exit Sub
         Set Ws = GetObject(ThisWorkbook.Path & "\" & DB).Worksheets(1)
             B% = 1
    End If
         Me.UsedRange.Columns("A:C").Offset(2).Clear
         Application.ScreenUpdating = False
    With Ws.UsedRange
            D$ = Format$([E2].Value, .Range("D2").NumberFormat)
        For N% = 1 To 3
            R& = Me.UsedRange.Rows.Count
            If N > 1 Then R = R + 4: [A1:C1].Copy Cells(R, 1): Cells(R, 1).Value = "DT" & N
            .Parent.AutoFilterMode = False
            .AutoFilter N + 3, D
            .Columns("A:C").Offset(1).Copy Cells(R + 1, 1)
        Next
    End With
         If B Then Ws.Parent.Close False Else Ws.AutoFilterMode = False
         Set Ws = Nothing
         Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Elliot]

Hi Marc L,

This is just what I was looking for,it works great
I really appreciate it , thank u very much