Excel saving a file witout quotations

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
66
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;

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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Danmc

New Member
Joined
Nov 19, 2015
Messages
33
i ran your code and it worked, don't see the quote marks though. maybe post your workbook.
 

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
66
Thanks for the help.

How do I post my worksheet?

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,473
Office Version
365
Platform
Windows
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).
 

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
66
The site is blocked by my provider so I guess I cant do that, thanks for the help anyway.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,473
Office Version
365
Platform
Windows
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?
 
Last edited:

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
66
HDR,JV1909110SP,Y,Y

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
VHD,PJE,OL,JV1909110SP,GL,Book ATM Income 01/05-31/05/19,30/06/2019,N,,SUB

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
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,,

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
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,,

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
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,,

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
FLT,21269.6,21269.6,194,1,198

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>

<tbody>
</tbody>


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,473
Office Version
365
Platform
Windows
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:
Code:
Worksheets.Add().Name = "DD"
should look like this:
Code:
Worksheets.Add.Name = "DD"
 

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
66
Thanks

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

Watch MrExcel Video

Forum statistics

Threads
1,099,992
Messages
5,471,863
Members
406,790
Latest member
richphi37

This Week's Hot Topics

Top