Excel VBA adds quotes on output to notepad 2

[jbivin]

I have a VBA code I run to select a sheet, copy that sheet, and save it as a .txt file. It pastes several strings of text and one just one of them it always adds quotes at the front and back. These quotes do not show up in the cell in which the text is contained. The sheet that is copied has cells that reference another sheet so maybe the reference is causing the issue, but I am not sure as some other cells also reference the same sheet and they copy and paste fine. Any help in removing these quotes would be greatly appreciated.

 

[SkipVought]

Any help in removing these quotes would be greatly appreciated.

Edit > Replace.

Any further advice will require your code and a sample of the source data in question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[glenntb]

Does the problematic text string contain a delimeter within it? (e.g. is there a comma in the string: "Doe, Jane")

Glenn

 

[jbivin]

Yes it does. This is an example of the string.

AW: 7#7, 18M

 

[PHV]

Again, as Skip suggested, why not posting your code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jbivin]

Here is the code for a button to export the sheet:


Dim PartNumber As String
Dim FileName As String

PartNumber = Range("C52")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Shield Splice TTP File").Select
Sheets("Shield Splice TTP File").Copy
ActiveWorkbook.SaveAs FileName:= _
"F:\Operations Improvement\Manufacturing Improvement\Manufacturing Eng\CO-OP MFG ENG\2009 - Fall, 2010 Spring, Summer - Jonathan Bivin\Patterns\" & PartNumber & ".TTP", _
FileFormat:=xlCSV, CreateBackup:=False

ActiveWorkbook.Close
ThisWorkbook.Activate

Sheets("TTS File").Select
Sheets("TTS File").Copy
ActiveWorkbook.SaveAs FileName:= _
"F:\Operations Improvement\Manufacturing Improvement\Manufacturing Eng\CO-OP MFG ENG\2009 - Fall, 2010 Spring, Summer - Jonathan Bivin\Patterns\" & PartNumber & ".TTS", _
FileFormat:=xlText, CreateBackup:=False

ActiveWorkbook.Close
ThisWorkbook.Activate

Sheets("TTC File").Select
Sheets("TTC File").Copy
ActiveWorkbook.SaveAs FileName:= _
"F:\Operations Improvement\Manufacturing Improvement\Manufacturing Eng\CO-OP MFG ENG\2009 - Fall, 2010 Spring, Summer - Jonathan Bivin\Patterns\" & PartNumber & ".TTC", _
FileFormat:=xlText, CreateBackup:=False

ActiveWorkbook.Close
ThisWorkbook.Activate

Sheets("Shield Splice TXP File").Select
Sheets("Shield Splice TXP File").Copy
ActiveWorkbook.SaveAs FileName:= _
"F:\Operations Improvement\Manufacturing Improvement\Manufacturing Eng\CO-OP MFG ENG\2009 - Fall, 2010 Spring, Summer - Jonathan Bivin\Patterns\" & PartNumber & ".TXP", _
FileFormat:=xlText, CreateBackup:=False

ActiveWorkbook.Close
ThisWorkbook.Activate

Here is the information that is copied, each line is a different cell:


PATTERN
SOFTWARE_ITEM
64832
SOFTWARE_VERSION
2
WINDOW
HEIGHT
6
WIDTH
12
END_WINDOW
PARK_THETA
0
Y_TO_THETA
DISABLED
PARK
X
6
Y
6
END_PARK
COUNT_FILE
PSDE130949.TTC
S/N_FILE
PSDE130949.TTS
S/N_INCREMENT_MODE
PATTERN
ARRAY
ROWS
1
COLUMNS
1
ROW_OFFSET
1
COLUMN_OFFSET
1
CENTERED
DISABLED
END_ARRAY
SHOW_INSTRUCTIONS
ENABLED
OPTIMIZE_VECTOR_PATH
ENABLED
USER_FONT

