Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a variable filename
Good day all
I am trying to write a MACRO in Excel 2007 running on XP to automate a file save as follows: I want to save the currently active worksheet as a new workbook *.xls or *.xlsb where €ś*€ť is a volatile filename contained in a cell on the currently active worksheet, e.g. €śSeptember€ť, €śOctober€ť €¦. Can anyone help with the code? Regards Wes_A |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a variable filename
Anything wrong with your previous post...?
Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = ActiveSheet.Range("A1").Text & ".xls" 'If it is same as the current workbook flToSave = ActiveWorkbook.Path OR 'If it is a different path assign to that variable flToSave = "m:\contract" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat OR ActiveWorkbook.SaveCopyAs flToSave & "\" & flName If this post helps click Yes --------------- Jacob Skaria "Wes_A" wrote: Good day all I am trying to write a MACRO in Excel 2007 running on XP to automate a file save as follows: I want to save the currently active worksheet as a new workbook *.xls or *.xlsb where €ś*€ť is a volatile filename contained in a cell on the currently active worksheet, e.g. €śSeptember€ť, €śOctober€ť €¦. Can anyone help with the code? Regards Wes_A |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a variable filename
Hi Jacob
Thanks, it was fine but I needed the month as the filename. Also it was saved in a format not easily recognised by the excel when trying to open it later. Thanks again for the help which is much appreciated. "Jacob Skaria" wrote: Anything wrong with your previous post...? Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = ActiveSheet.Range("A1").Text & ".xls" 'If it is same as the current workbook flToSave = ActiveWorkbook.Path OR 'If it is a different path assign to that variable flToSave = "m:\contract" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat OR ActiveWorkbook.SaveCopyAs flToSave & "\" & flName If this post helps click Yes --------------- Jacob Skaria "Wes_A" wrote: Good day all I am trying to write a MACRO in Excel 2007 running on XP to automate a file save as follows: I want to save the currently active worksheet as a new workbook *.xls or *.xlsb where €ś*€ť is a volatile filename contained in a cell on the currently active worksheet, e.g. €śSeptember€ť, €śOctober€ť €¦. Can anyone help with the code? Regards Wes_A |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a variable filename
Jacob, my code is now as follows:
Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = ActiveSheet.Range("$F$3").Text & ".xlsb" flToSave = "c:\INVENTORY\HISTORIC\DATA" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat When trying to run it I get an error saying that file cannot be accessed since it may not exist. The filename being looked for changes each time but always looks like: F2E6E900 or OE2BE900 or OE00F900 etc Any ideas? "Jacob Skaria" wrote: Anything wrong with your previous post...? Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = ActiveSheet.Range("A1").Text & ".xls" 'If it is same as the current workbook flToSave = ActiveWorkbook.Path OR 'If it is a different path assign to that variable flToSave = "m:\contract" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat OR ActiveWorkbook.SaveCopyAs flToSave & "\" & flName If this post helps click Yes --------------- Jacob Skaria "Wes_A" wrote: Good day all I am trying to write a MACRO in Excel 2007 running on XP to automate a file save as follows: I want to save the currently active worksheet as a new workbook *.xls or *.xlsb where €ś*€ť is a volatile filename contained in a cell on the currently active worksheet, e.g. €śSeptember€ť, €śOctober€ť €¦. Can anyone help with the code? Regards Wes_A |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a variable filename
Check whether your path is valid. "c:\INVENTORY\HISTORIC\DATA" or other wise
it should work fine...I tried the below.. Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flExtn = Mid(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) flName = ActiveSheet.Range("F3") & flExtn flToSave = "c:" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat If this post helps click Yes --------------- Jacob Skaria "Wes_A" wrote: Jacob, my code is now as follows: Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = ActiveSheet.Range("$F$3").Text & ".xlsb" flToSave = "c:\INVENTORY\HISTORIC\DATA" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat When trying to run it I get an error saying that file cannot be accessed since it may not exist. The filename being looked for changes each time but always looks like: F2E6E900 or OE2BE900 or OE00F900 etc Any ideas? "Jacob Skaria" wrote: Anything wrong with your previous post...? Dim flToSave As String Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = ActiveSheet.Range("A1").Text & ".xls" 'If it is same as the current workbook flToSave = ActiveWorkbook.Path OR 'If it is a different path assign to that variable flToSave = "m:\contract" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat OR ActiveWorkbook.SaveCopyAs flToSave & "\" & flName If this post helps click Yes --------------- Jacob Skaria "Wes_A" wrote: Good day all I am trying to write a MACRO in Excel 2007 running on XP to automate a file save as follows: I want to save the currently active worksheet as a new workbook *.xls or *.xlsb where €ś*€ť is a volatile filename contained in a cell on the currently active worksheet, e.g. €śSeptember€ť, €śOctober€ť €¦. Can anyone help with the code? Regards Wes_A |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a Variable Filename to a Constant Filename | Excel Programming | |||
Saving FileName | Excel Programming | |||
Saving with date in filename | Excel Programming | |||
Saving filename same as import filename | Excel Programming | |||
Saving Cell value as filename | Excel Programming |