Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
7 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel - Export sheet in CSV Value
Posted on 2011-09-05
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:
The Problem: Current Active Window file is become the csv file, i want to load the previous (the master) file ?
--------------------------
The Opt 2:
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 !
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
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
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 !
[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
Active today
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
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
Active today
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.
Thanks
Rob H
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
Thanks
Rob H
.Copy doesn't work because in a cell there's some function, when it convert to CSV the value doesnt show up instead #NAME?"
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
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
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month7 days, 17 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.