MACHINE
MARKER
INTERFACE
TCP_IP
CONTROLLER
TMC470
MODEL
TMP6100
SPEED
70
SPEED_SMALL
70
SETTLE
0
HOMING
DISABLED
PIN
TYPE
25L
STROKE
0.125
AIR_PRESSURE
80
RETURN_AIR_PRESSURE
20
RATE_ADJUST
30
DEPTH
0
END_PIN
END_MARKER
ROTATIONAL
SPEED_FACTOR
70
PART_DIAMETER
811630
MOUNTING_ANGLE
0
END_ROTATIONAL
END_MACHINE
TOOLS
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
OVERLAP CRIMPS
COLOR
0
ANCHOR_XY
X
5.515
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
270
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
OVERLAP CRIMPS
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
TOP/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
1
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
COMPRESS TO END
COLOR
0
ANCHOR_XY
X
5.74
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
270
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
COMPRESS TO END
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
TOP/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
1
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
START HERE
COLOR
0
ANCHOR_XY
X
5.965
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
270
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
START HERE
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
TOP/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
1
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
COLOR
0
ANCHOR_XY
X
6.19
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
270
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.073
JUSTIFICATION
BOTTOM/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
COLOR
0
ANCHOR_XY
X
6.755
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
90
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.073
JUSTIFICATION
BOTTOM/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
START HERE
COLOR
0
ANCHOR_XY
X
6.98
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
90
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
START HERE
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
TOP/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
1
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
COMPRESS TO END
COLOR
0
ANCHOR_XY
X
7.205
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
90
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
COMPRESS TO END
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
TOP/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
1
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
OVERLAP CRIMPS
COLOR
0
ANCHOR_XY
X
7.43
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
90
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
OVERLAP CRIMPS
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
TOP/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
1
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
AW: 7#7, 18M
COLOR
0
ANCHOR_XY
X
8.318
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
52
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
0
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
AW: 7#7, 18M
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
CENTER/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
EHS: 7/16
COLOR
0
ANCHOR_XY
X
8.318
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
26
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
0
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
EHS: 7/16
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
CENTER/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
60 TON PRESS MIN
COLOR
0
ANCHOR_XY
X
8.318
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
0
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
0
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
60 TON PRESS MIN
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
CENTER/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
%X FARGO
COLOR
0
ANCHOR_XY
X
8.318
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
334
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
0
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
%X FARGO
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
CENTER/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
TOOL_TEXT
TOOL
PRINT_MODE
STANDARD
THETA_MODE
INDEX
DESCRIPTION
811630
COLOR
0
ANCHOR_XY
X
8.318
Y
2.25
END_ANCHOR_XY
ANCHOR_2_XY
X
0
Y
0
END_ANCHOR_2_XY
ANCHOR_3
0
ANCHOR_4
308
ANCHOR_PRIORITY
1
ANCHOR_2_PRIORITY
1
ANCHOR_3_PRIORITY
1
ANCHOR_4_PRIORITY
1
ANGLE
0
DENSITY_VECTOR
100
PASSES_VECTOR
1
DENSITY_RASTER
300
PASSES_RASTER
1
VECTOR_FILL_MODE
0
VECTOR_FILL_SPACING
0.01
VECTOR_FILL_ANGLE
0
VECTOR_FILL_SOLID
ENABLED
VECTOR_FILL_SEPERATION
0
END_TOOL
TEXT
811630
FONT
FON57S
HEIGHT
0.125
WIDTH
0.063
PITCH
0.1
JUSTIFICATION
CENTER/CENTER
CONTINUOUS
FALSE
CHARACTER_INDEX
0
END_TOOL_TEXT
END_TOOLS
END_PATTERN

Thanks for any help.

 

[xlbo]

Why save one sheet as CSV and others as TEXT? You are saving as CSV which is comma delimited
If you have commas in your data, to tell the file that the comma is not a delimeter but part of a text string, "" are used



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jbivin]

They should have all been xlText. I was playing with the code trying to get the quotation marks to go away.

 

[combo]

There are some text format options in export. If the layout match your needs, try FileFormat:=xlTextPrinter

combo

 

[strongm]

xlTextPrinter is the one I'd advise.

Be warned, however, that it truncates strings to 240 characters

 

[jbivin]

Thanks to all who helped. The xlTextPrinter worked like a charm.