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

x
?
Solved

Excel - Export sheet in CSV Value

Posted on 2011-09-05
4
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 !
0
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
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
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
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

0
 
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
    
    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
0
 

Author Comment

by:veematics
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?"
0
 

Author Closing Comment

by:veematics
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
   

    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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

597 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