Access table query to Excel

Keywords: 	Access table query to Excel.txt

Make sure DAO.DBEngine.35 exists on those computers. Maybe try searching the registry for that application object. Search under HKEY_Classes_Root for DAO.DBEngine....

And make sure that version 3.5 (35 above) is installed. Open up the registry editor on the PC and do a search for:

DAO.DBEngine

If it's installed it will show up as: DAO.DBEngine.35

;	this script will allow you to take ANY table-query from MS Access and 
;	pump it into MS Excel, very similar to the ANALYZE IT WITH MS EXCEL
;	shorcut on the MS Access menu.
;
;	you don't have to fuss with knowing the tablename (other than the
;	sqlstr variable) how many fields are in the query or their names.
;	the script analyzes the recordset and does the work for you.
;
;	note that the key is the SQLSTR variable and the way you word the
;	sql statement. if you want all columns choose *, if you want only
;	certain ones, choose them in the order you'd like them to appear.
;
;	my script uses Excel and Access from Office 97 and Winbatch 2000c.
;	
;	1/30/2001 by Jay Alverson

debugtrace(@off, "c:\bin\eaprint.txt")

dao = objectopen("DAO.DBEngine.35")

ws = dao.CreateWorkspace("JetWorkspace", "admin", "")
dbasename = "c:\data\access\exchange region ea.mdb"
db = ws.OpenDatabase(dbasename)

sqlstr = "SELECT * FROM [OMC SCI] order by [count] desc;"

rs = db.openrecordset(sqlstr)

;	Start excel here
excelxls = objectopen("Excel.Application")
excelxls.visible = @true
rptxls = excelxls.workbooks
;	add a blank workbook...
rptxls.add
;	initialize the row/col vars...
x=1
y=1
;	get a count of how many fields in the recordset...
rf = rs.fields
rfcount = rf.count - 1	;	subtract 1 from it because access is zero-based...
;	first put in the field names...
for y = 0 to rfcount
;	input the field names...
;	access is zero-based...
	cell = excelxls.cells(x, y+1)
	thisfield = rs.fields(y)
	cell.value = thisfield.name
next
;	increment the rows for the data input...
x = 2
;	loop thru the recordset...
while rs.eof == @false
;	input the data for each field in the recordset...
;	access is zero-based...
	for y = 0 to rfcount
		cell = excelxls.cells(x, y+1)
		thisfield = rs.fields(y)
		cell.value = thisfield.value
	next
	x = x + 1	; increment the row
	rs.movenext
endwhile

objectclose(rs)
objectclose(db)
objectclose(ws)
objectclose(dao)

message("Debug", "End of Job")

debugtrace(@off, "c:\bin\eaprint.txt")

exit




Thanks, Jay


--------------------------------------------------------------------------------
Post New Topic | Reply to: "Putting Access data into Excel"

Article ID:   W14926

File Created: 2003:02:03:15:42:16

Last Updated: 2003:02:03:15:42:16

Database Search

TechHome

Access table query to Excel