Using Macro to Save Copy of File to New Location
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! |
Using Macro to Save Copy of File to New Location
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 |
Using Macro to Save Copy of File to New Location
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 |
Using Macro to Save Copy of File to New Location
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 |
All times are GMT +1. The time now is 02:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com