using SQL query to populate worksheet in Excel

[NBartomeli]

can someone point me to a quick tutorial, or give me some of the basic excel commands. everything I have tried so far has not worked. thanks in advance for any help

-Nick

 

[Loomah]

Hi Nick
I have to admit to being no expert when it comes to things like this (especially sql) so I've tried to use this as a learning exercise.

First thing I did, as is often the case, is record myself extracting data manually. I then tried to read the SQL. Not easy so I tidied it up a little.

The main thing I learned here was the need to manually code in the carriage returns in the SQL statement.

So, if I haven't over simplified your problem, here is the end result by way of example.

Sub EditedQuery()
'
' Macro1 Macro
' Macro recorded 24/07/2003 by Loomah
'

' Extracts Customer data from northwind2000
' only records for uk and canada customers returned

Dim sqlState As String
Dim Connect As String

Connect = "ODBC;DSN=MS Access Database;" & _
    "DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind2000.mdb;" & _
    "DefaultDir=C:\Program Files\Microsoft Office\Office\Samples;" & _
    "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;))"

sqlState = "SELECT Customers.CompanyName, Customers.Address, Customers.City, Customers.PostalCode, Customers.Country" & vbCrLf & _
    "FROM `C:\Program Files\Microsoft Office\Office\Samples\Northwind2000`.Customers Customers" & vbCrLf & _
    "WHERE (Customers.Country='UK') OR (Customers.Country='Canada')" & vbCrLf & "ORDER BY Customers.CompanyName"

With Worksheets("Data").QueryTables.Add(Connection:=Connect, Destination:=Worksheets("Data").Range("A1"))
    
    .CommandText = sqlState
    
    .Name = "Query from MS Access Database"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With
End Sub

Hope this helps
;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244