Public Function Macro3000()
'
' Macro3 Macro

ThisWorkbook.Worksheets("results").Select

' The for loop is used to read the records in the excel file
There are
'there items in excel file so thats why i have set the array siz
to be three

Dim a(3) As Variant
Dim i As Integer
Dim j As Integer
i = 2

For j = 0 To 2
a(j) = ThisWorkbook.Worksheets("Sheet1").Cells(i, 1)
i = i + 1


' This is the query that takes those three information jus
now and search for
' the complete information in the access database


With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents an
Settings\samajits\Desktop\db1.mdb;DefaultDir=C:\Do cuments an
Settings\samajits\Des" _
), Array("ktop;DriverId=25;FIL=M
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))


.CommandText = Array( _
"SELECT test1.MFR_CASE_ID, test1.Case_ID, test1.`Datashee
Name`, test1.`URL Link to Datasheet`, test1.`Body Material`
test1.Position, test1.`Package Outline`, test1.`Lead Type`
test1.Pitch, test1.`J" _
, _
"EDEC Name`, test1.`Pin shape`, test1.`Serial Number`
test1.Subtype, test1.`Pin mount`, test1.`Pin Count`, test1.Weight
test1.`Weight Units`, test1.Units, test1.D, test1.`D + Tol`, test1.`D
Tol`, t" _
, _
"est1.E, test1.`E + Tol`, test1.`E - Tol`, test1.A
test1.`A + Tol`, test1.`A - Tol`, test1.L, test1.`L + Tol`, test1.`L
Tol`, test1.`Supplier QA Name`, test1.`QA Date`, test2.`MPN`
test2.`Case_ID`" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents an
Settin" _
, _
"gs\samajits\Desktop\db1`.test1 test1, `C:\Documents an
Settings\samajits\Desktop\db1`.test2 test2" & Chr(13) & "" & Chr(10)
"WHERE test2.Case_ID=test1.Case_ID AND test2.MPN IN ('" + a(j) + "')
_
)


' I think this part i giving problem. The refresh styl
xlinsertentirerows is
' causing the data to be printed in 1 row.


.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With

Next

End Functio

--
ajitpalsingh20
-----------------------------------------------------------------------
ajitpalsingh200's Profile: http://www.excelforum.com/member.php...fo&userid=1615
View this thread: http://www.excelforum.com/showthread.php?threadid=27584

Does your select query return only 1 row at a time or 3 rows at a time
If it returns 3 rows at a time then they would go in 1 excel row.

And in which row in excel is the record going.

- Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=27584