Hello,
I have various period end financial reports in Microsoft Excel .xls workbooks setup with Jet Reports.
At period end, the reports are refreshed with the relavent option values. Some reports take 2mins to run, others can take up to 15mins to run as expected.
To significantly reduce manual work, I have combined the reports into one workbook (one worksheet per report as required) and added a macro.
The jet report options are on the first worksheet and referenced by all the other worksheets/reports.
Once the reports are refreshed, the user executes the macro and seeks approval before distributing the workbook.
During approval, accounting adjustments may be made and therefore certain worksheets need to be refreshed.
Currently, all worksheets refresh together - which takes a long time to run - causing a downside to combining the reports.
To save time, is there a way to select which worksheets refresh?
Thanks
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi,
There is no way to run only a single worksheet from a workbook. You could do it with a macro (get the UsedRange of the worksheet and then call Calculate on it).
Another way to possibly achieve your goals would be to use the Jet Scheduler's Batch File Generation feature. With this feature, you set up a master workbook which then runs other workbooks. With this sort of approach you could leave all your individual workbooks separate and then schedule a workbook to run which would run all the others. Then if a change needed to be made an individual workbook at the end, you could just re-run it. There is a tutorial video called "Dynamic Batch Scheduling" on this subject here: http://www.jetreports.com/customer-portal/tutorials.php I hope that helps.
Regards,
Hughes -
Jet Reports Historic Posts Thanks Hughes,
Your response sparked an idea. I achieved this with a Macro.
On a 'Control' worksheet (which contains the Jet report options and macro button), I added a drop down list of all Thisworkbook.Sheets names.
The user selects a worksheet and clicks another button which triggers the new macro.
The new macro:
> Moves the selected worksheet to a new workbook within the current Excel application and saves the new workbook as .xlsm.
> Opens the saved workbook in a new Excel application, adds the Jet add-in, refreshes the Jet Report and saves the file (refreshing in current app, refreshes all workbooks).
> Opens the saved workbook in the current Excel application, moves the selected worksheet back to it's original position and deletes the temp workbook.
ThanksSub Refresh_Sht() 'Sheet1.Range("Refresh_Sht").Value is the named range of the drop down list Call Refresh_Indv_JetReport(Sheet1.Range("Refresh_Sht").Value) End Sub Sub Refresh_Indv_JetReport(ByVal sht As String) 'Initialise Application.ScreenUpdating = False Application.DisplayAlerts = False 'Remember worksheet position Dim shtPos As Integer shtPos = ThisWorkbook.Sheets(sht).Index 'Setup new workbook Dim wbTemp As Workbook Set wbTemp = Application.Workbooks.Add 'Move worksheet to new workbook ThisWorkbook.Sheets(sht).Move Before:=wbTemp.Sheets(1) wbTemp.SaveAs Filename:=ThisWorkbook.Path & "\wbTemp.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled wbTemp.Close savechanges:=False 'Open new XL application Dim XLApp As Object Set XLApp = New Excel.Application XLApp.Visible = True XLApp.Interactive = True On Error Resume Next XLApp.Application.Workbooks.Open ("C:\Program Files (x86)\JetReports\JetReports.xlam") On Error GoTo 0 Set wbTemp = XLApp.Application.Workbooks.Open(ThisWorkbook.Path & "\wbTemp.xlsm") 'Refresh Jet report in new workbook application 'C:\Program Files (x86)\JetReports\JetReports.xlam'!nl() 'Application.Run "JetReports.xlam!JetMenu", "Refresh" XLApp.Application.Run "JetReports.xlam!JetMenu", "Report" wbTemp.Save wbTemp.Close savechanges:=False XLApp.Quit 'Open temp workbook in this application Set wbTemp = Application.Workbooks.Open(ThisWorkbook.Path & "\wbTemp.xlsm") 'Copy worksheet back to old workbook in correct position wbTemp.Sheets(sht).Move Before:=ThisWorkbook.Sheets(shtPos) wbTemp.Close savechanges:=False On Error Resume Next Kill ThisWorkbook.Path & "\wbTemp.xlsm" On Error GoTo 0 'Get rid of external references? 'Finalise ThisWorkbook.Save End Sub -
Jet Reports Historic Posts After a bit of testing I noticed that depending on what worksheet was extracted/refreshed, Jet Reports couldn't restore the formulae to therefore refresh the report.
I've resolved this using the same approach but instead of extracting the worksheet, a copy of the workbook is made, then all sheets are deleted except the Control sheet (with the Report options) and the worksheet to be refreshed.Sub Refresh_Indv_JetReport(ByVal sht As String) 'Initialise Application.ScreenUpdating = False Application.DisplayAlerts = False 'Remember worksheet position Dim shtPos As Integer shtPos = ThisWorkbook.Sheets(sht).Index 'Copy self FileCopy ThisWorkbook.FullName, ThisWorkbook.Path & "\wbTemp.xlsm" 'Setup new workbook in new Excel app Dim wbTemp As Workbook Dim XLApp As Object Set XLApp = New Excel.Application XLApp.Visible = True XLApp.Interactive = True On Error Resume Next XLApp.Application.Workbooks.Open ("C:\Program Files (x86)\JetReports\JetReports.xlam") On Error GoTo 0 Set wbTemp = XLApp.Application.Workbooks.Open(ThisWorkbook.Path & "\wbTemp.xlsm") 'Del all worksheet except selected and control Dim s As Worksheet For Each s In wbTemp.Sheets If s.Name <> sht And s.Name <> "Controls-0" Then s.Delete Next s 'Refresh Jet report in new workbook application XLApp.Application.Run "JetReports.xlam!JetMenu", "Refresh" wbTemp.Save wbTemp.Close savechanges:=False XLApp.Quit 'Del original worksheet ThisWorkbook.Sheets(sht).Delete 'assumed not referenced 'Open temp workbook in this application Set wbTemp = Application.Workbooks.Open(ThisWorkbook.Path & "\wbTemp.xlsm") 'Copy worksheet back to old workbook in correct position wbTemp.Sheets(sht).Move Before:=ThisWorkbook.Sheets(shtPos) wbTemp.Close savechanges:=False On Error Resume Next Kill ThisWorkbook.Path & "\wbTemp.xlsm" On Error GoTo 0 'Finalise ThisWorkbook.Save End Sub -
Jet Reports Historic Posts Hi sdale89,
Actually, I found a way to choose which worksheets to run without using any macro at all. Don't know if my method suitable for your need or not, but I'll give it a try.
Go ahead and read my explanation here:
http://community.jetreports.com/viewtopic.php?f=6&t=2453
For example how it's done, look here:
http://community.jetreports.com/viewtopic.php?f=7&t=2652#p8033
Andy -
Jet Reports Historic Posts Thanks for your suggestion Andy, I was really hoping for a simply options/formulae solution.
However, I think your suggestion serves a different purpose.
To give an example: In WorkbookA there is Sheet1 (P&L) and Sheet2 (List of Debtors). I use your suggestion and set 'YES' to both Sheet1 and Sheet2. I click 'Refresh' on the Jet panel, Sheet1 takes 2 mins to update and Sheet2 takes 10mins to update. I now have two populated worksheets as expected. I then make a change to the accounts and need to refresh the P&L (Sheet1). I only want to refresh Sheet1 since Sheet2 doesn't need to change. Using your suggestion, I set 'YES' to Sheet1 and 'NO' to Sheet2 and click 'Refresh' again. I now have an updated Sheet1 which now includes the change but Sheet2 is now blank. Instead I wanted Sheet2 to stay the same, whilst I only update Sheet1.
Please sign in to leave a comment.