Process data in a sheet within workbook using SQL query?

S

Stevie_mac

I asked this a while back, never really got it pinned down, thought I'd try again.
I'd like to query a sheet in a workbook - example...

Set rs = SomeFunction("SELECT TOP 5 Sales FROM Sheet1")

While Not rs.EOF
'Do something with rs
rs.MoveNext
Wend

Obviously this is in VBA - thats fine.

If this can be done (even if pivot tables have to be used) please help.

Cheers.

PS:
I could, as an alternative, go straight to SQL Server for data, but there are no DSNs set-up on users PCs (nor will
there be).
Is it possible to do my queries directly to SQL Server without a DSN (i.e. using a connection string)?
example...
sConnectionString = "Data Source=server1;Initial Catalog=myDatabaseName;Integrated Security=SSPI"
Set rs = SomeOtherFunction("SELECT TOP 5 Sales FROM SalesTable", sConnectionString)

While Not rs.EOF
'Do something with rs
rs.MoveNext
Wend
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top