+ Reply to Thread
Results 1 to 13 of 13

mixing Excel with code editors (IDEs)

  1. #1
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    mixing Excel with code editors (IDEs)

    I thought I would post some knowledge about this subject, since I just found out tonight. I do a lot of work that is not considered "standard" by the programming community, so this may not prove useful to everyone. But if you ever use Excel to modify code that developers usually modify in IDEs, you may run into this issue (I use Excel all the time to patternize code and make use of the grid-based layout). Apparently, if you copy text from a code editor into Excel and copy it back into the editor, every space and linebreak between text is filled with ASCII characters or symbols that only certain parsers recognize and show. This problem occurred with all of these editors: Visual Studio Code, Visual Studio 2019, Eclipse. Funny enough, going through the same process using MS Word does not result in this anomaly. If this post is not removed by a moderator, it may prove to be useful to some here if they ever start coding in other languages.

    Adam

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: mixing Excel with code editors (IDEs)

    Quote Originally Posted by vba_php View Post
    If this post is not removed by a moderator
    It won't be. We are pretty freewheeling about what can be posted in Tips & Tutorials.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: mixing Excel with code editors (IDEs)

    well wonderful! thank you for the clarification. I hope this provides some insight to someone here at some point, if they are as crazy as I am with coding work-arounds!

  4. #4
    Registered User
    Join Date
    07-08-2020
    Location
    Ukraine, Kiev
    MS-Off Ver
    Microsoft Office 2010 (64-bit)
    Posts
    15

    Re: mixing Excel with code editors (IDEs)

    Thank you, we're waiting for your work!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: mixing Excel with code editors (IDEs)

    @Adam, did you consider using further code to format the cell information specifically for pasting into VSCode?

    Can you post example of your 'patternize code' and the issues it raises when pasting?
    Cheers
    Andy
    www.andypope.info

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: mixing Excel with code editors (IDEs)

    Quote Originally Posted by Andy Pope View Post
    @Adam, did you consider using further code to format the cell information specifically for pasting into VSCode?

    Can you post example of your 'patternize code' and the issues it raises when pasting?
    what do you mean by "format the cell information"? I know that Excel 'recognizes' different data as certain types by default. Is this what you're referring to? If it is, it doesn't seem like that has anything to do with the red dots the GoDaddy agent saw in his parser. I can't post any patternized code for you, but I can show you the code I was working on when this happened. It is just a small server side script, but it has huge amounts of HTML table data in it that was pre-populated from Excel doing patternized work. Give me a while and I will upload it here in a new post, following this one......

  7. #7
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: mixing Excel with code editors (IDEs)

    Andy,

    Here is what I'm talking about, since you asked. This is a page's code I had to update recently. see attached text file (web_page_code).

    and, to do the update (of course I've changed the code to replace company info), I wrote a simple VB routine in Excel. This is not really what I'm talking about by using "patterns", but it is some of the ways I use Excel to manipulate web app code so I don't have to waste time typing stuff out. But then again, IDEs are getting so easy to use, this method will probably be just as easy using code editors very soon! Here is the VBA routine. see attached text file (vba_code).

    I tried to post this stuff here, but I assume these folks are using cloudfare as a security scanner? HTML is not allowed apparently here. I assume PHP is not either? enclosing the code in tags apparently doesn't get around the software either. =(
    Attached Files Attached Files

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: mixing Excel with code editors (IDEs)

    Do though 2 files illustrate the actual issue? If so can you explain.


    I was thinking that if the information you had in cells did not copy/paste nicely then write code to load the information into a string variable and copy that to the clipboard, then the paste should be a lot cleaner

  9. #9
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: mixing Excel with code editors (IDEs)

    well your advice is very handy Andy. But I don't know how to do *everything* with coding in all languages. That's why this gentleman on youtube is saying what he is saying. you ever seen this before?

    https://www.youtube.com/watch?v=NN1oN0-1M4A

    SO true. and there is only reason => all of those strange folks running those huge companies are releasing new javascript frameworks everyday. I'm sure their people get sick of it. and yes, the VBA file shows what kind of stuff I do with Excel, but that's all. 15 years ago I started doing this when I started writing web apps with PHP. I actually used VBA code inside of an access file to write the PHP code, throw it to an access table and then export it out and rename the file to a ".php" extension. all of that was automated with VBA code. Obviously I couldn't automate the upload process through the FTP client with windows because I use a 3rd party program. But, I actually did find on the internet way back then, an FTP client written entirely in VBA code. and it actually works to this day! I still have it sitting on this machine in my knowledgbase.

    I do not work for a large company, but what I do is exactly what they *will* be doing very soon I'm sure. There are patterns to everything, especially code writing. That's why machine learning is so profitable right now. I mean aside from predicting what consumers will do. I'm fairly sure though, that those firms will not automate developers out of jobs. The no-code and low-code concept is huge right now. There are startups everywhere, as I understand it, promoting the concept.

    There's a good future ahead. LOL. =) Pretty soon people like us won't have to know anything to earn a paycheck! Just drag and drop the icons. Push the buttons. =(

    and now that I've rambled on, yes, the issue actually WAS to replace the innerHTML text inside of every <TD> tag in the table. Hence, the VBA declarations for "start", "start2", and "word". stuff like that. I hope that answers your question.

  10. #10
    Registered User
    Join Date
    10-05-2019
    Location
    Work: East Coast; Home: Texas, USA
    MS-Off Ver
    Mostly 2010 ( Windows 10 & 365 Insider Super Fast )
    Posts
    32

    Re: mixing Excel with code editors (IDEs)

    Quote Originally Posted by vba_php View Post
    ...I tried to post this stuff here, but I assume these folks are using cloudfare as a security scanner? HTML is not allowed apparently here. I assume PHP is not either? enclosing the code in tags apparently doesn't get around the software either. =(
    Hi Adam...
    This may be issue 1:-
    https://www.excelforum.com/tips-and-...rkarounds.html

    Only is issue of a < under certain conditions of following 4 characters.
    We may make it possible to post with working around-ings
    https://www.excelforum.com/developme...ml#post5381932
    ( if you hit the Reply With Quote button at that test forum post, then you will see the trick I did a few times to mask the offending character strings )
    Molly
    Bollocks

  11. #11
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: mixing Excel with code editors (IDEs)

    Molly,

    I took a look at your workaround thread, however I am at a loss as to how you got the sparsely populated bytes all around the HTML tags. They are in every line. below in the image is what I'm talking about. your FAQ did not explain exactly how to do that. At least from what I read. Can you clarify? when I pushed the reply to your post and saw all of that trickery, it looked to me that it was similar to what web developers call markdown. I mean the concept looked similar. what I saw was not anything like markdown.
    Attached Images Attached Images  

  12. #12
    Registered User
    Join Date
    10-05-2019
    Location
    Work: East Coast; Home: Texas, USA
    MS-Off Ver
    Mostly 2010 ( Windows 10 & 365 Insider Super Fast )
    Posts
    32

    Re: mixing Excel with code editors (IDEs)

    Hi Adam
    Quote Originally Posted by vba_php View Post
    .. I am at a loss as to how you got the sparsely populated bytes all around the HTML tags. ..
    Sorry I didn’t understand that. Probably me, - I am getting slower at getting the point these days…

    When I first joined I was sent to that FAQ thread to check out the “Black Character trick”.
    That is the only reason why this posting difficulty you were having caught my eye… ( That was to do something else:- so that I could “disguise” posting links in my first 10 or so posts, since I guess there is some anti spammer measure to stop people registering then flooding the place with spam links in their first dozen or so posts )
    The “Black Character trick” is being demonstrated at that FAQ thread , as far as I can imagine, to overcome the check done to prevent SQL injection. ……….
    Much, much further back in my past than I care to remember we did something similar like that by putting a filter to catch the likely commands that could do damage. But don’t ask me the details, I long since forgot, and I probably shouldn’t talk about it anyway***. As far as I do recall, it was satisfactory to include just normal characters in the first few character places to catch anything naughty. Some other things, like spaces and [ and a few other things I can’t remember, could be allowed. So if any of those other characters appear in the first few characters after a < then the thing can be accepted.
    I think that FAQ thread is suggesting that 4 character after a < is enough to concern yourself with . I honestly don’t remember how many we included, I thought it was more, I can’t remember.
    It seems that here, most people simply add a space after the < to get the string accepted. The one problem there is that in the final post you also end up with a space, which may be undesirable if you want to copy from that final post.
    The idea behind the “Black Character trick”, as far as I can tell from that FAQ thread , is that you use the BB Code tags to colour black any of one or more of the first four characters.
    Your final post comes out just as you wanted, assuming your text was all default black anyway, but in the posted text you have introduced a [ into the first 4 characters after the <
    So your posted string is accepted. That makes sense I think: The Scurry is only concerned with what you try to post, not what you might be able to copy in the final post.
    So we could also use the black character trick to post some forbidden profanities, I guess… or even a white character trick…
    Hope that makes sense. I don’t really know more about it, I expect you understand these things better than me, - I did once do a lot of this internet security stuff, but my memory is fading and my knowledge is out of date probably, and *** I won’t enter into any more discussions about this, since I might start giving away to the spammers useful info… ( … and then you might report me again, Lol ! )

    Molly

  13. #13
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: mixing Excel with code editors (IDEs)

    Quote Originally Posted by MollyBrennholz View Post
    ( … and then you might report me again, Lol ! )
    I reported you for a reason! None of what you did regarding my post, the rep, or the PMs made any sense at all. So naturally my reaction is to ask the mod and admin about you. Thanks for your last post. I will read it thoroughly and get back to you later (repost here) if anything else is unclear. Have a good day.

+ 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. opening multiple VBA editors in Excel 2013
    By harold-sih in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2016, 11:52 AM
  2. Can I differntiate between Viewers and Editors
    By Apache_sim in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 05:01 AM
  3. Mixing Excel Formulas/Functions with VBA
    By garithmar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-23-2013, 03:26 PM
  4. Excel files mixing together
    By bn.ahhs in forum Excel General
    Replies: 0
    Last Post: 08-06-2008, 04:30 PM
  5. [SOLVED] MS Excel 2003 - inserting an editors name into a cell
    By TP GS 180506 in forum Excel General
    Replies: 3
    Last Post: 05-18-2006, 10:35 AM
  6. [SOLVED] Way keep track of edits and editors
    By M John in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2006, 05:10 PM
  7. [SOLVED] Can we use the mouse wheel in the VBA module editors?
    By Stefano Gatto in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2005, 01:10 PM

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