+ Reply to Thread
Results 1 to 9 of 9

Double Quotes using Chr(34) and Extra Quotes when saving as text file

  1. #1
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Double Quotes using Chr(34) and Extra Quotes when saving as text file

    I am in charge of a SharePoint site and I have a scrolling Marquee that I want some of my fellow coworkers to be able update. Problem is most of them will mess up the HTML code that drives the Marquee. My solution was to give them a spreadsheet that creates takes their typed messages, creates the html code, and saves it to a text file.


    My problems!

    1) Somehow the first two lines of text start and end with quotation marks that were not placed by me (not on purpose anyway)

    2) Somewhere in the transition from Excel to Text file it adds some quotations in other places where Chr(34) is used.

    Overall HTML Code generated:
    HTML Code: 
    Lines that I see in Excel:
    HTML Code: 
    Here is the code I am using:

    Please Login or Register  to view this content.
    Last edited by vamosj; 07-26-2013 at 11:58 AM.
    Janos S. Vamos
    Data Systems Technician/Fire Controlman PO1(SW/AW)
    US Navy Retired


    Remember, Record Macro can be your friend for figuring out solutions.

    Good or Bad, let me know how I did by clicking on the "Add Reputation" * just to the lower left of here. it only takes a few seconds to let someone know.

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    Not sure if these ideas will help.

    1. For the current method, replace Chr(34) with "&#34".
    2. Try putting the string into the text file in the background rather than SaveAs.

    You can use Sheet1.UsedRange. You may need to use Replace() to replace some vbTabs. Send the UserRange.Value and the text filename to:
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    Still pretty new to the vba world, so not quite understanding part 2 of your reply. As for part one, I went through and replaced all Chr(34) with the "&#34" and while it did remove the quotations at the first two lines, all internal double quotes now show up with the text &#34.


    HTML Code: 
    Last edited by vamosj; 07-18-2013 at 01:30 PM. Reason: spelling error

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    Paste this code into a Module. Be sure to add the Reference as commented in the GetClipboard routine. Run ken from a blank worksheet.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    Could you explain the "Reference" parameters a little to me. You tell me to add them but this is another function I am not too familiar with. I do a lot of logical pattern items (activecell.offsets, if statements, loops, etc...). Mainly the stuff I can easily follow as I step through it.

  6. #6
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    In the Visual Basic Editor (VBE), select the menus, Tools > References. Select the object named, MicroSoft Forms 2.0 Object. This lets you use the methods and properties of that object. e.g http://www.officecoders.com/2012/07/...s-provide.html

    If you have not added the Compile button to the VBE, do that. Always Compile code before you try to run it. Otherwise, during runtime you will see an Object error.
    Last edited by Kenneth Hobson; 07-22-2013 at 04:39 PM.

  7. #7
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    Thank you very much for your time there Ken. After some tweeks to it I was able to get it working pretty much for what I need. Only issue I was unable to overcome was saving it to a web folder which I don't think will be possible through VBA. So I got it set up to where the code saves itself as a text file on the individuals desktop and they can upload the text file which drives the marquee. Pretty simple for the end user. I have tried it out and it is as close to sailor proof as I will get it for now. I have attached the final code.

    Once again, thank you for your time.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    Feel free to start another thread for the upload part. A search may find a solution though. Look for FtpPutFile, FTPUpload, or URLUpload. Several of those methods have ways to do it with parameters for username and password if needed. IF you can do FTP from a DOS command prompt manually, several ways like that can be automated in VBA.

    Some programs like WSFTPPro have batch modes that can automate uploads as well.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Double Quotes using Chr(34) and Extra Quotes when saving as text file

    You might have noticed that only lines that contain double quotes " are being 'wrapped' in added double quotes "
    So it's easy to remove these:

    Please Login or Register  to view this content.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need double quotes when importing .csv file into text editor
    By rachelalbert in forum Excel General
    Replies: 0
    Last Post: 07-04-2012, 01:40 PM
  2. saving text to a file - getting rid of quotes
    By madhg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2012, 07:08 PM
  3. Replies: 2
    Last Post: 07-29-2009, 11:06 AM
  4. Setting a cell's value as a formula containing quotes or double quotes using vba
    By Metshrine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-09-2006, 04:10 AM
  5. saving as text adding extra quotes?
    By Corey Wirun in forum Excel General
    Replies: 2
    Last Post: 05-13-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1