Posts by FierceMouse

    Hello, All!


    I have a userform and am attempting to search through an array, see if something (the url) is there, if it isn't there then add a number (or other tally mark I can remove later) to it's neighbor, but if it is there just recreate the entry that is already there and let the neighbor stay as is.


    Here's the workbook: Source and Animal.xlsm, no colors or other formatting needs to be maintained.



    I'm wondering if maybe stringtofind is looking for my value as a portion of string versus as an exact match. Also not sure of the best way to set up a loop for my y, though this method of checking if the radio button was checked worked great and was trying to gain inspiration from that. Had at one point just had it add a "|" as a tally so I could just replace those later, so I think that could be a workable other approach. It was at least working moreorless and then in removing identifying features by making a new mini userform, lost some functionality, but if anyone could just lead me in the right direction to another concept or even another post that that would be appreciated.



    Thank you!


    Okay, so I feel like I've tried a bajillion things and was attempting to think outside the box so I could at least try my other code.


    Almost there:

    ready state issue, found workaround

    capturing the newest url, got it

    credential requirements, non applicable



    So this works well enough, and with my intranet urls it can pull it up the first time and it's fine so I know it's not a credentials thing.


    I put this code with the surrogate URL.


    This portion:

    Code
    1. "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""http://disclosure.bursamalaysia.com/FileAccess/viewHtml?e=2891609"";Extended Properties="""""), Destination:=Range("$A$11")).QueryTable


    With mine being like this:

    URL 1 --- https://abc.XYZ.org/.../v12/

    URL 2 --- https://abc.XYZ.org/.../cgi/..…engines=Bing&flags=genius

    URL 3 --- https://abc.XYZ.org/.../v12//main.html?path=110746_29570

    Then gets split:


    Code
    1. "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""https://abc.XYZ.org/.../v12//main.html?path=" _
    2. , "=110746_29570"";Extended Properties="""""), Destination:=Range("$A$11")).QueryTable


    Which actually could be cool if it recognized strings. What am I missing that it won't allow me to put a string (URLtail) here or any of the other portions where it is putting entire URLs? If I take out any quotations it gets immediately upset expecting a list separator.


    Thanks again for your thoughts!


    Code
    1. "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""https://abc.XYZ.org/.../v12//main.html?path=" _
    2. , "URLtail"";Extended Properties="""""), Destination:=Range("$A$11")).QueryTable





    Thanks, Alan! I was digging into this a bit since my post. And I'm 90% sure if I can get past this ready state issue, capturing the newest url, and possibly the credential requirements, I won't have any trouble at all getting capturing I need from the URL 3... Ok, maybe that's still a lot to tackle.


    Also in my perusing solutions I also appreciated from Pike regarding pulling the table:

    VBA retrieve Internet Explorer HTML Web Table


    I guess I missed the boat on realizing there is a "Get Data" ... "From Web" option under the Data tab. Obvious to those who it is obvious to, but not those of use who all the options in the ribbon still can kind of blur together. For other newbs like myself, this is how I got some assist in recording a macro for the finer details.

    I have looked at over 50 posts through various forums, working through at least 5-6 examples, and continue to get an error.

    Of those one was: Pull Web Page Into Worksheet


    I would be happy if any web browser worked, so if you have an existing code that would make your response easier with a preferred browser, don't let my first attempts with IE deter you.


    The main barrier that I predict is that it has multiple pause points and differing url's for them before the content I want shows up. It's an intranet site but I was able to get it to still pull in a cell from the final site but not .


    URL 1 is an intranet search engine, enter value into input box, click and it sends you to URL 2 (which has search string added on) but is just progress bars, URL 3 (with unique tail each search) is now loaded with the table (or whole page is fine) that I need copied.


    They follow this formatting:

    URL 1 --- https://abc.XYZ.org/.../v12/

    URL 2 --- https://abc.XYZ.org/.../cgi/..…engines=Bing&flags=genius

    URL 3 --- https://abc.XYZ.org/.../v12//main.html?path=110746_29570


    So I've attempted where I control IE and enter in the input box and click as well as where I just create the URL 2 as a search string. I can get URL 3 to come up as expected but idk if it's because it's a new URL (not new window) it's not seeing it as the active object to copy from?


    I've tried so many things outside of code as well, clearing cache/cookies, thinking maybe I don't have the right reference library I am now up to this mess...



    I have reviewed concepts of capturing a url from an existing window, copying by tags and class, etc. But I keep getting errors like:


    Method 'Busy' of object 'IWebBrowser2' failed, or if I lead it with Document then that is the method it stops on.


    Do While objIE.Document.ReadyState <> 4 Or objIE.Busy = True: DoEvents: Loop

    Do Until objIE.Document.ReadyState = 4: DoEvents: Loop


    Automation error


    Unspecificed error



    I cannot add extensions to Chrome as they are blocked by admin as well as cannot install things or I'd attempt to get a macro recorder for my browser.



    Thank you for all your help.



    I'll paste code here, but since I can't give you the site, idk how much use it will be other than to see some of the different approaches I tried. Here's a blank workbook with the code inside: CopyWebContent.xlsm


    This site may be an ok surrogate for URL 3: http://disclosure.bursamalaysi…Access/viewHtml?e=2891609




    PS I appreciate these developed answers too more than just copying and pasting them. I try to follow where they are referencing and it's great to have real-to-me examples that now I can google things like "Scripting.Dictionary", .CompareMode, IIf, and why the heck this "Mid$" has a dollar sign in it. So, thank you for the opportunity to learn at a deeper level.

    Hello!


    How should I go about getting VBA to look through an array, see that a repeat is in there and then have it combine the repeats into a list?


    If ... value in cell is not unique in the column (sans header of course)... then store values to the left to then be joined with a delimiter in another column.


    Visually:


    I don't need to preserve colors, column widths, or any other formatting. I thought I'd use household products as the data I am actually using is abstract and may not get the idea across as well. Here is the workbook I took the above screenshot from: Source and Product.xlsx


    Is this task going to be best approached through making a dictionary or a collection? Or something else? I haven't used either before. I'm just getting ok with loops and arrays in general.


    Also, the order in which the sources appear do not necessarily need to be maintained either.



    Thanks in advance!

    Hey, KjBox. I was trying to change this up a touch in order for it to maintain the previous three columns vs the previous one.


    These are the changes I've made so far in this workbook: Search Terms Tool.xlsm, thinking I could reverse engineer your code and understand how the array is being manipulated, but I think that doing it this way is messing with the 4th column too many times. And of course the adjustments I made cause bugs. I imagine there is a simple line that lets me preserve the three cells to the left rather than preserving row after row and trying to overlay them.


    I had gotten it to behave at one point though not preserve correctly.


    This is what would be stellar. No color, borders, or dimensions, etc. need to be preserved.


    Thanks!

    You know, this works! I appreciate the new angle. I was thinking it would be nice for the user to be aware the hyphens were being added as they typed. Sometimes I get stuck on fancy versus functional. Thanks much, Roy. Super helpful.

    Hello, All!


    I am trying to make a userform text box moreorless like you often see for phone numbers. If you start typing, once you get the area code in, it enters a hyphen. You type the next three it enters another. However, I am entering numbers where the last digit always needs to be separated, and then the preceding two while the first string can be varying lengths.


    That is:

    11095-11-7

    11130-47-5

    1392204-77-1

    50356-21-3

    51274-03-4

    52001-89-5

    63-25-2


    I've tried combinations of the Left, Right, Mid, but the problem with this approach is that starts to include the hyphen in the count. I've tried to think about how to access / store it within a string but that fell flat as well. This seemed the most promising, but maybe not.


    Code
    1. Private Sub CASTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    2. KeyAscii = KeyAscii * -CLng(Chr(KeyAscii) Like "[0-9]")
    3. Select Case Len(CASTextBox)
    4. Case Is > 3: CASTextBox = Left(CASTextBox, Len(CASTextBox) - 4) & "-" & _
    5. Mid(CASTextBox, Len(CASTextBox) - 4, 2) & "-" & _
    6. Right(CASTextBox, 2)
    7. End Select
    8. End Sub


    I've attached a workbook ( CAS Hyphens.xlsm ) just for a little playground and starting point apart from my greater machine.



    Thank you for any and all assist. If you would like to send me off with a different approach, I have now taken a short course (8.5 hours of video) on VBA and shouldn't be quite so dense if you try to explain it to me.

    I will try again now that I slept. Communication fails on little sleep as well. Oof. My apologies.


    Extracting nothing from "hexacosafluorohexadecahydrofluoranthene" was an oversight in my copy and pasting trying to demonstrate the desired amount.


    Extracting "hexadecafluorod" from "hexadecafluorodecane", but nothing from "hexadecafluorononyl" because "hexadecafluoro" should be able to catch it because there is less than 5 characters after the "hexadecafluoro".


    If it could compare strings, chop them where they differ, if the number of remaining characters is 5 or less, then keep the value that was preceding the chop. If the remaining characters is greater than 5, then chop at 5 characters less than the end of the string, then keep the value that was preceding the chop.


    I am trying to deal with a mass of search terms in a database that only allows a wildcard that has a maximum of 5 additional characters retrieved. If you think a different approach altogether would work better, I am open to new directions.


    This workbook ( Chemical Roots Clarified.xlsx ) should better demonstrate what I am thinking at the moment.


    I imagine this couple entries that retrieve the same thing. such as "hexafluoro" will catch "hexafluorobutyl" but we might also have "hexafluorobut" or even "hexafluorobuta" or "hexafluorobutan" for catching "hexafluorobutanoic" that catches it as well. These repeats are more than acceptable.



    Again, colors, column widths, and other formatting are not needing to be maintained.


    Thank you so much to both of you.

    Good evening, All,


    This should be simple, but somehow it's escaping me today. I am going to lay down some thoughts and a goal, but dabble on my own in the meanwhile as well.


    I have a long list of chemical names that I am trying to make shorter with wildcards more or less. So I am looking for a method for checking if the front portion of a string is contained within another string with a difference of 5 or less characters and then recording the list into a new column.



    What I am hoping for (Chemical Roots.xlsx) :


    Column sizes and any formatting does not need to be maintained. The center column is just there to more easily see the terms remaining.



    What I have tried:


    Some cumbersome series of formulas that are not overly reproducible (i.e. I will need to do this for 500+ terms) referring to cells and overly relying on the human eye with formulas like combining fixed width text to columns with extensive use of EXACT, this in too many cells: "=MID($B1,COLUMNS($B$1:B$1),1)", a VBA dive into what ended up not being what I needed mistakenly thinking I could use a similarity score, and then realized StrComp isn't helpful thus far either... but I am not the one to say if any of it could still be usable.


    I'm thinking my answer is in substrings, wildcards, or "If ... Like ..." but this is also discouraging due to lack of understanding in looping still. Or the InStr function looks promising... Of course, I'm familiar with Left/Right/Mid function, however, I feel like I have a bunch of puzzle pieces but no idea how to fit them together.


    Thank you so much for your time and thoughts.... and patience. Too little sleep lately for me to think clearly and creatively. I have a feeling someone will have what should have been obvious solution. And I preemptively thank you again for being a more functional human than I in this realm.



    PS Also, if someone wants to share their favorite Udemy course for learning VBA, I am open to suggestion.

    This is actually how it would reflect in my greater workbook. The two empty columns will already be there, ready for the split lines to be there. If columns were to be inserted it would change references to data to the right and I'd like to prevent that. They will not necessarily be right next to it, I would like to be able to specify.


    Sheet1 is the original data, all columns constant, the empty columns will be there, and all of it will be more or less nested between data that I do not want shifted up/down/left/right. If it takes multiple steps, it doesn't really matter to me how many columns are in there, just so that


    The "End Goal" sheet is identical to "Sheet1", just with the data manually put there how I would like the code to put it there.


    Here it is as requested, the only major difference is that I put other columns in there to signify that the original data and the resulting data will not be neighboring columns: Split Test Example Extended.xlsm


    Here is my bigger workbook: Carbaryl Search Terms Snip.xlsm. It is full of bugs and other potentially distracting problems/details that I will will get to at a later date, but then you can see the more or less the full context. In this workbook, the data with multiple lines is in column "S". The destination column for the split data is "W". The tag that is repeated for each line is in "R" to be in "V". This code will be in the sub "RelChemsSubmitButton".


    Again, thank you for all you insight on this project.


    -Mouse

    There are multiple sheets in the above .xlsm whereas the original .xlsx just had the one.


    The most recent example is titled "Sheet1" for ease. The desired results are shown on sheet "End Goal". The original example is the third sheet.


    I have uploaded the .xlsm again with the first sheet activated and with a new title so to not be mistaken with either of the above files that would've been better practice for me to have donein the first place.


    Split Test FierceMouse.xlsm


    Thank you again for your patience.

    Mouse

    I very much appreciate the time invested and creativity in problem solving. There seems to be a miscommunication as to what is my end goal. I apologize.


    Notably, this code chops the first character off of the data. I did not request this in my post either in word or image, though I notice that my uploaded workbook does show this as the desired difference. I had added those letters to the workbook for ease of identifying what moved where while I was working and neglected to notice that I had saved that change before uploading when prompted.


    It also removes the header and tampers with columns outside of A and B (or whichever ones that will be used in my larger workbook). If I need to do this process in another sheet and call it from there to avoid this, that's ok. Just a bonus if it can be all in one place with my other data.


    It looks like it will accept cells with even more lines than were in the example so that's stellar. Some of the user entries could be dozens of lines.


    I also need to have it preserve the original data and put the new list in an assigned column similar to my original code.


    Here is a more simplistic presentation of what my goal is. Colors and borders do not need to be preserved and are again there for clarity in presenting the idea of original data and desired positioning.


    I'll work through the code you provided some more, but request a little more assist.


    Here's the updated workbook: Split Test.xlsm


    Thank you!