Solved

Save filename with timestamp

Posted on 2008-10-21
2
917 Views
Last Modified: 2012-05-05
This needs to run as a schedualled task:

This code works while logged on:



Sub Original_Create_and_Send()

    On Error Resume Next
    Application.DisplayAlerts = False
   
    'opens Word template
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = False 'hides MS word
    Set wrdDoc = wrdApp.Documents.Open("C:\Reports\Template.doc")

    'copy to Word
    Sheets("Final Report").Select
    Range("A1:G45").CopyPicture ' creates report as a bitmap
    wrdApp.Selection.Paste
    Application.CutCopyMode = False
   
    'Save word
    Dim sFileName As String, sPath As String
    sPath = "C:\Reports\Archive\" 'change path here if you want
    'adds a timestamp to the file name
    sFileName = "VoIP Status " & Format(Now(), "mm_dd_yyyy hh mm AMPM")
    wrdDoc.SaveAs (sPath & sFileName)

But it does NOT work while logged off

If I change this:
    sFileName = "VoIP Status " & Format(Now(), "mm_dd_yyyy hh mm AMPM")
To this:
    sFileName = "VoIP Status "

It DOES work while logged off

So why is the
& Format(Now(), "mm_dd_yyyy hh mm AMPM")
Part not working while logged off??

Thank you!!
0
Comment
Question by:TelMaco
2 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 total points
ID: 22769993
Replace this line....

sFileName = "VoIP Status " & Format(Now(), "mm_dd_yyyy hh mm AMPM")

With this...

sFileName = "VoIP Status " & Format(Now(), "mm_dd_yyyy hh mm AM/PM") &".xls"

Saurabh...
0
 

Author Comment

by:TelMaco
ID: 22770346
One note - It's a Word File, so  it's : & ".Doc"

Anyways,

Why does it not require the  "& ".Doc"" if I am logged on ?!?!?

In any case it works like a charm now.  Thank you thank you thank you!!

Stephen
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question