VBA to get the data from Different Workbook.

[pedie]

Hi, I'm trying to get the data in excel workbook book1 from book2 without having to open and get data but to retrive it using below sort of command.

Please help me how to achieve this.

Thanks for helping.

[/FONT]
[FONT=Courier New]Option Explicit
Sub TRY()
Range("B2").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Book1.xlsm;Mode=Share Deny Wri" _
        , _
        "te;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
        , _
        "DB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet" _
        , _
        " OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Loc" _
        , _
        "ale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        ), Destination:=Range("$B$2")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet2$")
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\Book1.xlsm"
        .ListObject.DisplayName = "Table_Book1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[pedie]

bump