Link to home
Start Free TrialLog in
Avatar of Michael Katz
Michael KatzFlag for United States of America

asked on

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
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

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
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.