Solved

Excel - Export sheet in CSV Value

Posted on 2011-09-05
4
1,023 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 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now