Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving two file names? One with no extension?
I am having some dificulty finding the error in my code below. I can create
the file name I wish, except, I create second file name with no extension AND (you knew there was more, go ahead say it) the file name I want is like this example. "Original_filename.xlsNew_filename.xls" I do not know how to get rid of the duplicate file and the "xls" extension for the Original file name. My code: ' Macro recorded 4/20/2005 by RStanich ' Dim sPath As String, sNewFile As String Dim vMyFileName As Variant Dim sStartingName As String Sheets("Sheet2").Select Application.Run "CheckForOpenFile" Application.Run "ToggleControlChartBar" 'Get Current File Name 'Create File name Application.DisplayAlerts = False Range("C7").Select ActiveCell.FormulaR1C1 = "=R[-5]C& ""-"" &R[-5]C[1]" sPath = "c:\Modern_Excel_FIRs\" sFileName = ActiveCell vMyFileName = ActiveWorkbook.Name sNewFile = sPath & vMyFileName & "_" & sFileName & ".xls" Application.StatusBar = "Saving New FIR File" Sheets("Sheet1").Select Sheets("Sheet1").Copy ActiveWorkbook.SaveCopyAs sNewFile If sNewFile < "False" Then ActiveWorkbook.SaveAs sNewFile End If ActiveWorkbook.Close sNewFile Sheets("Sheet2").Select Application.DisplayAlerts = True Application.Run "ToggleControlChartBar" Application.StatusBar = " Ready " End Sub As always, your help is greatly appreciated. Best regards Rick S. PHX AZ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving two file names? One with no extension?
Rick
I am not sure I completely understood your question but if you are wanting to change this: "Original_filename.xlsNew_filename.xls" to: "Original_filename_New_filename.xls" then change the line in your code that sets the variable vMyFileName from: vMyFileName = ActiveWorkbook.Name to : vMyFileName = Left(ActiveWorkbook.Name, _ InStr(1, ActiveWorkbook.Name, ".") - 1) I also noticed that you Set the StatusBar to " Ready" at the end of your code. To properly render control of the StatusBar back to Excel you should change this statement to Application.StatusBar = False. Hope this helps Rowan "Rick S." wrote: I am having some dificulty finding the error in my code below. I can create the file name I wish, except, I create second file name with no extension AND (you knew there was more, go ahead say it) the file name I want is like this example. "Original_filename.xlsNew_filename.xls" I do not know how to get rid of the duplicate file and the "xls" extension for the Original file name. My code: ' Macro recorded 4/20/2005 by RStanich ' Dim sPath As String, sNewFile As String Dim vMyFileName As Variant Dim sStartingName As String Sheets("Sheet2").Select Application.Run "CheckForOpenFile" Application.Run "ToggleControlChartBar" 'Get Current File Name 'Create File name Application.DisplayAlerts = False Range("C7").Select ActiveCell.FormulaR1C1 = "=R[-5]C& ""-"" &R[-5]C[1]" sPath = "c:\Modern_Excel_FIRs\" sFileName = ActiveCell vMyFileName = ActiveWorkbook.Name sNewFile = sPath & vMyFileName & "_" & sFileName & ".xls" Application.StatusBar = "Saving New FIR File" Sheets("Sheet1").Select Sheets("Sheet1").Copy ActiveWorkbook.SaveCopyAs sNewFile If sNewFile < "False" Then ActiveWorkbook.SaveAs sNewFile End If ActiveWorkbook.Close sNewFile Sheets("Sheet2").Select Application.DisplayAlerts = True Application.Run "ToggleControlChartBar" Application.StatusBar = " Ready " End Sub As always, your help is greatly appreciated. Best regards Rick S. PHX AZ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving two file names? One with no extension?
Thank you Rowan, for both answers ;)
Now I am onto a new problem, one I did not see coming. The new workbook I am creating has links back to the original workbook. Can I purge the links from the new workbook? "Rowan" wrote: Rick I am not sure I completely understood your question but if you are wanting to change this: "Original_filename.xlsNew_filename.xls" to: "Original_filename_New_filename.xls" then change the line in your code that sets the variable vMyFileName from: vMyFileName = ActiveWorkbook.Name to : vMyFileName = Left(ActiveWorkbook.Name, _ InStr(1, ActiveWorkbook.Name, ".") - 1) I also noticed that you Set the StatusBar to " Ready" at the end of your code. To properly render control of the StatusBar back to Excel you should change this statement to Application.StatusBar = False. Hope this helps Rowan "Rick S." wrote: I am having some dificulty finding the error in my code below. I can create the file name I wish, except, I create second file name with no extension AND (you knew there was more, go ahead say it) the file name I want is like this example. "Original_filename.xlsNew_filename.xls" I do not know how to get rid of the duplicate file and the "xls" extension for the Original file name. My code: ' Macro recorded 4/20/2005 by RStanich ' Dim sPath As String, sNewFile As String Dim vMyFileName As Variant Dim sStartingName As String Sheets("Sheet2").Select Application.Run "CheckForOpenFile" Application.Run "ToggleControlChartBar" 'Get Current File Name 'Create File name Application.DisplayAlerts = False Range("C7").Select ActiveCell.FormulaR1C1 = "=R[-5]C& ""-"" &R[-5]C[1]" sPath = "c:\Modern_Excel_FIRs\" sFileName = ActiveCell vMyFileName = ActiveWorkbook.Name sNewFile = sPath & vMyFileName & "_" & sFileName & ".xls" Application.StatusBar = "Saving New FIR File" Sheets("Sheet1").Select Sheets("Sheet1").Copy ActiveWorkbook.SaveCopyAs sNewFile If sNewFile < "False" Then ActiveWorkbook.SaveAs sNewFile End If ActiveWorkbook.Close sNewFile Sheets("Sheet2").Select Application.DisplayAlerts = True Application.Run "ToggleControlChartBar" Application.StatusBar = " Ready " End Sub As always, your help is greatly appreciated. Best regards Rick S. PHX AZ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving two file names? One with no extension?
I think the easiest way to do this is to copy the cells and pastespecial as
values but there may be better ways. If you search the newsgroups for "Break Links" you will get loads of examples for different versions of Excel. Regards Rowan "Rick S." wrote: Thank you Rowan, for both answers ;) Now I am onto a new problem, one I did not see coming. The new workbook I am creating has links back to the original workbook. Can I purge the links from the new workbook? "Rowan" wrote: Rick I am not sure I completely understood your question but if you are wanting to change this: "Original_filename.xlsNew_filename.xls" to: "Original_filename_New_filename.xls" then change the line in your code that sets the variable vMyFileName from: vMyFileName = ActiveWorkbook.Name to : vMyFileName = Left(ActiveWorkbook.Name, _ InStr(1, ActiveWorkbook.Name, ".") - 1) I also noticed that you Set the StatusBar to " Ready" at the end of your code. To properly render control of the StatusBar back to Excel you should change this statement to Application.StatusBar = False. Hope this helps Rowan "Rick S." wrote: I am having some dificulty finding the error in my code below. I can create the file name I wish, except, I create second file name with no extension AND (you knew there was more, go ahead say it) the file name I want is like this example. "Original_filename.xlsNew_filename.xls" I do not know how to get rid of the duplicate file and the "xls" extension for the Original file name. My code: ' Macro recorded 4/20/2005 by RStanich ' Dim sPath As String, sNewFile As String Dim vMyFileName As Variant Dim sStartingName As String Sheets("Sheet2").Select Application.Run "CheckForOpenFile" Application.Run "ToggleControlChartBar" 'Get Current File Name 'Create File name Application.DisplayAlerts = False Range("C7").Select ActiveCell.FormulaR1C1 = "=R[-5]C& ""-"" &R[-5]C[1]" sPath = "c:\Modern_Excel_FIRs\" sFileName = ActiveCell vMyFileName = ActiveWorkbook.Name sNewFile = sPath & vMyFileName & "_" & sFileName & ".xls" Application.StatusBar = "Saving New FIR File" Sheets("Sheet1").Select Sheets("Sheet1").Copy ActiveWorkbook.SaveCopyAs sNewFile If sNewFile < "False" Then ActiveWorkbook.SaveAs sNewFile End If ActiveWorkbook.Close sNewFile Sheets("Sheet2").Select Application.DisplayAlerts = True Application.Run "ToggleControlChartBar" Application.StatusBar = " Ready " End Sub As always, your help is greatly appreciated. Best regards Rick S. PHX AZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seeing Text File Names when Saving Excel Files | New Users to Excel | |||
Seeing existing file names when saving | Excel Discussion (Misc queries) | |||
Saving CSV file with TXT extension | Excel Discussion (Misc queries) | |||
Saving file names in a different color | Excel Discussion (Misc queries) | |||
Saving file names in a different color | Excel Discussion (Misc queries) |