I'm trying to record, and modify the code of, a macro that will be used to
save a file to a new location using a different file type. The problem I'm
having is that the macro now uses the recorded file name for every file that
is copied, regardless of the original file name. The "save as" procedure I'm
doing is to allow the user to click a custom toolbar button in any file to
save that file to a specific location, in the CSV format, while preserving
the original file's name.

Is there code available to preserve the original file name while changing
the file format for any file I choose to save?

Thank you!

I'd do something like:

Option Explicit

Sub testme01()
Dim wks As Worksheet
Dim myName As String

With ActiveWorkbook
myName = .Name
If Right(LCase(myName), 4) = ".xls" Then
myName = Left(myName, Len(myName) - 4)
End If
Set wks = .ActiveSheet
End With

wks.Copy 'to a new workbook

With ActiveSheet.Parent 'that new workbook
'just overwrite any existing file
'and don't show the warning message
Application.DisplayAlerts = False

'use your favorite folder!
.SaveAs Filename:="C:\temp\" & myName & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With

End Sub


Chris Z wrote:

I'm trying to record, and modify the code of, a macro that will be used to
save a file to a new location using a different file type. The problem I'm
having is that the macro now uses the recorded file name for every file that
is copied, regardless of the original file name. The "save as" procedure I'm
doing is to allow the user to click a custom toolbar button in any file to
save that file to a specific location, in the CSV format, while preserving
the original file's name.

Is there code available to preserve the original file name while changing
the file format for any file I choose to save?

Thank you!

--

Dave Peterson

I finally got the time to try this and it worked great - thank you!

"Dave Peterson" wrote:

I'd do something like:

Option Explicit

Sub testme01()
Dim wks As Worksheet
Dim myName As String

With ActiveWorkbook
myName = .Name
If Right(LCase(myName), 4) = ".xls" Then
myName = Left(myName, Len(myName) - 4)
End If
Set wks = .ActiveSheet
End With

wks.Copy 'to a new workbook

With ActiveSheet.Parent 'that new workbook
'just overwrite any existing file
'and don't show the warning message
Application.DisplayAlerts = False

'use your favorite folder!
.SaveAs Filename:="C:\temp\" & myName & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With

End Sub


Chris Z wrote:

I'm trying to record, and modify the code of, a macro that will be used to
save a file to a new location using a different file type. The problem I'm
having is that the macro now uses the recorded file name for every file that
is copied, regardless of the original file name. The "save as" procedure I'm
doing is to allow the user to click a custom toolbar button in any file to
save that file to a specific location, in the CSV format, while preserving
the original file's name.

Is there code available to preserve the original file name while changing
the file format for any file I choose to save?

Thank you!

--

Dave Peterson

Glad it worked ok.

Chris Z wrote:

I finally got the time to try this and it worked great - thank you!

"Dave Peterson" wrote:

I'd do something like:

Option Explicit

Sub testme01()
Dim wks As Worksheet
Dim myName As String

With ActiveWorkbook
myName = .Name
If Right(LCase(myName), 4) = ".xls" Then
myName = Left(myName, Len(myName) - 4)
End If
Set wks = .ActiveSheet
End With

wks.Copy 'to a new workbook

With ActiveSheet.Parent 'that new workbook
'just overwrite any existing file
'and don't show the warning message
Application.DisplayAlerts = False

'use your favorite folder!
.SaveAs Filename:="C:\temp\" & myName & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With

End Sub


Chris Z wrote:

I'm trying to record, and modify the code of, a macro that will be used to
save a file to a new location using a different file type. The problem I'm
having is that the macro now uses the recorded file name for every file that
is copied, regardless of the original file name. The "save as" procedure I'm
doing is to allow the user to click a custom toolbar button in any file to
save that file to a specific location, in the CSV format, while preserving
the original file's name.

Is there code available to preserve the original file name while changing
the file format for any file I choose to save?

Thank you!

--

Dave Peterson


--

Dave Peterson