Solved

Save Copy As in Excel

Posted on 2001-08-31
5
415 Views
Last Modified: 2008-02-07
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
0
Comment
Question by:seasons712
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:forsbom
ID: 6443896
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
 

Author Comment

by:seasons712
ID: 6443952
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
 
LVL 3

Accepted Solution

by:
forsbom earned 50 total points
ID: 6443969
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
 

Author Comment

by:seasons712
ID: 6444005
Thank you very much, Peter!
0
 
LVL 3

Expert Comment

by:forsbom
ID: 6444026
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question