Excel - Export sheet in CSV Value

veematics
veematics used Ask the Experts™
on
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Partner
Commented:
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

Rob HensonFinance Analyst
Commented:
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
veematics

Author

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial