Solved: Query Data in Excel WorkBook and Return Results in Same Workbook

[bconner]

I have an excel 2007 workbook that contains a Data Worksheet, I would like to write an MS Query against this Data Worksheet and return the results in the Same Workbook but different Worksheet is this possible?


Any help is greatly appreciated.

Brian

[austenr]

Try this:

Dim adoConnection As ADODB.Connection
Set adoConnection = New ADODB.Connection
With adoConnection
'connect to worksheet using Microsoft Jet OLE DB Provider
'.Provider = "Microsoft.Jet.OLEDB.4.0"
'.ConnectionString = "Data Source=P:\public\StatementsBugDB.xls;" & _
"Extended Properties=Excel 8.0;"

'connect to worksheet using ODBC driver
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=P:\public\StatementsBugDB.xls; ReadOnly=False;"
.Open
End With

'set up the query string
strSql = "INSERT INTO StatementsBugDB " & _
"(name, phone, accountNum, clientInfo, " & _
"callReason, problemDescription, " & _
"action, resolved, enteredBy, dateStarted) " & _
"VALUES( '" & cellName & "', '" & _
cellPhone & "', '" & _
cellAccountNum & "', '" & _
cellClient & "', '" & _
cellReason & "', '" & _
cellDescription & "', '" & _
cellAction & "', '" & _
cellResolved & "', '" & _
enteredBy & "', '" & _
Date & "')"


'execute the sql INSERT
adoConnection.Execute strSql

'close the connection and destroy adoConnection
If CBool(adoConnection.State And adStateOpen) = True Then adoConnection.Close
Set adoConnection = Nothing
You will have to do some modification to it. Field names, file path, etc. Cant take credit for it. Found it on another board.

[bconner]

Thanks austinr for the code I think this will do the job, appreciate it...