Solved: Save data to textfile with " in.

[Marcster]

Hello People :hi: ,

I have data in an Excel file as shown below:


01-23-45 John Smith 02345678 123.10 Reference 99
65-43-21 Joe Bloggs 87654320 1245.10 Reference 99

What I want to be able to do is, save the above information
to a text file without an extension to the same place as the workbook.

The file name should be the uk date. i.e. for today: 031005
When that file is opened in notepad the contents should look like:

"01-23-45","John Smith","02345678","123.10","Reference","99"
"65-43-21","Joe Bloggs","87654320","1245.10","Reference","99"

Like a .CSV file but with " before and after the data in the columns.
The data rows will vary in length. :help

Is there a macro to do this please?. :think: .

Thanks,

Marcster.

[lucas]

Hi Marcster,
haven't worked all the bugs out but this will get you started.....still saves as a date.txt file......and it closes excel when you run it....see if it helps


Option Explicit
Sub SaveTextfile()
Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("A1:A" & LastRow)

ActiveWorkbook.SaveAs Filename:= _
"F:\Temp\" & Format(Date, "ddmmyy") & ".txt", FileFormat:=xlCSVMSDOS, _
CreateBackup:=False
Application.Run "checkname"
ActiveWorkbook.Close True
Set rng = Nothing
End Sub

[Aaron Blood]

Been there, done that...

http://www.xl-logic.com/xl_files/vba/csv.zip

[lucas]

Cant get it to save without a file extension yet or to close the txt file without closing the workbook...still working on it. But this will cause the save to be in the same directory as the excel workbook.
Option Explicit
Sub SaveTextfile()
Dim LastRow As Long
Dim rng As Range
Dim fPath As String
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("A1:A" & LastRow)
fPath = ActiveWorkbook.Path & "\"
ActiveWorkbook.SaveAs Filename:= _
fPath & Format(Date, "ddmmyy") & ".txt", FileFormat:=xlCSVMSDOS, _
CreateBackup:=False
Application.Run "checkname"
ActiveWorkbook.Close True
Set rng = Nothing
End Sub

[Marcster]

Thanks for the replies :thumb .

I have a problem with the csv.xls file though:

When i click on the button 'Show_CSV_Form' an error is displayed:
Run-time error '438':
Object doesn't support this property or method

I think it may be to do with:
Application.UseSystemSeparators = Chk_UseSystem.Value
in Private Sub Shk_UseSystem_Click

.UseSystemSeparators doesn't appear when I type Application. in the VBE.


I'm using Excel 2000 on Windows XP Pro.

[Marcster]

Solved on thread
http://www.vbaexpress.com/forum/showthread.php?p=46452#post46452