Derek Schauland
asked on
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").EntireColum n.AutoFit
Columns("E:E").EntireColum n.AutoFit
Columns("D:D").EntireColum n.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\creDI T\nacm reports\nacm" + filenme + ".txt", FileFormat:= _
xlText, CreateBackup:=False
End Sub
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").EntireColum
Columns("E:E").EntireColum
Columns("D:D").EntireColum
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\creDI
xlText, CreateBackup:=False
End Sub
ASKER
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...??
I am not even sure that is possible...??
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.
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.
ASKER
is there a fileformat I can specify that will generate a csv?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
seems ok so far... when i hear further I will let you know if it is working
thanks
thanks
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
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
change the line to
ActiveWorkbook.SaveAs Filename:="F:\groups\creDI