Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically close 'Source' worksheet and refresh Pivot Table?
Hello,
I run the following code from behind a command button in an Access database. It produces the file fine, but when I open the file, I would like to worksheet named 'SOURCE' to be hidden, and for the pivot table data to be refreshed. Are there some lines of code I can add to make this happen? Here is the code: Option Compare Database Option Explicit Function CreateExcelReport(strQueryName As String) As Boolean 'Turn off any access warning and prompts DoCmd.SetWarnings False 'Get current Database location. 'Used as a variable b/c individual drive mappings may vary Dim db As DAO.Database, strPath As String, strDir As String Set db = CurrentDb() strPath = CurrentDb.Name strDir = Left$(strPath, Len(strPath) - Len(Dir(strPath)) - 1) & "\" 'Set Report Export Path and File Name w/ last Sunday's date Dim strReportPath, strFileName As String strReportPath = strDir & "Report_Exports\" strFileName = strReportPath & strQueryName & "_" & Replace(Forms![frm_MAIN_MENU]![txtRptEnd], "/", "") & ".xls" 'Calculate Prior Sunday's Date = [Todays Date] - (WhatDayInTheWeekIsToday) + 1 = LAST SUNDAY 'ie. Fri 10/24: ' Friday is 6th Day in the Week, Sunday is 1st Day in the Week, so need to go back 5 days. ' [10/24] - (6)+ 1 = 10/19 'Dim strDate 'strDate = Date - Weekday(Date, vbSunday) + 1 'strFileName = strReportPath & "ReportTitle_" & Format(strDate, "dddd mm-dd-yyyy") & ".xls" 'Path and FileName for TEMPLATE Files. Could use queryName in Template FileName to permit 'using function with mulitple templates Dim strTemp As String strTemp = strReportPath & "doNotTouch_BLANK_TEMPLATE\" & strQueryName & "_TEMP.xls" 'strTemp = strReportPath & "doNotTouch_BLANK_TEMPLATE\AllRecordsAsOfYesterday _TEMP.xls" 'Get and Set String Values for Report Headings - Title, Run Dates, Eff Dates, etc. Dim strTitle, strClaimDate, strRunDate strTitle = "Default Title" 'Build Report title using values on Main Form (frm_MAIN_MENU) strTitle = "Acknowledgment Information on Quotes/Policies Created Between " & _ Forms![frm_MAIN_MENU]![txtRptStart] & " AND " & Forms![frm_MAIN_MENU]![txtRptEnd] 'Old Code building dates on the fly and formatting them. Now using values from form. ''Current Months Report 'strClaimDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) 'strTitle = Format(strClaimDate, "mmmm yyyy") & " Claims as of " & _ ' Format(DateSerial(Year(Now()), Month(Now()), Day(Now()) - 1), "mm/dd/yy") 'Set up query being run. 'Note: This is just the value being passed and can likely be removed Dim strExpName As String strExpName = strQueryName '-- Dump Query to Spreadsheet and Set Up Worksheetname Name for Worksheet -- Dim strRngName As String 'strRngName = "All Records As Of Yesterday" strRngName = strQueryName DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strExpName, strTemp, , strRngName 'Create an Excel Object, OPen Workbook for formatting and point it to the Template Directory Dim xlApp As Object Set xlApp = CreateObject("EXCEL.APPLICATION") Dim DataWB As Workbook xlApp.Visible = True Set DataWB = xlApp.Workbooks.Open(strTemp) '-- Format Excel Worksheet DataWB.Worksheets("DETAIL").Range("Create_Date").V alue = "Report Created : " & Format(Now, "mm/dd/yyyy") DataWB.Worksheets("DETAIL").Range("Report_Title"). Value = strTitle '-- Save and Close Formatted Report, Exit Excel -- On Error Resume Next Kill strFileName 'delete an existing version if it already exists On Error GoTo 0 DataWB.SaveAs strFileName DataWB.Close xlApp.Quit '-- Alert Report has been printed -- MsgBox "Your Report has been saved to " & strFileName DoCmd.SetWarnings True End Function -- Pat Dools |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically refreshing a data source for a pivot table (olap) | Excel Programming | |||
Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table's Source Range) | Excel Programming | |||
How to refresh a pivot table that uses changing source data | Excel Programming | |||
How do I refresh Pivot Table from an external source ? | Excel Worksheet Functions | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions |