[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7843
  • Last Modified:

Creating a comma delimited text file from an excel workbook

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
Derek Schauland (Microsoft MVP)
Asked:
Derek Schauland (Microsoft MVP)
2 Solutions
 
KavarCommented:
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
 
Derek Schauland (Microsoft MVP)IT ConsultantAuthor Commented:
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
 
ajnabitouCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Derek Schauland (Microsoft MVP)IT ConsultantAuthor Commented:
is there a fileformat I can specify that will generate a csv?
0
 
DabasCommented:
Hi DerekSchauland:

    ActiveWorkbook.SaveAs Filename:="F:\groups\creDIT\nacm reports\nacm" + filenme + ".csv", FileFormat:= _
        xlCSV, CreateBackup:=False
Dabas
0
 
ajnabitouCommented:
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
 
Derek Schauland (Microsoft MVP)IT ConsultantAuthor Commented:
seems ok so far... when i hear further I will let you know if it is working


thanks
0
 
tfmiltzCommented:
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now