?
Solved

Creating a comma delimited text file from an excel workbook

Posted on 2004-04-06
8
Medium Priority
?
7,840 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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

Accepted Solution

by:
Dabas earned 1200 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 800 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

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!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Six Sigma Control Plans
Suggested Courses

770 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