Solved

Excel - Export sheet in CSV Value

Posted on 2011-09-05
4
1,028 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
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 350 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 150 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

829 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