Link to home
Start Free TrialLog in
Avatar of jcuzzola
jcuzzola

asked on

Output data to excel template. help

Below is a bit of code that exports data to excell. It creates a new excel file  each time it is run. The file is named based on the s-file name and the location shown below.  I would like to add some code that would do the same but open the data in  a template  file called Export_Template.xls. The template will run calculations on the data.  Can anyone help me out?

JCz

    'Build filename
    sFile = "qryReactorBatch_" & Format(Now(), "mm-dd-yyyy-hhmm") & ".xls"

    'True Output to excel and open
   DoCmd.OutputTo acQuery, "qryReactorBatch", acFormatXLS, "J:\Fermentation_Scale-Up\DB_Export_Excel\" & sFile, True


Private Sub cmdexport_via_Click()
On Error GoTo Err_cmdexport_via_Click

   
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
   
    Set MyDB = CurrentDb()
    'JCz 2/13/2007 Export Cell Count data Second
    'This is the code that generates the query...
   
    strSQL = "SELECT tbl_SpinName_Source.sns_Spinner, tbl_U2_Spinners.Date, tbl_U2_Spinners.Log_Hour, Round([tbl_U2_Spinners].[Log_Hour]/24,2)AS[Culture Days] , tbl_U2_Spinners.Viable, tbl_U2_Spinners.Total, Round([tbl_U2_Spinners].[Viable]/[tbl_U2_Spinners].[total]*100,2) AS [% Viable]  FROM tbl_SpinName_Source INNER JOIN tbl_U2_Spinners ON tbl_SpinName_Source.sns_ID = tbl_U2_Spinners.SpinnerID"
   
    'Build the IN string by looping through the listbox
    For i = 0 To LstFlasks.ListCount - 1
        If LstFlasks.Selected(i) Then
            If LstFlasks.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & LstFlasks.Column(0, i) & "',"
        End If
     Next i
     
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [SNS_Spinner] in (" & Left(strIN, Len(strIN) - 1) & ") ORDER BY tbl_SpinName_Source.sns_Spinner, tbl_U2_Spinners.Date;"
   
    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If
   
    MyDB.QueryDefs.Delete "qryReactorBatch"
    Set qdef = MyDB.CreateQueryDef("qryReactorBatch", strSQL)
'***********************************************************************************
    'This code will open the qry before the excel file...
    'Open the query, built using the IN clause to set the criteria
    'DoCmd.OpenQuery "qryReactorBatch", acViewNormal
'*************************************************************************************

    'Build filename
    sFile = "qryReactorBatch_" & Format(Now(), "mm-dd-yyyy-hhmm") & ".xls"

    'True Output to excel and open
   DoCmd.OutputTo acQuery, "qryReactorBatch", acFormatXLS, "J:\Fermentation_Scale-Up\DB_Export_Excel\" & sFile, True
   
    'False Output to excel do not open
'    DoCmd.OutputTo acQuery, "qryReactorBatch", acFormatXLS, "c:\myfiles\" & sFile, False
'****************************************************************************************

   
    'Clear listbox selection after running query
    For Each varItem In Me.LstFlasks.ItemsSelected
        Me.LstFlasks.Selected(varItem) = False
    Next varItem
   
   
Exit_cmdexport_via_Click:
   
    Exit Sub
   
Err_cmdexport_via_Click:

   If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list", , "Selection Required !"
        Resume Exit_cmdexport_via_Click
    Else
    'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Err_cmdexport_via_Click
    End If

End Sub
ASKER CERTIFIED SOLUTION
Avatar of stevbe
stevbe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial