[AccessD] Writing to an excel file

I got it to work! With a little bit from here and a little from there this is what I am doing...
 
    On Error GoTo ErrControl
    
    DoCmd.SetWarnings False
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset 'records to be sent to the excel sheet
    Dim rsStatus As DAO.Recordset 'a list of all the statuses
    Dim qry As DAO.QueryDef 'used to define the query for the recordset we need to send to excel
    Dim strSQL As String 'used to hold the fields for exporting to excel
    Dim tabName As String 'the name of the tab in excel and the name of the query to export
    Dim outputFile As String 'destination excel file
    Dim monthlyCopy As String 'used to create a monthly copy of the file
    Dim i As Integer 'counter used to determine a new line in excel
    
    Set db = CurrentDb
    
    'select fields for excel sheet
    strSQL = "SELECT LNAME, FNAME, KAECSESID, [FROM], [TO], [PROCEDURE CODE], " _
            & "[DIAGNOSIS CODE], [UNIT RATE], UNITS, EXTENSION, [AUTHORIZATION NUMBER] " _
            & "FROM qryStandardBillingForm "
            
    'destination
    outputFile = "I:\DEPTDATA\BILLING FORM\BillingForm.xls"
    
    'open the excel spreadsheet
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = True
    xlsApp.UserControl = True
    xlsApp.Workbooks.Open (outputFile)
    
    '**************************************************************************************
    'Create HOSP tab of the excel spread sheet
    
    tabName = "HOSP"
    
    Set qry = db.CreateQueryDef(tabName, strSQL _
            & " WHERE PLCTYPE = 'HOSP' ORDER BY LNAME, FNAME")
    
    'select the appropriate tab
    Set xlsWSheet = xlsApp.Worksheets(tabName)
    xlsWSheet.Activate
    
    'select range to be cleared and clear it
    xlsApp.Range("A10:K65536").Select
    xlsApp.Selection.ClearContents
    
    'get records for the tab from our newly made query
    Set rs = db.OpenRecordset(tabName)
    
    i = 10 'set counter to the line of our excel worksheet where I want it to start writing
    
    Do Until rs.EOF
    
        xlsApp.ActiveSheet.Range("A" & i).Value = rs("LNAME")
        xlsApp.ActiveSheet.Range("B" & i).Value = rs("FNAME")
        xlsApp.ActiveSheet.Range("C" & i).Value = rs("KAECSESID")
        xlsApp.ActiveSheet.Range("D" & i).Value = rs("FROM")
        xlsApp.ActiveSheet.Range("E" & i).Value = rs("TO")
        xlsApp.ActiveSheet.Range("F" & i).Value = rs("PROCEDURE CODE")
        xlsApp.ActiveSheet.Range("G" & i).Value = rs("DIAGNOSIS CODE")
        xlsApp.ActiveSheet.Range("H" & i).Value = rs("UNIT RATE")
        xlsApp.ActiveSheet.Range("I" & i).Value = rs("UNITS")
        xlsApp.ActiveSheet.Range("J" & i).Value = rs("EXTENSION")
        xlsApp.ActiveSheet.Range("K" & i).Value = rs("AUTHORIZATION NUMBER")
        
        i = i + 1
    
    rs.MoveNext
    Loop
                                         
    DoCmd.DeleteObject acQuery, tabName 'trash the temporary query
    '**************************************************************************************
    

DWUTKA at marlow.com wrote:
Do you want to start on the second line? You can treat Excel as a recordset
with ADO.

Drew

-----Original Message-----
From: Lonnie Johnson [mailto:prodevmg at yahoo.com]
Sent: Tuesday, August 02, 2005 7:35 AM
To: AccessD solving'
Subject: [AccessD] Writing to an excel file


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












---------------------------------
Start your day with Yahoo! - make it your home page 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us





 





__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com