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]

No, I am running everything from Access. The only thing that is Excel is when it is creating the object, opening the file, selecting the sheet and outputting the query.

I know what you mean about something obvious. Anytime that I have encountered this error before it has always been something obvious, but for the life of me I can't get it this time!

 

[Montez659]

Just a bit more info, if it helps at all. As I step through the VBA, the error definitely triggers on

Set rst = CurrentDb.OpenRecordset(strTQName)

At which point it kicks out with an error number of 3061. Not sure any of that is pertinent.

EDIT: Here is a link that explains what is happening, though I am not sure that I understand the solution well.

http://www.pcreview.co.uk/forums/run-time-error-3061-too-few-parameters-expected-1-a-t1632430.html

 

[boblarson]

What's the ENTIRE code you are attempting to use and are you using my function? If so, how are you passing the SQL to it or what is the SQL of the saved query if you are using one.

 

[Montez659]

Here is the code that I am playing around with from your site Bob.

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 rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field

    Dim strPath As String

    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107

    On Error GoTo err_handler




    strPath = strFilePath




    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("A2").CopyFromRecordset rst


    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With


    ApXL.Selection.Font.Bold = True


    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With


    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select


    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit


    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").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

This is how I am calling it in VBA with the On Click event of a commandbutton:

Private Sub cmdExport_Click()
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)

End Sub

The SQL of the saved query is what was posted above, but I shall again for the sake of having it all in one place.

SELECT tblGroups.groupID AS tblGroups_groupID, tblContactInfo.firstName, tblContactInfo.lastName, JOINEDtblAttendance.attendanceID, JOINEDtblAttendance.contactID, JOINEDtblAttendance.groupID AS JOINEDtblAttendance_groupID, JOINEDtblAttendance.wk1, JOINEDtblAttendance.wk2, JOINEDtblAttendance.dateCreated, JOINEDtblAttendance.needChildcare
FROM tblContactInfo INNER JOIN (tblGroups INNER JOIN JOINEDtblAttendance ON tblGroups.groupID = JOINEDtblAttendance.groupID) ON tblContactInfo.contactID = JOINEDtblAttendance.contactID
WHERE (((tblGroups.groupID)=[Forms]![frmExportToSheet]![cmbGroupID]));

 

[boblarson]

The problem is going to be this line:

WHERE (((tblGroups.groupID)=[Forms]![frmExportToSheet]![cmbGroupID]));


You will need to add parameter handling by using something like found here:

http://msdn.microsoft.com/en-us/library/aa160564(v=office.11).aspx

It would be something like this code below and put before this part

For Each fld In rst.Fields

and then this (except the declarations would go at the top)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset 

Set db = CurrentDb
Set qdf = db.QueryDefs(strTQName)
qdf.Parameters(0) = _ 
Forms!frmExportToSheet.cmbGroupID
Set rst = qdf.OpenRecordset

<CODE></CODE>




</PRE>

 

[Montez659]

Thanks for the link. I will read through it and get it fixed. Thanks for your help (and your code!).

 

[Montez659]

Bob, that link was much more helpful than some of the other stuff that I found out there, so I really appreciate it. It works great so far with all of the modifications in place. Thanks again for your willingness to help!

 

[Montez659]

Xenou,

I am absolutely having trouble with that SQL string. In the Sample SQL that I provided, this

JOINEDtblAttendance.wk1

is a boolean, and I wish it to display Yes/No as opposed to True/False, but even though I've been futzin around with it, I can't seem to get it. My SQL is quite beginner as most of what I have done I was able to do through the query builders.

So is this where you use the IIf statement? Should it look like

 ,IIF([JOINEDtblAttendance.wk1]=TRUE,"Yes","No") As MyField,

from

SELECT tblGroups.groupID AS tblGroups_groupID, tblContactInfo.firstName, tblContactInfo.lastName, JOINEDtblAttendance.attendanceID, JOINEDtblAttendance.contactID, JOINEDtblAttendance.groupID AS JOINEDtblAttendance_groupID, IIF([JOINEDtblAttendance.wk1]=TRUE,"Yes","No") As MyField, JOINEDtblAttendance.wk2, JOINEDtblAttendance.dateCreated, JOINEDtblAttendance.needChildcare
FROM tblContactInfo INNER JOIN (tblGroups INNER JOIN JOINEDtblAttendance ON tblGroups.groupID = JOINEDtblAttendance.groupID) ON tblContactInfo.contactID = JOINEDtblAttendance.contactID
WHERE (((tblGroups.groupID)=[Forms]![frmExportToSheet]![cmbGroupID]));

Or am I completely off base?

 

[boblarson]

Sometimes it works better to use this instead:

IIF([JOINEDtblAttendance.wk1] <> 0,"Yes","No") As MyField,

A true/false is really 0 for false and, while it uses -1 for true it really doesn't have to be anything other than not a 0 (or an empty string or null).

 

[Montez659]

Yeah, that worked. I am assuming that either the equal sign or "TRUE" was triggering a "reserved word" error and that fixed it. Thanks!