Solved

Save Copy As in Excel

Posted on 2001-08-31
5
423 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

860 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