Consulting

Results 1 to 4 of 4

Thread: Solved: Fetching data from excel

  1. #1

    Solved: Fetching data from excel

    hello all,

    Let say I have one excel file at my D: drive naming 'Book'

    Now I want to fetch data from it using following code (basically I am trying to fetch data through some Commection).......

    [VBA]
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    '
    Workbooks("Book1").Connections.AddFromFile "D:\Book.xls"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\Book.xls;Mode=Share Deny Write;Extended Properties=""H" _
    , _
    "DR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet OLE" _
    , _
    "DBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passw" _
    , _
    "ord="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet " _
    , _
    "OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Va" _
    , "lidation=False"), Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("Sheet1$")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceConnectionFile = _
    "D:\Documents and Settings\703108054\My Documents\My Data Sources\Book Sheet1$.odc"
    .SourceDataFile = "D:\Book.xls"
    .ListObject.DisplayName = "Table_Book_Sheet1"
    .Refresh BackgroundQuery:=False
    End With
    End Sub
    [/VBA]

    Now if I run this macro, a pop-up box naming 'Select Table' opens. From that box I need to select the Sheet1 (data is in that sheet).

    My goal is to stop popping up that box, and fetching the data completely programatically without any user intervention.

    Is it possible to modify above code so that I can achieve this?

    Thanks and regards,

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    I am just not familiar with using database methods, but have you tried anything like
    [vba]
    Source=[D:\Book.xls]!Sheets("Sheet1).Range("A1:AB65535")[/vba]

    I'm just throwing that out, I hope,but don't know, if it will help.

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,862
    Location
    have a look here and see if this helps.

    http://www.erlandsendata.no/english/...php?t=envbadac
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    This reads your data into an array

    [VBA]Public Sub GetData()
    Dim oConn As Object 'ADODB.Connection
    Dim oRS As Object 'ADODB.Recordset
    Dim sFilename As String
    Dim sConnect As String
    Dim sSQL As String
    Dim ary As Variant

    sFilename = "D:\Book.xls"
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFilename & ";" & _
    "Extended Properties=Excel 8.0;"

    Set oRS = CreateObject("ADODB.Recordset")

    sSQL = "SELECT * FROM [Sheet1$]"
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
    adLockReadOnly, adCmdText

    If Not oRS.EOF Then

    ary = oRS.getrows
    Else

    MsgBox "No records returned.", vbCritical
    End If

    oRS.Close
    Set oRS = Nothing
    Set oConn = Nothing
    End Sub[/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •