Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MaxFrance
 
Posts: n/a
Default Excel Quotation Marks!

If I save (using a macro) an excel file as a text file MSDOS (not tab
delimited), I get a file with quotation marks at the start and end of each
line! If I do the process manually the file is perfect. Can someone help !!

Excel 2000 all updates done
XP prof all updates done (with sp2)

Thanks
  #2   Report Post  
Jim Rech
 
Posts: n/a
Default

When I save a file with this code I do not get quotes:

ActiveWorkbook.SaveAs "abc", xlTextMSDOS

--
Jim Rech
Excel MVP
"MaxFrance" wrote in message
...
| If I save (using a macro) an excel file as a text file MSDOS (not tab
| delimited), I get a file with quotation marks at the start and end of each
| line! If I do the process manually the file is perfect. Can someone help
!!
|
| Excel 2000 all updates done
| XP prof all updates done (with sp2)
|
| Thanks


  #3   Report Post  
Jim Rech
 
Posts: n/a
Default

If there are commas in your data then you will get quotes with a macro,
despite having French setting (I assume). When a macro runs Excel is thrown
into "US settings mode" where a comma is the list separator. In order to
distinguish separator commas from data commas quotes are used.

You might try using this macro which gives you more control over the output:

''No quotes around strings
''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsTrueCSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName < False Then
ListSep = Application.International(xlListSeparator)
'ListSep = "," 'use this to force commas as separator regardless of
regional settings
If Selection.Cells.Count 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

--
Jim Rech
Excel MVP
"MaxFrance" wrote in message
...
| If I save (using a macro) an excel file as a text file MSDOS (not tab
| delimited), I get a file with quotation marks at the start and end of each
| line! If I do the process manually the file is perfect. Can someone help
!!
|
| Excel 2000 all updates done
| XP prof all updates done (with sp2)
|
| Thanks


  #4   Report Post  
MaxFrance
 
Posts: n/a
Default

This is the bizarre thing, for certain lines it's okay (every 4th - specifc
format) but the others no. I had already used the code you've suggested,
bizarre.



"Jim Rech" wrote:

When I save a file with this code I do not get quotes:

ActiveWorkbook.SaveAs "abc", xlTextMSDOS

--
Jim Rech
Excel MVP
"MaxFrance" wrote in message
...
| If I save (using a macro) an excel file as a text file MSDOS (not tab
| delimited), I get a file with quotation marks at the start and end of each
| line! If I do the process manually the file is perfect. Can someone help
!!
|
| Excel 2000 all updates done
| XP prof all updates done (with sp2)
|
| Thanks



  #5   Report Post  
MaxFrance
 
Posts: n/a
Default

I'll try this, could be the answer, unfortunately for any french analysis I
prepare using any excel system, I am obliged by the companies to implement
their number system - commas as opposed to points for the decimal break!
I'll let you know what happens. Thanks

"Jim Rech" wrote:

If there are commas in your data then you will get quotes with a macro,
despite having French setting (I assume). When a macro runs Excel is thrown
into "US settings mode" where a comma is the list separator. In order to
distinguish separator commas from data commas quotes are used.

You might try using this macro which gives you more control over the output:

''No quotes around strings
''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsTrueCSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName < False Then
ListSep = Application.International(xlListSeparator)
'ListSep = "," 'use this to force commas as separator regardless of
regional settings
If Selection.Cells.Count 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

--
Jim Rech
Excel MVP
"MaxFrance" wrote in message
...
| If I save (using a macro) an excel file as a text file MSDOS (not tab
| delimited), I get a file with quotation marks at the start and end of each
| line! If I do the process manually the file is perfect. Can someone help
!!
|
| Excel 2000 all updates done
| XP prof all updates done (with sp2)
|
| Thanks





  #6   Report Post  
MaxFrance
 
Posts: n/a
Default

The reason for the quote marks is now apparent - french system requiring
commas as opposed to points for the decimal indicator - I can and have tested
the file replacing the commas by points - perfect - unfortunately I am
obliged to create and pass through the interface a text file with commas in
place of the points - So I am back at stage one. Any further help would be
great. Thanks

"Jim Rech" wrote:

If there are commas in your data then you will get quotes with a macro,
despite having French setting (I assume). When a macro runs Excel is thrown
into "US settings mode" where a comma is the list separator. In order to
distinguish separator commas from data commas quotes are used.

You might try using this macro which gives you more control over the output:

''No quotes around strings
''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsTrueCSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName < False Then
ListSep = Application.International(xlListSeparator)
'ListSep = "," 'use this to force commas as separator regardless of
regional settings
If Selection.Cells.Count 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

--
Jim Rech
Excel MVP
"MaxFrance" wrote in message
...
| If I save (using a macro) an excel file as a text file MSDOS (not tab
| delimited), I get a file with quotation marks at the start and end of each
| line! If I do the process manually the file is perfect. Can someone help
!!
|
| Excel 2000 all updates done
| XP prof all updates done (with sp2)
|
| Thanks



  #7   Report Post  
MaxFrance
 
Posts: n/a
Default

Okay,

I modified your macro to take into effect the automatic save of a specfic
name each time, and changed the delimiter. It's perfect. Thanks for you
help - it's cured a major headache.


"Jim Rech" wrote:

If there are commas in your data then you will get quotes with a macro,
despite having French setting (I assume). When a macro runs Excel is thrown
into "US settings mode" where a comma is the list separator. In order to
distinguish separator commas from data commas quotes are used.

You might try using this macro which gives you more control over the output:

''No quotes around strings
''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsTrueCSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName < False Then
ListSep = Application.International(xlListSeparator)
'ListSep = "," 'use this to force commas as separator regardless of
regional settings
If Selection.Cells.Count 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

--
Jim Rech
Excel MVP
"MaxFrance" wrote in message
...
| If I save (using a macro) an excel file as a text file MSDOS (not tab
| delimited), I get a file with quotation marks at the start and end of each
| line! If I do the process manually the file is perfect. Can someone help
!!
|
| Excel 2000 all updates done
| XP prof all updates done (with sp2)
|
| Thanks



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I get a program error when I download an excel template Ladybug Excel Discussion (Misc queries) 3 March 4th 05 01:02 AM
html to excel nellie Excel Discussion (Misc queries) 4 February 8th 05 11:37 PM
Merge from Excel to Excel dalstar Excel Discussion (Misc queries) 3 January 30th 05 03:37 PM
Excel 2002 and 2000 co-install. Control Which Starts ? cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM
Shortcut file fails to open JimH Excel Discussion (Misc queries) 3 January 15th 05 11:13 PM


All times are GMT +1. The time now is 07:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"