Oracle DB View -> Copying View to Excel using VBScript

Question

english isn't my native tongue, but I hope I can explain my problem sufficiently.

I made a View in the Oracle DB which only contains the data I need. Using SQL in my VBScript file, I select the View by using:

"SELECT * FROM TEST_1234"

I have selected the complete view now, that works fine. Now I need to 'export' or copy the complete View to Excel using VBScript (via UFT [Unified Functional Testing]).

1. Is there an easy way to just copy the whole thing at once or at least complete rows or columns?
2. If 1. doesn't work, can I just 'iterate' through the rows and columns using two loops and copy the data from every field to the respective field in Excel?

It would be nice to be able to copy the Data without using the names of the columns in a recordset (is there a way to use numbers until EOC [End of columns]?), because there is a very high amount of columns to be copied and the column names are subject to change.

Thanks for any help!

Answer 1

From a programmer==code writer's point of you the most attractive solution is your very first approach (copy the whole thing with just one SQL statement). Depending on the providers' capabilities this statement could look like

INSERT INTO [DstTable] SELECT * FROM [SrcTable] IN '' 'odbc;dsn=DSNName'

or

SELECT * INTO [DstTable] FROM [SrcTable] IN '' 'odbc;dsn=DSNName'

Look here for a working solution that couldn't be simpler; but I admit that a dsnless connection to the destination database looks more complicated and your drivers may have other incantations to refer to the external Database. Furthermore, your pair of providers may not support an external connection from the source to the destination and the dirty trick of using the Access OLEDB driver (which came/still comes? with ADO) to connect to both Databases externally may not work for you. In all, it's certainly not easy to get "INSERT/SELECT INTO External Database" right. [Look at my (just downvoted) answer to see that people dispair and fall back (and upvote) code that uses single-item-copy-loops.] In your case, you'll have to research whether at least one of the Oracle providers available to you supports external connections to Excel (or vice versa).

From a programmer==hacker's point of view (let's get the job done with minimal fuss) an easy solution could be to export the views/tables to .csv ( I looked at this and was disappointed, but you may know much better) and to import them into Excel (just load .csv and save .xls) If you can't/won't use the file system, you could go thru memory: Use GetRows to get the data into a two dimensional array and assign that to the desired Excel range.

If all the above fails and you need assignments to single cells in row and column loopings over the recordset, remember that the Fields collection gives you access to not only the data but the meta-info (number of columns, column-names, types, ...) too.

Answer 2

Thanks for the help, and the links you provided, Ekkehard and Bond! After reading them and trying a lot, i got a very simple solution.

Here's some working code, if anybody else faces the same or a similar problem:

Option explicit

Dim conn, rec, xlStat, xlStatW, dbCnnStr, SQLSec, statArt

Set conn        = Createobject("ADODB.Connection") 
Set rec         = CreateObject("ADODB.Recordset")
Set xlStat      = CreateObject("Excel.Application")

dbCnnStr = "[your DB-connection]"

conn.open dbCnnStr
'Start Excel XXX
Set xlStatW = xlStat.Workbooks.Add()
xlStatW.Sheets(1).Name = "AAA_123"
xlStatW.Sheets(2).Name = "BBB_123"
xlStatW.Sheets(3).Name = "CCC_123"

SQLSec = "SELECT * FROM XXX_123"
rec.open SQLSec,conn
xlStatW.Sheets(1).cells(2,1).CopyFromRecordset rec
rec.Close

SQLSec = "SELECT * FROM YYY_123"
rec.open SQLSec,conn
xlStatW.Sheets(2).cells(2,1).CopyFromRecordset rec
rec.Close

SQLSec = "SELECT * FROM ZZZ_123"
rec.open SQLSec,conn
xlStatW.Sheets(3).cells(2,1).CopyFromRecordset rec
rec.Close

xlStatW.SaveAs ("C:\test.xlsx")
xlStatW.Close
'Ende Excel XXX
conn.Close