Solved

Save Copy As in Excel

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

713 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