Query's

[Guest]

Is it possible when getting external data in to Excel, to run multiple query's that load data on to individual worksheets of 1 workbook?

 

[smozgur]

You can use DAO to get data by SQL strings easily.

'This routine opens sample.xls file then uses the sqlstr string as SQL text then retrieves data by given criteria. You can create multiply queries with this way.

Sub GetData()
'Reference : DAO 3.6 object library
Dim dbmain As Database
Dim rcset As Recordset
Dim sqlstr As String
Dim i As Integer
Set dbmain = OpenDatabase("c:windowsdesktopsample.xls", False, False, "Excel 8.0;")
sqlstr = "SELECT * FROM [Jan2002$] WHERE Code<100000"
Set rcset = dbmain.OpenRecordset(sqlstr)
Do Until rcset.EOF
i = i + 1
ActiveSheet.Cells(i, 1).Value = rcset.Fields(1).Value
ActiveSheet.Cells(i, 2).Value = rcset.Fields(2).Value
rcset.MoveNext
Loop
Set rcset = Nothing
Set dbmain = Nothing
End Sub

regards
Suat
(You can visit TheWordExpert for VBA help and also other office applications)

 

[Mark W.]

On 2002-03-14 07:03, Anonymous wrote:
Is it possible when getting external data in to Excel, to run multiple query's that load data on to individual worksheets of 1 workbook?

Yes, using the Data | Get External Data menu command.