Macro to Save (As) a file
 

Quck question I hope. Using Excel 2007 - Is there code to process a Save as?
I need to include the statement to save a file as the valus contained in
cell AE2 (This is a constant). I copied and pasted while recording - however
now it saves all files as the value that was in AE2 (this is a merged cell
containing AE2-AN2 but can be unmerged easily) when the Macro was created....


Range("AE2:AN2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CAS-7403-477"
ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\CAS-7403-477.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Help????

Macro to Save (As) a file
 

Application.CutCopyMode = False

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--
Regards,
Tom Ogilvy
"betany70" wrote:

Quck question I hope. Using Excel 2007 - Is there code to process a Save as?
I need to include the statement to save a file as the valus contained in
cell AE2 (This is a constant). I copied and pasted while recording - however
now it saves all files as the value that was in AE2 (this is a merged cell
containing AE2-AN2 but can be unmerged easily) when the Macro was created....


Range("AE2:AN2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CAS-7403-477"
ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\CAS-7403-477.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Help????

Macro to Save (As) a file
 

Thanks Tom- works like a charm in Excel 2007 which is what I am using -
however when sharing the file with 2003 users I get an error...

Method 'Save As' of object _Workbook failed

Is there a way to avoid it? I modified the commend slightly (which is
probably the issue!) See below:

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
"Tom Ogilvy" wrote:


Application.CutCopyMode = False

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--
Regards,
Tom Ogilvy
"betany70" wrote:

Quck question I hope. Using Excel 2007 - Is there code to process a Save as?
I need to include the statement to save a file as the valus contained in
cell AE2 (This is a constant). I copied and pasted while recording - however
now it saves all files as the value that was in AE2 (this is a merged cell
containing AE2-AN2 but can be unmerged easily) when the Macro was created....


Range("AE2:AN2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CAS-7403-477"
ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\CAS-7403-477.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Help????

Macro to Save (As) a file
 

I am guessing that xl2003 doesn't understand the constant xlExcel8

You might need to have two lines of code

if application.Version 11 then

ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
else

ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlWorkbookNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End if

--
Regards,
Tom Ogilvy


"betany70" wrote:

Thanks Tom- works like a charm in Excel 2007 which is what I am using -
however when sharing the file with 2003 users I get an error...

Method 'Save As' of object _Workbook failed

Is there a way to avoid it? I modified the commend slightly (which is
probably the issue!) See below:

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
"Tom Ogilvy" wrote:


Application.CutCopyMode = False

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--
Regards,
Tom Ogilvy
"betany70" wrote:

Quck question I hope. Using Excel 2007 - Is there code to process a Save as?
I need to include the statement to save a file as the valus contained in
cell AE2 (This is a constant). I copied and pasted while recording - however
now it saves all files as the value that was in AE2 (this is a merged cell
containing AE2-AN2 but can be unmerged easily) when the Macro was created....


Range("AE2:AN2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CAS-7403-477"
ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\CAS-7403-477.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Help????

Macro to Save (As) a file
 

Perfect! Thanks again!

"Tom Ogilvy" wrote:

I am guessing that xl2003 doesn't understand the constant xlExcel8

You might need to have two lines of code

if application.Version 11 then

ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
else

ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlWorkbookNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End if

--
Regards,
Tom Ogilvy


"betany70" wrote:

Thanks Tom- works like a charm in Excel 2007 which is what I am using -
however when sharing the file with 2003 users I get an error...

Method 'Save As' of object _Workbook failed

Is there a way to avoid it? I modified the commend slightly (which is
probably the issue!) See below:

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & "Attendance.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
"Tom Ogilvy" wrote:


Application.CutCopyMode = False

ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\" & _
Range("AE2").Value & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--
Regards,
Tom Ogilvy
"betany70" wrote:

Quck question I hope. Using Excel 2007 - Is there code to process a Save as?
I need to include the statement to save a file as the valus contained in
cell AE2 (This is a constant). I copied and pasted while recording - however
now it saves all files as the value that was in AE2 (this is a merged cell
containing AE2-AN2 but can be unmerged easily) when the Macro was created....


Range("AE2:AN2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CAS-7403-477"
ChDir "C:\Datatel Merges"
ActiveWorkbook.SaveAs Filename:="C:\Datatel Merges\CAS-7403-477.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Help????