Format problem

[Guest]

When I try to save the file it fails because DateSave is formated 5/1/07
even thou E213 is formated 5-1-07

Is there any way to force the DateSave to 5-1-07 and also can the existing
code be simplified J

Private Sub CommandButton4_Click() 'Saves wookbook and exits

Dim NameSave As Range
Dim DateSave As Range

Sheets("Checkbook").Select

Set NameSave = Worksheets("Summary").Range("A2")
Set DateSave = Worksheets("Summaryk").Range("E213")

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("Bank Records.XLS").Activate

Application.GoTo Reference:="Database"
Selection.Copy

Windows("TempData.XLS").Activate
Range("A1").Select
ActiveSheet.Paste

BankRecords = InputBox("Please enter file name to save", "Bank Records
Backup", "C:\Bank Records\" & NameSave & "" & DateSave)
ActiveWorkbook.SaveAs Filename:=BankRecords + "xls"
ActiveWorkbook.Close

oldjay

 

[Don Guillett]

try
Set DateSave = format(Worksheets("Summaryk").Range("E213"),"m-d-yy")

 

[Ron Rosenfeld]

When I try to save the file it fails because DateSave is formated 5/1/07
even thou E213 is formated 5-1-07

Is there any way to force the DateSave to 5-1-07 and also can the existing
code be simplified J

Private Sub CommandButton4_Click() 'Saves wookbook and exits

Dim NameSave As Range
Dim DateSave As Range

Sheets("Checkbook").Select

Set NameSave = Worksheets("Summary").Range("A2")
Set DateSave = Worksheets("Summaryk").Range("E213")

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("Bank Records.XLS").Activate

Application.GoTo Reference:="Database"
Selection.Copy

Windows("TempData.XLS").Activate
Range("A1").Select
ActiveSheet.Paste

BankRecords = InputBox("Please enter file name to save", "Bank Records
Backup", "C:\Bank Records\" & NameSave & "" & DateSave)
ActiveWorkbook.SaveAs Filename:=BankRecords + "xls"
ActiveWorkbook.Close

oldjay

Try this:

....& NameSave & "" & DateSave.Text)


If that doesn't work, you can use the Format function to ensure DateSave is in
the format you want in the file name.
--ron

 

[Guest]

Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@")

 

[Guest]

When I run macro it fails at
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@")

Runtime error 424
Object required

Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@")

When I try to save the file it fails because DateSave is formated 5/1/07
even thou E213 is formated 5-1-07

Is there any way to force the DateSave to 5-1-07 and also can the existing
code be simplified J

Private Sub CommandButton4_Click() 'Saves wookbook and exits

Dim NameSave As Range
Dim DateSave As Range

Sheets("Checkbook").Select

Set NameSave = Worksheets("Summary").Range("A2")
Set DateSave = Worksheets("Summaryk").Range("E213")

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("Bank Records.XLS").Activate

Application.GoTo Reference:="Database"
Selection.Copy

Windows("TempData.XLS").Activate
Range("A1").Select
ActiveSheet.Paste

BankRecords = InputBox("Please enter file name to save", "Bank Records
Backup", "C:\Bank Records\" & NameSave & "" & DateSave)
ActiveWorkbook.SaveAs Filename:=BankRecords + "xls"
ActiveWorkbook.Close

oldjay

 

[Guest]

& NameSave & "" & DateSave.Text) didn't work 9same result)
Tried
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@") per Mikes reply
It returned Object required

 

[Ron Rosenfeld]

& NameSave & "" & DateSave.Text) didn't work 9same result)
Tried
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@") per Mikes reply
It returned Object required

I would expect that.

Did you try my alternate solution. Spelled out, it would give you:

BankRecords = InputBox("Please enter file name to save", "Bank Records
Backup", "C:\Bank Records\" & NameSave & "" & Format(DateSave,"m-d-yyyy")


It would be more robust than using DateSave.text as it would be independent of
the formatting in the cell.

I'm surprised that using DateSave.Text didn't work, though.

Note the following that shows how this works:

==============================
Sub foo()
Dim DateSave As Range
Set DateSave = [a1]

Debug.Print "With Format Command:",, Format(DateSave, "m-d-yyyy")
Debug.Print "from Cell formatted as " & DateSave.NumberFormat, DateSave.Text

End Sub
----------------------------

Immediate Window:

With Format Command: 1-2-2007
from Cell formatted as m-d-yyyy 1-2-2007

=====================================================


--ron

 

[Guest]

I copied your code over my code and got a compile error - Expected: list
separator or)

& NameSave & "" & DateSave.Text) didn't work 9same result)
Tried
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@") per Mikes reply
It returned Object required

I would expect that.

Did you try my alternate solution. Spelled out, it would give you:

BankRecords = InputBox("Please enter file name to save", "Bank Records
Backup", "C:\Bank Records\" & NameSave & "" & Format(DateSave,"m-d-yyyy")


It would be more robust than using DateSave.text as it would be independent of
the formatting in the cell.

I'm surprised that using DateSave.Text didn't work, though.

Note the following that shows how this works:

==============================
Sub foo()
Dim DateSave As Range
Set DateSave = [a1]

Debug.Print "With Format Command:",, Format(DateSave, "m-d-yyyy")
Debug.Print "from Cell formatted as " & DateSave.NumberFormat, DateSave.Text

End Sub
----------------------------

Immediate Window:

With Format Command: 1-2-2007
from Cell formatted as m-d-yyyy 1-2-2007

=====================================================


--ron

 

[Ron Rosenfeld]

I copied your code over my code and got a compile error - Expected: list
separator or)

You don't supply enough information for me to be sure what the problem is. I
suspect that in pasting the code, you may not be taking the line breaks induced
by the newsgroup and/or your reader into account. My code was not designed to
be just copied over your code.
--ron

 

[Guest]

I put in the missing ")" and it worked
Thanks

I copied your code over my code and got a compile error - Expected: list
separator or)

& NameSave & "" & DateSave.Text) didn't work 9same result)
Tried
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@") per Mikes reply
It returned Object required

I would expect that.

Did you try my alternate solution. Spelled out, it would give you:

BankRecords = InputBox("Please enter file name to save", "Bank Records
Backup", "C:\Bank Records\" & NameSave & "" & Format(DateSave,"m-d-yyyy")


It would be more robust than using DateSave.text as it would be independent of
the formatting in the cell.

I'm surprised that using DateSave.Text didn't work, though.

Note the following that shows how this works:

==============================
Sub foo()
Dim DateSave As Range
Set DateSave = [a1]

Debug.Print "With Format Command:",, Format(DateSave, "m-d-yyyy")
Debug.Print "from Cell formatted as " & DateSave.NumberFormat, DateSave.Text

End Sub
----------------------------

Immediate Window:

With Format Command: 1-2-2007
from Cell formatted as m-d-yyyy 1-2-2007

=====================================================


--ron

 

[Ron Rosenfeld]

I put in the missing ")" and it worked
Thanks

You're welcome. Glad to help
--ron