Excel saving a file witout quotations

[5h1v]

Hey,

I am trying to make some VBA that saves one row of data from a spreadsheet and saves it as a PRN file which I can do but it adds quotation marks to the start and end of every row, how do I go about removing them without having to do it manually?

Here is my code;

   Sheets("Olink").Select
    Columns("P:P").Select
    Range("P188").Activate
    Selection.Copy
    Sheets.Add after:=ActiveSheet
    Worksheets.Add().Name = "DD"
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    myFolder = Application.GetSaveAsFilename(fileFilter:="PRN (*.prn), *.prn")
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlCSV, CreateBackup:=False
End Sub

 

[Danmc]

i ran your code and it worked, don't see the quote marks though. maybe post your workbook.

 

[5h1v]

Thanks for the help.

How do I post my worksheet?

I am opening the output with notepad 2 if that makes any difference.

 

[Joe4]

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Note that sometimes people will upload files to a file share site, and provide links to it here. Just note that for security reasons, many people are unable or unwilling to download files from the internet (especially ones with VBA code).

 

[Danmc]

to post your workbook,go to http://www.tinyupload.com/ and upload. then post the URL to this forum

 

[5h1v]

The site is blocked by my provider so I guess I cant do that, thanks for the help anyway.

 

[Joe4]

The site is blocked by my provider so I guess I cant do that, thanks for the help anyway.

Are you able to use any of the tools discussed in the first link I provided?

Also, it looks like your code may just be copying a single cell (P188). If that is the case, could you just copy/paste the contents of that cell here for us to see?

 

[5h1v]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]HDR,JV1909110SP,Y,Y[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]VHD,PJE,OL,JV1909110SP,GL,Book ATM Income 01/05-31/05/19,30/06/2019,N,,SUB[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]VIL,NL,01-1179-0390,GLJ,JV1909110SP,Book ATM Income 01/05-31/05/19,30/06/2019,GBP,47.47,1,47.47,CR,,[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]VIL,NL,01-1262-0390,GLJ,JV1909110SP,Book ATM Income 01/05-31/05/19,30/06/2019,GBP,41.07,1,41.07,CR,,[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]VIL,NL,01-1276-0390,GLJ,JV1909110SP,Book ATM Income 01/05-31/05/19,30/06/2019,GBP,54.29,1,54.29,CR,,[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]FLT,21269.6,21269.6,194,1,198[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]


That is the outcome of the cells although they are a formulae cell if that matters the formulae is below;

=CLEAN(IF(C3="","",OLink!A3&","&OLink!B3&","&OLink!C3&","&OLink!D3&","&OLink!E3&","&OLink!F3&","&TEXT(OLink!G3,"DD/MM/YYYY")&","&OLink!H3&","&OLink!I3&","&OLink!J3&","&OLink!K3&","&OLink!L3&",,"))

I am trying the other alternatives but my provider is not great

 

[Joe4]

I did some testing, and it is because your data has commas in it. Excel automatically does to differentiate literal commas in data from commas used as delimiters.
I have seen people get around that by having VBA code write directly to a text file instead of saving it as a text file.

You also have an error in your code.
This:

Worksheets.Add().Name = "DD"

should look like this:

Worksheets.Add.Name = "DD"

 

[5h1v]

Thanks

That makes sense, how would I write it to a text file instead of saving it?