ODBC driven Excel report to Static Data Excel Report.

Good evening,

I have an Excel report that when 'Refresh All' is clicked it runs and updates accordingly.. What I would like to happen is that after the excel spreadsheet is updated that it automatically creates a Static version of the report in the same drive but with a timestamp and does not have the ODBC connection.. How can this be accomplished using Task Scheduler so that it happens automatically??  Loaded question but it is fairly important
Michael KatzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
see if this creates the file you want:

Option Explicit

Sub SaveStaticFile()
'151115, strive4peace

   On Error GoTo Proc_Err
   
   Dim sFilename As String _
      , sPath As String _
      , sPathFile As String
      
   Dim wbNew As Workbook
            
   With ActiveWorkbook
      sFilename = .Name
      sPath = .Path & "\"
   End With
   
   sFilename = Replace(Replace(sFilename, ".", "_" & Format(Now, "yymmdd_hhnn") & "."), ".xlsm", ".xlsx")
   
   sPathFile = sPath & sFilename
   
   If Dir(sPathFile) <> "" Then
      'delete file to overwrite
      Kill sPathFile
   End If
   
   'copy all information on the sheet
   ActiveSheet.Cells.Copy
    
   'create workbook
   Set wbNew = Workbooks.Add

   Selection.PasteSpecial Paste:=xlPasteValues ', Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   ActiveSheet.Range("A1").Select

   'save file and close
   wbNew.Close True, sPathFile
   
   Range("A1").Select
   
   'comment this line to run this with a scheduler
   MsgBox "Done creating static workbook", , "Done"
   
Proc_Exit:
   On Error Resume Next
   'release object variables
   If Not wbNew Is Nothing Then
      wbNew.Close False
      Set wbNew = Nothing
   End If
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SaveStaticFile"

   Resume Proc_Exit
   Resume
End Sub

Open in new window

assumption is that what you want to copy is the active sheet when this is run

if so, then we can discuss scheduling it
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
answered your loaded question!

attached:

This is a ZIP file with:
1. an Excel Workbook with the linked data and code
-- Refresh_Scrape.xlsm
2. Steps_Using_Windows_Task_Scheduler_by_crystal.docx
-- a Word Document with steps explaining how to use the Windows Task Scheduler to open the workbook.  It also has screen shots.

here is the code (there is also a userform):

'ThisWorkbook:

Private Sub Workbook_Open()
'strive4peace 151115
   gEarliestTime = Now + TimeSerial(0, 0, 10) 'start 10 seconds after workbook opened
   gLatestTime = Now + TimeSerial(0, 0, 30) 'latest time is 30 seconds after workbook opened
   Application.OnTime EarliestTime:=gEarliestTime _
      , latestTime:=gLatestTime _
      , Procedure:="RefreshMyWorkbook" _
      , Schedule:=True
   frm_Cancel.Show
      
End Sub

'-----------------------------------------------

'userform: frm_Cancel

'pops up when the workbook opens in case you want to cancel the automatic process

'code behind form:

Private Sub cmd_Quit_Click()
'151115 strive4peace
   Call QuitExcel
End Sub

Private Sub cmdCancel_Click()
'151115 strive4peace
'cancel workbook ontime event
   Call CancelOnTime
   Unload Me
End Sub

'-----------------------------------------------

'module: mod_RefreshAndSave

Option Explicit

Public gEarliestTime _
   , gLatestTime

Public Sub CancelOnTime()
'strive4peace 151115
   Application.OnTime EarliestTime:=gEarliestTime _
      , Procedure:="RefreshMyWorkbook" _
      , Schedule:=False
End Sub

Public Sub QuitExcel()
   'quit excel and don't save
   ActiveWorkbook.Saved = True
   Application.Quit
End Sub

Sub RefreshMyWorkbook(Optional piSeconds As Integer = 10)
'strive4peace 151115
   ActiveWorkbook.RefreshAll
   'wait for specified number of seconds
   If Application.Wait(Now + TimeSerial(0, 0, piSeconds)) Then
      Call SaveStaticFile
   End If
   Call QuitExcel
End Sub


Sub SaveStaticFile()
'151115, strive4peace

   On Error GoTo Proc_Err
   
   Dim sFilename As String _
      , sPath As String _
      , sPathFile As String
      
   Dim wbNew As Workbook
            
   With ActiveWorkbook
      sFilename = .Name
      sPath = .Path & "\"
   End With
   
   sFilename = Replace(Replace(sFilename, ".", "_" & Format(Now, "yymmdd_hhnn") & "."), ".xlsm", ".xlsx")
   
   sPathFile = sPath & sFilename
   
   If Dir(sPathFile) <> "" Then
      'delete file to overwrite
      Kill sPathFile
   End If
   
   'copy all information on the sheet
   Sheets(1).Cells.Copy
    
   'create workbook
   Set wbNew = Workbooks.Add

   Selection.PasteSpecial Paste:=xlPasteValues ', Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   ActiveSheet.Range("A1").Select

   'save file and close
   wbNew.Close True, sPathFile
   
   Range("A1").Select
   
   'comment this line to run this with a scheduler
'   MsgBox "Done creating static workbook", , "Done"
   
Proc_Exit:
   On Error Resume Next
   'release object variables
   If Not wbNew Is Nothing Then
      wbNew.Close False
      Set wbNew = Nothing
   End If
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SaveStaticFile"

   Resume Proc_Exit
   Resume
End Sub

Open in new window

Steps_Using_Windows_Task_Scheduler_.docx
Refresh_Scrape.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for strive4peace's comment #a41261311

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.