Macro to Save (As) a file

G

Guest

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????
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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
 
G

Guest

Perfect! Thanks again!

Tom Ogilvy said:
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
Click to expand...
 
G

gsrik

Joined
Jun 24, 2008
Messages
2
Reaction score
0
saving xls as a txt utf-8

Hello Tom,


Not sure if you'll read this soon... but

I am trying to 'save as' an xls file to a text (tab delimited) file of utf-8 encoding. How would you modify the macro you suggested below to make that happen?

thanks
srik

=?Utf-8?B?YmV0YW55NzA=?= said:
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????
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Save As File Format 1
Save as marco 3
Simplify save code 11
Two digit dates and two digit days 3
Save with ref. to cell A1 2
Save as Read only 2
Macro syntax "Save As" with filename from cell ref. 1
Input to Save As Macro 9

Top