Append date to auto-generated Excel sheet

[jamesftoland]

Hi

I have a macro that translates table data into a newly created Excel
worksheet, and saves the name of the xls in the format of the name of
the word doc that spawned it, save for losing the .doc extension and
appending the the designation "_log_" and the current date.

Example: Word doc titled "VBA Question.doc" generates spreadsheet
called "VBA Question_log_01-13-06.xls

Here is the code I used to do this, which seems like it should work,
yet obviously does not:

'extract name of current active document and specify the name of excel
doc to save to
cwfile = ActiveDocument.FullName 'get current file name and path
cwfile = Left(cwfile, Len(cwfile) - 4) 'get rid of extension
formattedDate = Left(Date, 2) & "-" & Mid(Date, 4, 2) & "-" &
Right(Date, 4) 'get date and format
cwfile = cwfile & "_log_" & formattedDate & ".xls" 'full excel filename

It gets the name, but garbles up in the date extension, and misses the
..xls extension altogether.

What obvious mistake am I making? Or is there a cleaner way to do this?

Thanks, I love this place

 

[Doug Robbins - Word MVP]

cwfile = cwfile & "_log_" & Format(Date, "MM-dd-yy") & ".xls"

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jamesftoland]

Doug,

Worked like a champ.

You are a rockstar.

Thanks!