Solved

Creating a comma delimited text file from an excel workbook

Posted on 2004-04-06
8
7,833 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
Question by:Derek Schauland
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

Author Comment

by:Derek Schauland
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
 
LVL 9

Author Comment

by:Derek Schauland
ID: 10769546
is there a fileformat I can specify that will generate a csv?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Author Comment

by:Derek Schauland
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
wordsWithout 49 79
sumHeights  challenge 17 61
control image tags in a string ? 12 62
SUM 2 INTEGER ARRAYS INTO 1 10 58
This is an explanation of a simple data model to help parse a JSON feed
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
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 …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

706 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

21 Experts available now in Live!

Get 1:1 Help Now