seasons712
asked on
Save Copy As in Excel
Dear All,
I have a Excel file that want to export/save as to .csv format. I have already create a custom button that is linked to my module. When the user click the custom button, then the current worksheet will be save copy as the indicated directory. It is no problem for me to create the custom button. But I dont know how to coding the "save copy as" part.
For example:
Public Sub SaveMyFile()
Worksheets(1).SaveCopyAs "C:\test1.csv"
End Sub
Once I click the button and then the file(.xls) is save copy as a "csv" file, however, there are lots of garbage in the file, it is not necessary for me. Has any another solution for me?
Many thanks!
Best Regards,
Emily
I have a Excel file that want to export/save as to .csv format. I have already create a custom button that is linked to my module. When the user click the custom button, then the current worksheet will be save copy as the indicated directory. It is no problem for me to create the custom button. But I dont know how to coding the "save copy as" part.
For example:
Public Sub SaveMyFile()
Worksheets(1).SaveCopyAs "C:\test1.csv"
End Sub
Once I click the button and then the file(.xls) is save copy as a "csv" file, however, there are lots of garbage in the file, it is not necessary for me. Has any another solution for me?
Many thanks!
Best Regards,
Emily
ASKER
Hi Tom,
Thanks for your solution, but I want to keep the current file, and just save the copy of file in another place. Has any better solution?
Best Regards,
Emily
Thanks for your solution, but I want to keep the current file, and just save the copy of file in another place. Has any better solution?
Best Regards,
Emily
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much, Peter!
Hi again
You may want to delete the xls file that is copied each time. Add the string :
FileSystem.Kill ("C:\test1.xls")
to the end of the procedure.
Just make sure that you do not delete the wrong file. So when you do the copy process you might want to add TMP to the filename like this :
Public Sub SaveMyFile()
Dim wbMyBook As Workbook
Dim wbMyCSV As Workbook
Dim strTMPPath As String
Dim strTMPFileName As String
Set wbMyBook = ThisWorkbook 'Set a reference to the current workbook
strTMPFileName = "TMP" & wbMyBook.Name 'set the TMP name to be used
strTMPPath = "C:\" 'or some other path
wbMyBook.SaveCopyAs strTMPPath & strTMPFileName 'Save a copy of this workbook
Set wbMyCSV = Workbooks.Open(Filename:=s
wbMyCSV.Worksheets(1).Save
FileFormat:=xlCSV 'Then save it as the fileformat you need
wbMyCSV.Close SaveChanges:=False 'And then close it again. You do not need to save it
FileSystem.Kill (strTMPPath & strTMPFileName) 'Delete the TMP file
Set wbMyBook = Nothing
Set wbMyCSV = Nothing
End Sub
Peter
You may want to delete the xls file that is copied each time. Add the string :
FileSystem.Kill ("C:\test1.xls")
to the end of the procedure.
Just make sure that you do not delete the wrong file. So when you do the copy process you might want to add TMP to the filename like this :
Public Sub SaveMyFile()
Dim wbMyBook As Workbook
Dim wbMyCSV As Workbook
Dim strTMPPath As String
Dim strTMPFileName As String
Set wbMyBook = ThisWorkbook 'Set a reference to the current workbook
strTMPFileName = "TMP" & wbMyBook.Name 'set the TMP name to be used
strTMPPath = "C:\" 'or some other path
wbMyBook.SaveCopyAs strTMPPath & strTMPFileName 'Save a copy of this workbook
Set wbMyCSV = Workbooks.Open(Filename:=s
wbMyCSV.Worksheets(1).Save
FileFormat:=xlCSV 'Then save it as the fileformat you need
wbMyCSV.Close SaveChanges:=False 'And then close it again. You do not need to save it
FileSystem.Kill (strTMPPath & strTMPFileName) 'Delete the TMP file
Set wbMyBook = Nothing
Set wbMyCSV = Nothing
End Sub
Peter
You should use the SaveAs method.
Try this:
Public Sub SaveMyFile()
Worksheets(1).SaveAs Filename:="C:\test1.csv", _
FileFormat:=xlCSV
End Sub
Peter