Exporting into Excel, Automating a Summary Sheet

[sam.eade]

Every day, I have to export a query from my database into excel format, where it shows all current sales for a specific product.

Each time I have to produce another summary sheet in the excel file using the exported data, its just a simple 4x2 table that does a couple of SUMIFs and COUNTIFs which will produce totals based on the stage of the sale.

What I want to try and do is automate the process, so there can be an excel file in the path folder, which has all of the formulas already in place and when the sheet is exported (into the same file), the data will update and save me having to repeat the process daily.

Is this possible?



The current code i'm using for the export is simple,

 

DoCmd.OutputTo acOutputQuery, "Query Name", "ExcelWorkbook(*.xlsx)", "t:\Management Reports\Pipeline Reports" & "\LaunchX..." & Format(Date, "DD.MM.YY") & ".xlsx", True, "", , acExportQualityPrint


Command1_Click_Exit:
   Exit Sub


Command1_Click_Err:
   MsgBox Error$
   Resume Command1_Click_Exit

Also, on a slightly less important note, does anyone know how the export the contents of a text box into a file name? I have to enter the product ID into a text box to run the query, and each time I export I want this to be reflected in the name. Much in the same way the date is.

Thankyou in advance.

[offie]

So what I would do is to in vba:

Select a file path
-Can be that same name but you enter the date and time
-can be a user input
(The user change either choose the folder and the file name is set, or choose the file name and folder)


Get the data
-Get it from a query (useful if you input parameters into a form)
-Make the query in VBA

Insert the data into the newly made excel file

Use Excel code to change the alter the data, however this code can be stored in Access VBA.

Below is an example, I'll cut bits of it out so that it isnt repetitive.
I have the user choose a folder not a file.
Notice that I specify the name of th sheet in excel.

To use these functions (Office and Excel) you'll have to turn it on: Tools>References: Select "Mircosoft Excel #.# Object Library" and "Mircosoft Office #.# Object Library"

Notice that this is all within an on-click event, so when a button on a form is clicked this excel file is made.

I didn't know how to use excel code so I would record a macro and then steal and alter slightly that code.

Private Sub Command19_Click()
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set dbs = CurrentDb
'Get file path from user
Dim F As Office.FileDialog
    Set F = Application.FileDialog(msoFileDialogFolderPicker)
    F.Show
Dim FolderPath As String
    FolderPath = F.SelectedItems.Item(1)
'MsgBox FolderPath
Dim path As String
    path = FolderPath & "\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls"

'Criteria Queries
'Duration Information
DoCmd.Close acQuery, "DurationInformation"
Dim DurationSQL As String
    DurationSQL =       " "
'MsgBox DurationSQL
CurrentDb.QueryDefs("DurationInformation").SQL = DurationSQL
DoCmd.OpenQuery "DurationInformation"
DoCmd.Save acQuery, "DurationInformation"
'End Duration Information




'Set SQL for all Graphs
' To update the Excel format acSpreadsheetTypeExcel9 must be (acSpreadsheetTypeExcel12xml and the path changes to .xlsx) or (acSpreadsheetTypeExcel12 and the path changes to .xlsb)
'Construction Survey
Dim strSQL As String, strQDF As String
strSQL = " "
strQDF = "CnstSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF, path
dbs.QueryDefs.Delete strQDF

'Start Here
'100Survey
Dim strSQL2 As String, strQDF2 As String
strSQL2 = ""
strQDF2 = "100PctSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF2, strSQL2)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF2, path
dbs.QueryDefs.Delete strQDF2
'End Here


'Edit Excel
Set xl = Excel.Application
   xl.Workbooks.Open (path)
    xl.UserControl = True



With xl
    'Construction Survey
    .Sheets("CnstSurvey").Select
    .Range("I2").Select
    .ActiveCell.FormulaR1C1 = "=RC[-8] & "" ("" & RC[-7] & "")"""
    .Range("I2").Select
    .Selection.AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row), Type:=xlFillDefault
    '.Selection.AutoFill Destination:=Range("I2:I23"), Type:=xlFillDefault
    .Columns("C:G").Select
    .Selection.Copy
    .Columns("J:N").Select
    .ActiveSheet.Paste
    .Columns("I:N").Select
    .ActiveSheet.Shapes.AddChart.Select
    .ActiveChart.ChartType = xlLineMarkers
    .ActiveChart.SetSourceData Source:=Range("'CnstSurvey'!$I:$N")


End With
 xl.Visible = True

End Sub

It looks more complicates that it is, mainly because I have really big queries.

EDIT!!! I just got rid of my sql's because it was hard to find what you really want, if you don't know how to write sql in vba there are lots of resources online. Just remember if you have anything like "d" it must become ""d""

Note that the Duration query was used by one of the queries that set the excel data.

[ssanfu]

Ken Snell has a great site to help with Access/Excel automation
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

[sam.eade]

Thankyou very much guys!