Excel - Export sheet in CSV Value

Posted on 2011-09-05
Medium Priority
Last Modified: 2012-06-27
I'm creating a Command Bar, with a function to export a sheet in CSV Value..

Currently i test two approach and both have their own problem

The Opt 1:
ActiveWorkbook.SaveAs Filename:="C:\" & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False

Open in new window

The Problem: Current Active Window file is become the csv file, i want to load the previous (the master) file ?

The Opt 2:
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\" & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True

Open in new window

The Problem: A Value in a Cell does not display properly because the value comes from a VBA's Procedure, the exported file comes like this:
"Wallpaper,Korean,Dream 2011-2012,Y,#NAME?"

#NAME? supposed to be a value, this problem not happened with the opt 1


Any idea on to find a solution ? i want it create a file and keep the current excel active.

Thanks !
Question by:veematics
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
  • 2
LVL 31

Accepted Solution

gowflow earned 1050 total points
ID: 36487841
yes do something like this.
You need to affect the current workbook to a variable and he new one ot a variable the you can close which one you like and keep the one you like open.

pls chk the below code
Sub SavenExit()
Dim ThisWB As Workbook
Dim NewWb As Workbook

Set ThisWB = ActiveWorkbook
Set NewWb = Workbooks.Add
ThisWB.Sheets("Sheet1").Copy after:=NewWb.Worksheets(NewWb.Worksheets.Count)
NewWb.SaveAs Filename:="C:\" & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End Sub

Open in new window

LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 450 total points
ID: 36488395
Would SaveCopyAs work instead?

Alternatively similar to gowflow suggestion,

Pass the curent workbook to a variable
Save as CSV
Reopen xls
Activate CSV
Close CSV

I have similar for creating a txt file for upload to HFM.

Sub SendToText()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    FW = Range("thiswk")
    LW = Right(Range("'Input Form'!T7"), 2)
    SourceFile = ActiveWorkbook.FullName
    SourcePath = ActiveWorkbook.Path
    DestPath = SourcePath & "\HFM Upload\"
    NewFileName = "13 Week Forecast " & FW & "-" & LW & ".txt"
    TextFile = DestPath & NewFileName
    Msg = MsgBox("HFM Upload file will be created:" & Chr(10) & TextFile, _
                vbOKOnly, "Complete")
    Sheets("HFM Upload").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:=TextFile, FileFormat:= _
        xlText, CreateBackup:=False
    Workbooks.Open Filename:=SourceFile, Origin:=xlWindows
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Open in new window

Rob H

Author Comment

ID: 36493355
.Copy doesn't work because in a cell there's some function, when it convert to CSV the value doesnt show up instead #NAME?"

Author Closing Comment

ID: 36493391
I found the solution from tweaking the code from the ideas that stated here.

the flow:
- Save ActiveWorkbook
- set the SourceFile
- set the Targeted Sheet/Filename
- Save As CSV
- Open the SourceFile (the Original)
- Close the CSV's workbook that just newly created

From the code/technical view.. it goes like this:

   Dim SourceFile As String
   Dim CSVSheet As String

    SourceFile = ActiveWorkbook.FullName
    CSVSheet = ActiveSheet.Name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\" & CSVSheet & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True
    Workbooks.Open Filename:=SourceFile, Origin:=xlWindows
    Workbooks(CSVSheet).Close savechanges:=False

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

801 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