WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Easy CrossTabbed Spreadsheet Creation 

Keywords:  Easy CrossTabbed Spreadsheet Creation
The attached script is a hard-coded sample of how one might take a legacy database ( a DBF file or Ascii file ) and using Access as middleware, output crosstab results in Excel.

The script uses DAO.

stan littlefield 

; ///////////////////////////////////////////////////////////////////
; // Easy CrossTabbed Spreadsheet Creation. Link External File     //
; // to temporary Access Database, then perform SQL                //
; //                                                               //
; // Uses DAO, not ADO - tested with Office 2000                   //
; //                                                               //
; // Scenario - you have compiled sales for car models in a dbase  //
; //            file, where each record has Model, Year and Sales  //
; //            (number sold). You need to ship a quick CrossTab   //
; //            report in Excel Format                             //
; //                                                               //
; //             Stan Littlefield, July 18, 2002                   //
; ///////////////////////////////////////////////////////////////////


; //////////// check for files and create variables /////////////////
cPath      = DirGet()
cDBF       = "CARSALES"
If ! FileExist( StrCat( Dirget(), cDBF, ".DBF" ) ) Then Exit
cMDB       = StrCat( Dirget(), "CARS.MDB" )
cXLS       = StrCat( Dirget(), "CARS.XLS" )

BoxOpen( StrCat("Creating CrossTab ",cXLS," Linking ",cDBF) )

If FileExist( cXLS ) Then FileDelete( cXLS )
If FileExist( cMDB ) Then FileDelete( cMDB )


; ///// Create temp Access Database, Link DBF File //////////////////
oMDB       = ObjectOpen("Access.Application.9")
oMDB.NewCurrentDatabase(cMDB)
APP        = oMDB.Application
Cmd        = APP.docmd
Cmd.TransferDatabase( 2, "dBase III", cPath, 0, cDBF, cDBF)
ObjectClose(Cmd)
oMDB.CloseCurrentDatabase()


; //// re-open Access File and Create Temp Crosstab Query////////////
dao        = APP.DBEngine
ws         = dao.CreateWorkspace("JetWorkspace", "admin", "")
db         = ws.OpenDatabase(cMDB)

cSQL       = "TRANSFORM Sum([SALES]) SELECT [MODEL], Sum([SALES]) AS [Total SALES] "
cSQL       = StrCat( cSQL,"FROM CARSALES GROUP BY [MODEL] PIVOT [YEAR];" )

qdef       = db.CreateQueryDef("temp",cSQL)
ObjectClose(qdef)

; Move the Query to Excel with Simple SQL
cSQL = StrCat("SELECT temp.* INTO [Excel 8.0;DATABASE=",cXLS,"].[YearlySales] FROM [temp];")
db.Execute( cSQL )
qdefs = db.QueryDefs
qdefs.Delete("temp")

; close Access, delete file if desired
db.Close()
ObjectClose(db)
ObjectClose(ws)
ObjectClose(dao)
ObjectClose(APP)
ObjectClose(oMDB)
FileDelete( cMDB )

; /// finally open and format the Excel CrossTab ////////////////////
BoxText("Closing Link, Opening Excel")
oAPP               = ObjectOpen( "Excel.Application" )
oAPP.Visible       = @TRUE
oAPP.DisplayAlerts = @FALSE
oWK                = oAPP.workbooks
oWK.open(cXLS)
oACT               = oAPP.ActiveWorkbook
oWS                = oAPP.Worksheets(1)
BoxShut()
oCell                = oWS.Cells(1,1)
oRegion              = oCell.CurrentRegion
oCols                = oRegion.Columns
oCols.AutoFit
oCell                = oWS.UsedRange
oCell.AutoFormat(:: Format=3)
ObjectClose(oCols)
ObjectClose(oRegion)
oACT.Save()
ObjectClose(oACT)
ObjectClose(oWS)
ObjectClose(oWK)
ObjectClose(oAPP)

Exit
The DBF file and script are included in the zip below: 
Database to Excel.zip

Article ID:   W15629

File Created: 2017:08:29:11:58:44
Last Updated: 2017:08:29:11:58:44