Solved

Creating a comma delimited text file from an excel workbook

Posted on 2004-04-06
8
7,837 Views
Last Modified: 2012-06-21
I am trying to create a report in excel from SQL server.  This part of the problem works just fine, however the end result needs to be in the form of a comma delimited text file... and the excel text file it creates is not visible in notepad.

How might I get this to work?

the command button code to create the txt is below

Private Sub CommandButton2_Click()
    Dim filenme As String
   
   
    Rows("7:616").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("C:C").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Application.CutCopyMode = False
    filenme = InputBox(Prompt:="Please enter report date (ie 04022004)", Title:="Save As...")
    'If filenme = "" Then Exit Sub
    ActiveWorkbook.SaveAs Filename:="F:\groups\creDIT\nacm reports\nacm" + filenme + ".txt", FileFormat:= _
        xlText, CreateBackup:=False
End Sub
0
Comment
[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
8 Comments
 
LVL 10

Expert Comment

by:Kavar
ID: 10767325
most likely reason is because your program doesn't know what xlText is...

change the line to
ActiveWorkbook.SaveAs Filename:="F:\groups\creDIT\nacm reports\nacm" + filenme + ".txt", FileFormat:= -4158, CreateBackup:=False
0
 
LVL 9
ID: 10768695
Looks good... I have a feeling there might be some yelling about fixed length fields in the text file...

I am not even sure that is possible...??

0
 
LVL 1

Expert Comment

by:ajnabitou
ID: 10769141
Saving the file with a txt extension does not necessarily mean that the files contents are text.
A contents of a CSV file should be pure ascii format. A link you will find useful:
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm
If you are using EXCEL objects to create the file then you are propabably saving it in excel format. I am not sure but that may be the problem.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9
ID: 10769546
is there a fileformat I can specify that will generate a csv?
0
 
LVL 27

Accepted Solution

by:
Dabas earned 300 total points
ID: 10769714
Hi DerekSchauland:

    ActiveWorkbook.SaveAs Filename:="F:\groups\creDIT\nacm reports\nacm" + filenme + ".csv", FileFormat:= _
        xlCSV, CreateBackup:=False
Dabas
0
 
LVL 1

Assisted Solution

by:ajnabitou
ajnabitou earned 200 total points
ID: 10769911
Yeah that could work. I do not know a lot about office programming . But seems like that will work. If that doesnt work a simple solution is to create a simple file with a csv extension and write data to it following the rules that are specified in the link I posted above.
Let me know if this makes sense. Or I will eloborate.
0
 
LVL 9
ID: 10769955
seems ok so far... when i hear further I will let you know if it is working


thanks
0
 
LVL 1

Expert Comment

by:tfmiltz
ID: 10796978
Why not simply BCP it out from SQL Server through DMO or Command Prompt ?
Or ?  Simply DTS from SQL Server.

I'm looking at solving this with 1 line with the BCP from the command prompt,   as BCP will create a comma delited (or any character virtually) file.  I ask,  why not keep it simple ?  

Tim Miltz
Chief Technology Officer
Etaion Software
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

735 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