Visual Basic / SQL help

[Chronus]

Apologies for putting it in the wrong forum... I'm getting really frustrated with some visual basic.

Microsoft's ODBC.XLA doesn't work for Excel 2003 (so no more sql.request function - if I could get the source code though...), and I am trying to use a VisualBasic function to get data from a SQL Server. The data is summed, so there is only one result. I have tried making a macro to put it on the worksheet but functions only let you affect the cell you are calling it from. I'm not sure how to get the data without putting it on the worksheet.

Any ideas what I can do ?

*note: some of the work below is edited out, eg. the connect string for the connection, with passwords etc

Sub mcrImportExt()

Dim sql As String

Dim connect As String

sql = "SELECT ISNULL(SUM(amount),0.000) FROM table1 WHERE period >= 200701 AND period <= 200712"

connect = "ODBC;DRIVER=SQL;SERVER=RWG-HelpMe!;UID=RWG1;PWD=RWG2;APP=Microsoft Office 2003;WSID=RWGisGreat"

'

With ActiveSheet.QueryTables.Add(Connection:=connect, Destination:=Range("A1"))

.CommandText = (sql)

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.Refresh BackgroundQuery:=False

End With

End Sub

Function GetAmt()

Dim Amt

Call mcrImportExt

GetAmt = Worksheets("sheet1").Range("A1")

End Function

[cornerstone]

Dim poster

.PutItInTheWrongForum

Function GetMod

.MovedOffTopic

End Function

[HauteHippie]

Suggestion............. don't use VB.

[owdeguy]

when are you going to run this? ad-hoc or on a scheduler? If this is going to be run like an application then I suggest you ditch it and just use cmdsql to run your query and push your result into a sheet. Then all you need to do is link the result fields to your sheet.

[Chronus]

Suggestion............. don't use VB.

It's in Excel... I'm trying to make a function so that other users can pull sums directly from the database. This will help in the production of automatic reports.

It's very frustrating !

[Chronus]

when are you going to run this? ad-hoc or on a scheduler? If this is going to be run like an application then I suggest you ditch it and just use cmdsql to run your query and push your result into a sheet. Then all you need to do is link the result fields to your sheet.

It's mainly on a report.

There is user entry for the time period (for example), and then the whole book is refreshed, and it will pull all the data off. This can then be printed off, pdf'd etc and sent to the relevant people.

Previously this was done using SQL.Request, but that's disappeared

[Chronus]

And there are lots of cells getting lots of results. The calculation function on the sheet will be disabled and attached to a button to re-calculate.