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
seasons712Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
forsbomConnect With a Mentor Commented:
Hi again
Try this:
Public Sub SaveMyFile()
    Dim wbMyBook    As Workbook
    Dim wbMyCSV    As Workbook
    Set wbMyBook = ThisWorkbook 'Set a reference to the current workbook
    wbMyBook.SaveCopyAs "C:\test1.xls" 'Save a copy of this workbook
    Set wbMyCSV = Workbooks.Open(Filename:="C:\test1.xls") 'Open it again
    wbMyCSV.Worksheets(1).SaveAs Filename:="C:\test1.csv", _
    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
End Sub

I hope this helps  :-)

Peter
0
 
forsbomCommented:
Hi
You should use the SaveAs method.
Try this:
Public Sub SaveMyFile()
   Worksheets(1).SaveAs Filename:="C:\test1.csv", _
     FileFormat:=xlCSV
End Sub


Peter
0
 
seasons712Author Commented:
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
0
 
seasons712Author Commented:
Thank you very much, Peter!
0
 
forsbomCommented:
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:=strTMPPath & strTMPFileName) 'Open it again
    wbMyCSV.Worksheets(1).SaveAs Filename:="C:\test1.csv", _
    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
0
All Courses

From novice to tech pro — start learning today.