Saving two file names? One with no extension?

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

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

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

 

[Guest]

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

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?

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

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