Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
On Thu, 10 May 2007 12:10:02 -0700, Oldjay
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@")
"Oldjay" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
When I run macro it fails at
Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@") Runtime error 424 Object required "Mike" wrote: Set DateSave = Format(Range("E213"), "[$-409]mm-dd-yy;@") "Oldjay" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
& 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" wrote: On Thu, 10 May 2007 12:10:02 -0700, Oldjay wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
On Thu, 10 May 2007 13:31:03 -0700, Oldjay
wrote: & 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
I copied your code over my code and got a compile error - Expected: list
separator or) "Ron Rosenfeld" wrote: On Thu, 10 May 2007 13:31:03 -0700, Oldjay wrote: & 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
On Thu, 10 May 2007 15:05:00 -0700, Oldjay
wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
I put in the missing ")" and it worked
Thanks "Oldjay" wrote: I copied your code over my code and got a compile error - Expected: list separator or) "Ron Rosenfeld" wrote: On Thu, 10 May 2007 13:31:03 -0700, Oldjay wrote: & 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format problem
On Thu, 10 May 2007 17:38:00 -0700, Oldjay
wrote: I put in the missing ")" and it worked Thanks You're welcome. Glad to help --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Format Problem - Date Overrides Format | Excel Discussion (Misc queries) | |||
General Format to Custom format problem | Excel Discussion (Misc queries) | |||
format problem | Excel Programming | |||
Format Problem | Excel Discussion (Misc queries) | |||
Format problem | Excel Discussion (Misc queries) |