Enclosing values in Quotation marks

B

bypass

I am currently using the script below to copy values from one sheet t
another.

I am hoping to get all values copied to the destination sheet enclose
by quotation marks (eg "value1"), so that I can then save the sheet a
a .CSV file for a dataload.

Can anyone please suggest a modification to the sub below that would d
this?

Sub CSV()
Dim DestSheet As Worksheet, SrcSheet As Worksheet
Set SrcSheet = ActiveSheet
Set DestSheet = Sheet2
With SrcSheet
.Activate
Range(.Range("a3"), .Range("a3").End(xlDown)).Resize(,
34).Copy
End With
With DestSheet.Range("a1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End Sub


xl2000 on windows 2
 
G

Gjones

Dear ByPass;

If I understand you correctly you just need to save sheet
2 out as a csv file. Excel will handle the delimiting for
you automatically. The code might look like the below.
You will probably want to disable the alerts.

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Administrator\My
Documents\Book1.csv", FileFormat _
:=xlCSV, CreateBackup:=False


Thanks,

Greg
 
T

Tom Ogilvy

Don't think that approach will give you what you want - you will end up with
more double quotes than you bargained for if you were successful (plus there
is no command to do that anyway - not sure why you think pastespecial will
put in doublequotes).

See this article for code that should produce the csv file you describe.

http://support.microsoft.com/default.aspx?scid=kb;en-us;213448&Product=xlw
XL2000: Procedure to Export a Text File with Both Comma and Quote Delimiters
 
Top