Hi,
Was wording if anyone knew of a way to run a SQL to create a new block of data from a pre-existing block of data in the current active workbook???
I am very used to using SQL statements to extract data from databases into excel workbooks and would dearly love to use SQL to manipulate the data already in the current active workbook.
Is this remotely possible or just a pipe dream???
Thanking in advance
Ceelly
Re: Ues SQL on data in Active Workbook
With Sheet1 .Cells.Clear
Set Cnn = New ADODB.Connection
With Cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Extended Properties=Excel 12.0;" _
& "Data Source=" & ThisWorkbook.Path & "\数据.xlsx"
.Open
End With
Set rs = New ADODB.Recordset
Sql = "Select * From [Sheet1$]"
rs.Open Sql, Cnn, adOpenKeyset, adLockOptimistic
For j = 0 To rs.Fields.Count - 1
.Cells(1, j + 1) = rs.Fields(j).Name
Next
r = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A" & r + 1).CopyFromRecordset rs
End WithRe: Ues SQL on data in Active Workbook
Yes it's possible as shown in the post above, but you don't really want to do it since it causes memory leaks.
Re: Ues SQL on data in Active Workbook
Thanks for your replys.. It would be nice if you could do this effectively with SQL in excel.. I ended up using a filter to create a new block of data from existing data in the workbook using VB code.
Don’t have an account yet? Register yourself now and be a part of our community!