| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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???? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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???? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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???? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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???? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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???? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
| Macro to save Excel file with date and time in the file name? | Excel Programming | |||
| Macro Save File (Unique file name) | Excel Worksheet Functions | |||
| Macro to insert values from a file and save another sheet as a .txt file | Excel Programming | |||
| Automate open file, update links, run macro, close and save file | Excel Programming | |||
Hybrid Mode
