CurrentDb.OpenRecordset

[Blayzze]

yes when I run the query on it's own using the same search criteria it returns 96 records, so I don't think that is the problem...

 

[Macropheliac]

Did you add the [ ] around Plate Name? Can you post your rewritten code?

 

[Blayzze]

Module:

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
    
    Dim strSQL As String
    
   
    strSQL = "SELECT * FROM qryExportToExcel WHERE [Plate Name]='" & PlateName & "';"

    
    

    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    'On Error GoTo err_handler
    

    strPath = strFilePath
 
    
    
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
 
    Set ApXL = CreateObject("Excel.Application")
 

    Set xlWBk = ApXL.Workbooks.Open(strPath)
 
    ApXL.Visible = True
 
    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWSh.Activate
 
    xlWSh.Range("C8").Select
 
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
        
    
    Next
    rst.MoveFirst
    
    xlWSh.Range("C8").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

Command Button On Click:

Private Sub Command2_Click()
Call SendTQ2XLWbSheet("qryExportToExcel", "TFDNA311SampleInfo", "C:....\TF-DNA311_Template.xlsx")
End Sub

 

[Macropheliac]

Is PlateName the name of your control? Where is this code stored? Is it in the form module?

 

[Blayzze]

Plate Name is the name of the control on a form with a command button and a combo box.

The module is a stand alone module.

 

[Macropheliac]

Okay. So, your code is looking for e record "PlateName" instead of the value from the combobox. Try setting another variable in your function such as strPlateName and pass it from the click event. Either that or use Forms!YourForm!Platename.

 

[Macropheliac]

Private Sub Command2_Click()
Call SendTQ2XLWbSheet("qryExportToExcel", "TFDNA311SampleInfo", "C:....\TF-DNA311_Template.xlsx", Me.PlateName)
End Sub

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String, strPlateName)
End Function
' 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
 
    Dim strSQL As String
 
 
    strSQL = "SELECT * FROM qryExportToExcel WHERE [Plate Name]='" & strPlateName & "';"

 

[Blayzze]

I don't even know how to thank you. This worked so perfectly and it did it exactly as I had hoped. I've learned so much from this project and knew NOTHING about VB when I started (I know only a little more than that now).

Thank you so much for your time and patience.

 

[Macropheliac]

You're very welcome. We're all learning as we go along. Especially me.

 

[boblarson]

The only bad thing in this solution is that we have taken, what USED to be a generic function which would work for any table or query and have made it so that it is good for only ONE.

 

[Blayzze]

This particular part of my project has been so unique and specific that I had no choice but to do it in a non-conventional way because of the limitations of the number of fields you can have in a query...and yes I broke all the rules with that one. I may never need to use this code for anything else ever again but it works!!! And it's beautiful. I think I want to marry this code.

 

[Macropheliac]

lol! Bob is absolutely correct, but the codes and methods used would have to be completely revamped. I'd rather have several specific functions that are easily used instead of a generic one that I have to run around in circles to use anyway.

 

[boblarson]

lol! Bob is absolutely correct, but the codes and methods used would have to be completely revamped. I'd rather have several specific functions that are easily used instead of a generic one that I have to run around in circles to use anyway.

You obviously don't understand the power of reusable code. It wouldn't be too hard to fix. But at the moment I have a very slow loading connection (am working remotely from home and our VPN is not all that good). I will try to post back the better solution later.

 

[Macropheliac]

No argument here, Bob. I am a novice compared to you. I do understand the advantage of reusable functions. I use many of them. I also remember just starting out in VBA and being lost.

Once I studied what a function could do and how it worked, I was able to create a generic one from it.