Lonnie,
Be sure to set the DAO reference and the Excel reference. This works in
Access/Excel 2000. Please see comment about where to set column and row
reference. This code currently uses A1 as the starting point.
Good Luck
Hal
Function QrytoExcel(strFile As String, strSheet As String, strQuery As
String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim strCurrentField As String
Dim varCurrentValue As Variant
Set db = CurrentDb()
Set rst = db.OpenRecordset(strQuery, dbOpenForwardOnly)
'OpenRecordsetOutput rstTemp
DoCmd.Hourglass True
If Assistant.Visible = True Then
With Assistant
.On = True
.Animation = msoAnimationCheckingSomething
.Visible = True
End With
End If
'**************************************************************************
'Open Spreadsheet
'**************************************************************************
' Start Excel Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open File
xlApp.Workbooks.Open (strFile)
'************************************************************************************
'Clear Spreadsheet
'************************************************************************************
'
'Set Sheet = xlApp.activeworkbook.Sheets("ScheduleChangesDetail")
With xlApp
.Sheets(strSheet).Select
.Range("A2:F65536").Select 'Note range selected may need to be
changed
.Selection.ClearContents
End With
Set xlSht = xlApp.ActiveWorkbook.Sheets(strSheet)
j = 1
'Loop through the Microsoft Access field names and create
'the Microsoft Excel labels.
For i = 0 To rst.Fields.Count - 1
varCurrentValue = rst.Fields(i).Name
xlSht.Cells(j, i + 1).Value = varCurrentValue
Next i
j = 2
' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until rst.EOF
For i = 0 To rst.Fields.Count - 1
varCurrentValue = rst(i)
xlSht.Cells(j, i + 1).Value = varCurrentValue 'Make changes here to
control riw and column
Next i
rst.MoveNext
j = j + 1
Loop
xlApp.Workbooks.Application.Visible = False
xlApp.Application.ActiveWorkbook.Save
xlApp.Application.ActiveWorkbook.Close
xlApp.Quit
' Clear the object variable.
Set xlSht = Nothing
Set xlWkb = Nothing
Set xlApp = Nothing
DoCmd.Hourglass False
With Assistant
.On = True
.Animation = msoAnimationEmptyTrash
.Visible = True
End With
End Function
Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC
Lonnie Johnson
<[EMAIL PROTECTED]
om> To
Sent by: AccessDevelopers
AccessDevelopers@ <accessdevelopers@yahoogroups.com>
yahoogroups.com cc
Subject
08/02/2005 08:36 [AccessDevelopers] Writing to an
AM excel file
Please respond to
AccessDevelopers@
yahoogroups.com
I have a recordset that I would like to write as rows in an excel
spreadsheet.
I want to start on a certain line.
The columns are already predefined and match my recordset.
This will run each month so it will need to overwrite the prior month's
data.
What is the best way to go about this?
May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us
Yahoo! Mail
Stay connected, organized, and protected. Take the tour
Please zip all files prior to uploading to Files section.
YAHOO! GROUPS LINKS
Visit your group "AccessDevelopers" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12h73dtus/M=362329.6886308.7839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123003080/A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?page_id=1992
">Fair play? Video games influencing politics. Click and talk back!</a>.</font>
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
