Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

Solved

Excel - Export sheet in CSV Value

Posted on 2011-09-05

2 solutions

Medium Priority

1,049 Views

Last Modified: 2012-06-27

Hello,
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:

Sheets(Sheetname).Activate
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.Sheets(Sheetname).Select
    Application.Sheets(Sheetname).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\" & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
    Windows(oldfilename).Activate
    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 !

Comment

Question by:veematics

[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

Join The Community

4 Comments

LVL 31

Accepted Solution

by:gowflow

gowflow earned 1050 total points

ID: 364878412011-09-06

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
gowflow

Sub SavenExit()
Dim ThisWB As Workbook
Dim NewWb As Workbook

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

Open in new window

LVL 33

Assisted Solution

by:Rob Henson

Rob Henson earned 450 total points

ID: 364883952011-09-06

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
    
    ActiveWorkbook.Save
    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
    Range("A1:A14").Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ActiveWorkbook.SaveAs Filename:=TextFile, FileFormat:= _
        xlText, CreateBackup:=False
    
    Workbooks.Open Filename:=SourceFile, Origin:=xlWindows
    Windows(NewFileName).Activate
    ActiveWorkbook.Close
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

Open in new window

Thanks
Rob H

Author Comment

by:veematics

ID: 364933552011-09-06

.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

by:veematics

ID: 364933912011-09-06

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

ActiveWorkbook.Save
SourceFile = ActiveWorkbook.FullName
CSVSheet = ActiveSheet.Name
Application.DisplayAlerts = False
Sheets(Sheetname).Activate
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

Question has a verified solution.

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

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

Error 0xc004d307 When Rearming Office 2013 for KMS

Article by: Paul

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…

Creating a Double-Sided Bar Chart in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Microsoft Excel

Using, Creating and Modifying Styles in Microsoft Excel

Video by: Bob

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…