Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Save Copy As in Excel

Posted on 2001-08-31
5
Medium Priority
?
464 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

722 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