Setting Reference to Excel Library

[Montez659]

If I set a reference in my Access db to Excel 12.0, will this reference automatically carry over to a different machine if I distribute the db?

What if they are working with an earlier version of Excel by some chance - will it have to be manually set then or will it default to the most current library?

 

[Montez659]

One last question - What is the proper way to "close" the file for the code that you linked to? I seem to be finding that the Excel sheet retains a record locking file after the code is done executing.

 

[boblarson]

One last question - What is the proper way to "close" the file for the code that you linked to? I seem to be finding that the Excel sheet retains a record locking file after the code is done executing.

What is the final code for the entire process that you ended up with. It is likely that there is one thing (or more) not tied to an instantiated object. Read this from my website which explains why that is bad:
http://www.btabdevelopment.com/ts/excelinstance

 

[Montez659]

Here is the final code:

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to


' strFilePath is the name and path of the file you want to send this data into.

    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
    strPath = strFilePath
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strTQName)
    qdf.Parameters(0) = _
        Forms!frmExportToSheet!cmbGroupID
    Set rst = qdf.OpenRecordset


    
    'Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPath)
    ApXL.Visible = True
    Set xlWSh = xlWBk.Worksheets(strSheetName)
    xlWSh.Range("A1").Select
    'For Each fld In rst.Fields
    '    ApXL.ActiveCell = fld.Name
    '    ApXL.ActiveCell.Offset(0, 1).Select
    'Next
    rst.MoveFirst
    xlWSh.Range("B8").CopyFromRecordset rst
    xlWSh.Range("1:1").Select

    rst.Close
    Set rst = Nothing
    

Exit_SendTQ2XLWbSheet:
    Exit Function


err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheet
End Function

and the function is called by:

Dim curPath As String
Dim queryName As String
Dim sheetName As String

curPath = DLookup("[setInfo]", "tblSettings", "[ID] =" & 1)

queryName = "qrySendToSheet"
sheetName = "Master"

Call SendTQ2XLWbSheet(queryName, sheetName, curPath)

I see what your post is saying, but I don't see any instance (and maybe I missed it) where I tried to do something not tied to an object.

EDIT: huh...that I just ran it again and it worked fine. Maybe I killed the code before it finished before, but I am almost positive that it did it several times.