Solved: Fetching data from excel

[arrun]

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,

[SamT]

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.

[Aussiebear]

have a look here and see if this helps.

http://www.erlandsendata.no/english/...php?t=envbadac

[Bob Phillips]

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]