EXCEL:Save work book in a file & include the value of a cell with date 2

[coolcarls]

So I have.
Sub SaveThis()
ActiveWorkbook.SaveCopyAs "C:\Projects\LIST" & "\List-" & Sheet2.Cells(11, 16)
The value in Sheet2.Cells(11, 16) is a date. The cell is formatted to date, but when the file is save instead of it reading 8-11-04 it reads 36748
Also, it doesn't save the Excel icon, it looks like it doesn't recognize the file type although it does open right up in Excel.
Thanks for your time,
Carl

 

[kjv1611]

for the excel format, make sure to add:

& ".xls"

on the end...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

For the date part, possibly try adding this function into your code for your save-as name:

FormatDateTime Function


Description

Returns an expression formatted as a date or time.

Syntax

FormatDateTime(Date[,NamedFormat])

The FormatDateTime function syntax has these parts:

Part Description
Date Required. Date expression to be formatted.
NamedFormat Optional. Numeric value that indicates the date/time format used. If omitted, vbGeneralDate is used.



Settings

The NamedFormat argument has the following settings:

Constant Value Description
vbGeneralDate 0 Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.
vbLongDate 1 Display a date using the long date format specified in your computer's regional settings.
vbShortDate 2 Display a date using the short date format specified in your computer's regional settings.
vbLongTime 3 Display a time using the time format specified in your computer's regional settings.
vbShortTime 4 Display a time using the 24-hour format (hh:mm).

from Excel VBA help file.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[coolcarls]

The & ".xls" worked great. How simple. The date part, I too saw that in the help file but it didn't make much sense and I still can't get it to work. I'll keep messing around with it though.
Thank you SO much for your help.
Carl

 

[kjv1611]

If I get a chance in the mean time, I'll try to fiddle around with that. I have a date saved via vba code to workbooks created each day, but I am just saving the current date. I'm assuming you are wanting to save the specific date from your cell in excel, and not just today's date. If that is the case, is there a particular formula you are using to get that date, or is it user entered? If a specific formula, then I would guess you could include that in the Save-as command to set the date without referencing the Cell value at all.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[coolcarls]

The date is actually the date the document is created and is user entered, but sometimes we have to use a different date, so it'll have to be user entered date.
Here's another one too

Private Sub CommandButton1_Click()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

This is part of what I need, but when the SaveAs box opens, I need it to be already in a certain file in the C drive. So when the sub is run the address it defaults to will be C:\List\Document\Test Results\Aug

I think that if I save it there one time it may come back to there every time until it is saved somewhere else, but not sure.

Anyway,
Thanks again

 

[asrisk]

For the date formatting, try (after the last & in your expression):

format(Sheet2.Cells(11, 16), "m-dd-yy")

the string "m-dd-yy" tells it how to compose the month, day and year into a string. You could use yyyy instead of yy if you want 2004 instead of 04.

Look up the format function in help - it can do all sorts of useful things for you.

For your second problem, have a look at "GetSaveAsFilename". This opens a save-as dialog which you can "seed" with an initial filename, but doesn't actually save - it just returns the path & filename as a string. You can then add your date stuff to the string, and use ActiveWorkbook.SaveCopyAs

 

[coolcarls]

Thanks a million!!!!!