Query Excel Spreasheet to populate another spreadsheet 1

[malaygal]

I just created a data dump of our Oracle database into an Excel spreadsheet.

I am looking for a way to query this spreadsheet to populate a new spreadsheet on the same workbook.
This is what I have so far:

Dim datafilename, serverdir

serverdir = "C:\temp"
datafilename = serverdir & "\Productivity.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True
objExcel.Application.ScreenUpdating = True


Set objWorkbook = objExcel.Workbooks.Open(datafilename)

Set objWorksheet1 = objWorkbook.Worksheets.Add(, objWorkbook.Worksheets(objWorkbook.Worksheets.Count))
objWorksheet1.Name = "Productivity"

Set rsExcel = CreateObject("ADODB.Recordset")

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn = sConn & "Data Source=" & datafilename & ";"
sConn = sConn & "Extended Properties=Excel 8.0;"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open sConn

sSQL = "SELECT * FROM Jul" 'Jul being the spreadsheet-I do not have the where clause yet and already I am getting an error

rsExcel.Open sSQL, cnn

rsExcel.Close
cnn.Close
Set rsExcel = Nothing
Set cnn = Nothing


Any help to get me started will be greatly appreciated.

 

[malaygal]

I found a solution using Querytable that worked fine in VBA but giving me syntax error (MISSING ")")when I used it in my Window script file.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Temp\Productivity.xls;Mode=Share Deny Write;Extended P" _
, _
"roperties=""HDR=NO;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type" _
, _
"=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Dat" _
, _
"abase Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact" _
, "=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Select F1 from [Jul$] where F8 = ""GAP"" ORDER BY F1 DESC")
.Name = "Productivity"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Temp\Productivity.xls"
.Refresh BackgroundQuery:=False
End With
End Sub

Can anybody show me how to convert this macro into a useable code in wsf.

Any help will be greatly appreciated.

 

[oldenny]

Try

sSQL = "SELECT * FROM [$Jul]

 

[malaygal]

Thanks for the post.
Tried it but still get same error.

 

[tsuji]

[1] connection string
[tt]
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn = sConn & "Data Source=" & datafilename & ";"
sConn = sConn & "Extended Properties=Excel 8.0;""HDR=no;IMEX=1"";"
[/tt]
Some extended properties setting may not be needed for an administration script hosted by wscript/cscript, but sure you can get some inspiration from the 2nd approach recorded in the macro.

[2] Preliminary sql
[tt]
sSQL = "SELECT * FROM [Jul$]"
[/tt]
Elaborated sql can be read from your second approach.

 

[malaygal]

Nothing seemed to happened (at least no error) when I tried this:

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn = sConn & "Data Source=" & datafilename & ";"
sConn = sConn & "Extended Properties=Excel 8.0;""HDR=no;IMEX=1"";"

sSQL = "SELECT * FROM [Jul$]"

Set rs = CreateObject("ADODB.Recordset")
rs.Open sSQL, sConn

PArt of the code creates a Productivity sheet, which it did.
Maybe I need to specify target cell?

 

[tsuji]

I would say the sql and the connection string should be fine modulo any password protection etc set on the workbook...

[3] But, you have to make "Productivity" is not existing before naming the newly created worksheet with the said name.

[4] You have to make use of the returned rsExcel to conclude it does not work if there is no runtime error. The simple test line can arbitrarily be something like this.
[tt]
if not rsExcel.eof then
wscript.echo rsExcel.fields.count
else
wscript.echo "Find no record."
end if
[/tt]

 

[malaygal]

Tsuji, thanks for the replies.

I did this,

if not rs.eof then
wscript.echo rs.fields.count
else
wscript.echo "Find no record."
end if

but got zero records.

 

[tsuji]

... then inspect if the worksheet named "Jul" is there or not and if it is empty? It should be empty if rsExcel.eof is true right away.

 

[malaygal]

Jul Worksheet exist with 1026 rows and 11 columns.

 

[tsuji]

[1.a] I am sorry! I had typed out the sconn amendment wrong.
>[self]sConn = sConn & "Extended Properties=Excel 8.0;""HDR=no;IMEX=1"";"
It should be read like this.
[tt]sConn = sConn & "Extended Properties=[red]""[/red]Excel 8.0[highlight];H[/highlight]DR=no;IMEX=1"";"[/tt]

ps: One thing I now note is that you use rsExcel in your original script and eventually end up using rs. I hope there is no mixed up there. I don't think so, though.

 

[malaygal]

It worked as far as giving me the field count.
When I expand on my sSQL

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn = sConn & "Data Source=" & datafilename & ";"
sConn = sConn & "Extended Properties=""Excel 8.0;HDR=no;IMEX=1"";"


sSQL = "SELECT F1 FROM [Jul$] WHERE F8 = ""GAP"""

Set rs = CreateObject("ADODB.Recordset")
rs.Open sSQL, sConn

if not rs.eof then
wscript.echo rs.fields.count - 1
else
wscript.echo "Find no record."
end if

i = 1
do while not rs.eof

wscript.echo(rs("F1")) - EXPECTED 3 RECORDS ONLY BUT RETURNED MORE THAN 3 - LOST COUNT AFTER 100
.cells(i,1).value = rs("F1") -did not write to Productivity ws
.cells(i,2).value = "GAP" -write to Productivity ws


Loop

 

[malaygal]

do while not rs.eof

wscript.echo(rs("F1")) - EXPECTED 3 RECORDS ONLY BUT RETURNED MORE THAN 3 - LOST COUNT AFTER 100
.cells(i,1).value = rs("F1") -did not write to Productivity ws
.cells(i,2).value = "GAP" -write to Productivity ws

i = i + 1 forgot this
Loop

 

[tsuji]

Don't forget to move the record with
[tt] rs.movenext[/tt]
Put it before or after [tt]i=i+1[/tt] before [tt]Loop[/tt].

 

[malaygal]

tsuji, thank you for your patience.
Everything's working as expected.