?
Solved

Output data to excel template. help

Posted on 2007-03-19
1
Medium Priority
?
474 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:jcuzzola
1 Comment
 
LVL 39

Accepted Solution

by:
stevbe earned 1500 total points
ID: 18750733
I think if you use the FileCopy command to make a copy of you template you can then either arrange the template to read from the dtaa that gets pumped in all in 1 big block or you will have to hand code pushing the data into the sreadsheet via automation

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question