Import data from closed Workbook (Dynamic range)

[marreco]

How import data from workbook, but get dynamic data?

Option Explicit
'you can extract data from a closed file by using an
'XLM macro. Credit for this technique goes to John
'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm
Sub ImportarAVAR()
   
    Dim FilePath$, Row&, Column&, Address$
    Dim LastRw As Long
    'change constants & FilePath below to suit
    '***************************************
    Const FileName$ = "BD_Geral_AvaES.xlsx"
    Const SheetName$ = "BD_AVAR"
    Const NumRows& = 100 '->> I need dinamyc range <<-
    Const NumColumns& = 16
    FilePath = ActiveWorkbook.Path & "\"
    '***************************************
   
    'LastRw = how get lastrow from my close workbook?
   
    DoEvents
    Application.ScreenUpdating = False
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "O arquivo " & FileName & " não foi encontrado", , "Arquivo não existe"
        Exit Sub
    End If
    For Row = 1 To LastRw '<<- how do it?
        For Column = 1 To NumColumns
            Address = Cells(Row, Column).Address
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next Column
    Next Row
    ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
    Range(Address).Range("A1").Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data)
End Function

 

[Nebu]

Hi:
Just by providing a code you got from a website won't give you solutions for your problem. Would you care uploading a sample workbook. Instead of looping through the rows you can use advanced filter or SQL statements to fetch the data in chunks, but again you should explain how you want to pull data and on what basis.

Thanks

 

[marreco]

I only need use dynamic range instead of (Const NumRows& = 100 ) static number of rows

 

[Nebu]

Hi:

On what basis you want to make a range dynamic?

Thanks

 

[marreco]

My closed workbook receive data each day.
Today has 100 rows, tomorrow 150 rows ..and so on...

I try it, but doesn't work..

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    For Row = 1 To LastRw 'NumRows
        For Column = 1 To NumColumns
            Address = Cells(Row, Column).Address
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next Column
    Next Row

 

[Deepak]

@ marreco

I think u just want to copy all data from closed workbook which got updated daily then use something like as below.

With VBA

Screen update = false
open wb
copy - paste data
close wb

OR use SQL with VBA like already suggested!

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

 

[marreco]

look
Wkb Orig is closed (“BD_Geral_AvaES.xlsx”) sheet(“BD_AVAR”) head columns ( “A”)

Wkb Dest is active workbook (“BD_Geral_AvaES.xlsx”) sheet (“Ctr_AVAR_Geral”)head columns ( “A”)

I need copy all data in Wkb Orig to Wkb Dest, but Wkb Orig every day receives data (range("A2100") anothe day range("A2180") and range("A2280"))

In you r code has .ListObjects("Table1"). i not has table in my case.

 

[Marc L]

Hi,

see in VBA help CurrenRegion, UsedRange, …

​

 

[Nebu]

Hi:
From what you have posted it is very simple to achieve.
This is code snippet which allows you to pull data from a closed workbook using SQL

Sub Pulldata()
Application.ScreenUpdating = False
Dim Str As String
Dim rs As ADODB.Recordset
Dim qry As String
Dim fName As String
Dim i As Long
i = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Your Range.ClearContents
fName = ThisWorkbook.Path & "\Your file from where you want to pull data from.xlsx"

    Str = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & fName & ";" & _
              "Extended Properties=Excel 12.0"
    qry = "SELECT * FROM [Your Sheet Name$] "
Set rs = New ADODB.Recordset
rs.Open qry, Str, adOpenUnspecified, adLockUnspecified
Your range where you want to paste data to.CopyFromRecordset rs
Application.ScreenUpdating = True

End Sub

Note Activate Microsoft ActiveX Data Objects library before using the code.

Thanks

 

[marreco]

Hi nebu, very nice!

Work very well.

Thank you very much!!