Posts by RG26

    Re: Extract URLs from website, name of attachment, and save attachment to a folder $8


    Ok, thanks for the info. I do not want the application to deal with the log in part because the users will already be logged on, and I also do not want user names or passwords in the code. The current example I attached currently pulls all URLs from any website; however, it pulls all URLs and not only the URLs associated with an attachment so I am hoping someone very familiar with connecting to websites via VBA can create the Excel application that I requested.


    Quote from John_w;766140

    Multiple websites means your requirement is too open-ended for me to proceed with this work, and the code to handle a specific website would probably have to be specific to that website, i.e. not generic to all websites. Plus the log in aspect makes it potentially more difficult, if you want the code to do the log in.


    Therefore open to other developers.


    PS you might want to look at https://www.httrack.com/, which with suitable filters and settings should be able to handle your request, including a command line which should be callable from Excel VBA. Can't really help with this because it's many years since I used the HTTrack UI and never from Excel.

    Re: Extract URLs from website, name of attachment, and save attachment to a folder $8


    UPDATE: #1 and #2 can be on the same button if that is easier for coding purposes; that is, when the code that extracts the URLs occurs, it might be easier to grab the file name at that time.


    Re: Extract URLs from website, name of attachment, and save attachment to a folder $8


    1. I will be working with multiple websites, but the websites will only be accessible for a user once they log into the main website. I cannot disclose which websites due to privacy protections.


    2. I need it to work with other websites as well - websites that will potentially have attachments. For example, this website (with my post) contains the attachment file named Download Internet Files Automatically - User-Defined File Names - SAMPLE.xlsm.


    3. For a/the give website, I want the Excel application to download files ONLY for that specific website URL.


    Thanks


    Quote from John_w;766127

    Some questions.


    1. Exactly which website do you want this to work with?
    2. Is it only this one website you need it to work with, or do you need it to work with other websites as well?
    3. For a/the given website, do you want it to scrape URLs and download files for ONLY that specific website URL, or is the given website URL a 'starting point' and you want to scrape URLs and download files for all sibling pages and child pages as well?

    I need an Excel file that contains the below:


    UPDATE: #1 and #2 can be on the same button if that is easier for coding purposes; that is, when the code that extracts the URLs occurs, it might be easier to grab the file name at that time.


    1. A button with code that extracts URLs from a given website that is entered in a cell. I only need URLs that link to an attachment (e.g., .PDF, .XLS, .XLSX, JPEG, .MSG, .DOC, .DOCX, etc.). I might not have listed all the data types that I might encounter, but I need the Excel file to extract URLs for all data types from a given website.


    2. A button with code that extracts the name of the File Name (with extension) as displayed on the website (e.g., the example file that I have posted on this post is named Download Internet Files Automatically - User-Defined File Names - SAMPLE.XLSM and that is file name that I want updated to the Excel file).


    3. A button with code that will save the attachment files (from the website) to a folder that will be selected by the user (see Download Files button in the attached example file).


    4. A button to Clear URLs and File Names (see Clear button in the attached example file).


    5. A button to Select a directory (see Select Folder button in the attached example file).


    The attached file is ONLY an example to help convey what I need; please create a new Excel file but with a similar layout. The attached example file pulls all URLs even if the URLs are NOT tied to an attachment; so, that is NOT what I want in the Excel file I am requesting because I ONLY want URLs related to an attachment as mentioned above in #1. The attached example currently only works for PDF files and not other file types as it did not save correctly a PowerPoint file that I tested. The attached example file and code is from a file I found online and the code was created by Christos Samaras.

    Re: Conditional formatting in a cell with custom format based on text from another c


    Attached is a file using =IF(SEARCH("Temp",$C4),TRUE,FALSE) as the formula rule for the conditional formatting. The Search function is case insensitive so it will pick up temp and Temp, for example.


    Quote from felix.teahiu;723052

    Attached is the workbook i was working on. The Col. C can have many different alarm messages and i want the col. D to have the format accordingly, in my example here the degree symbol+C.

    Re: Counting with requirements but removing duplicates


    Hi there, try adding the additional criteria like below:


    =SUM(IF(FREQUENCY(IF((D$20:D$600="Specific PM Name")*(Q$20:Q$600>=1)*(Q$20:Q$600<=9),MATCH(E$20:E$600,E$20:E$600,0)),ROW(E$20:E$600)-ROW(E$20)+1),1))


    Re: 3 variables put into 1 cell - creating dates


    I would use one of the below functions, datevalue or dateserial. Please note that if a user doesn't fill out one or more of the combo boxes, you might get a data type mismatch error so I would do a check in the code to make sure the combo box values do not equal to "" (empty string"):



    Code
    Range("A2") = DateValue(Sheet1.cboDay.Value & "/" & Sheet1.cboMonth.Value & "/" & Sheet1.cboYear.Value)
    Range("A3") = DateSerial(Sheet1.cboYear.Value, Sheet1.cboMonth.Value, Sheet1.cboDay.Value)



    Re: Conditional formatting in a cell with custom format based on text from another c


    Can you attach a sample workbook where you have the custom format you are referring to?


    Re: Designating ActiveSheet as Worksheet datatype


    If you are clearing data that is located in the workbook where your code resides, use something like:


    Code
    ThisWorkbook.Sheets(WorksheetName).Range(ClearRange).ClearContents


    If you are clearing data that is located in another workbook, use something like this:


    Code
    Workbooks("Book2").Sheets(WorksheetName).Range(ClearRange).ClearContents


    Quote from biocentrism;722940

    Ok that got me through to the Subroutine. But in the Subroutine I need to use the variable as a Worksheet datatype and it fails in the following code:


    Code
    ws.Range(ClearRange).ClearContents


    How would I convert the ws String to an object Worksheet?

    Re: Designating ActiveSheet as Worksheet datatype


    Hi there,


    On your sub routine, try changing WorksheetName As Worksheet to WorksheetName As String


    Quote from biocentrism;722934

    So I tried this and got ByRef Argument type mismatch. I am certain it is caused by the WorksheetName as I when I type it in directly into the Call it works. Any ideas?


    Re: Combining 3 VBA Codes Into 1


    As I mentioned, the macro that adds your delimiter starts out with "For Each cell in Selection", if after your first macro is ran only one cell is selected than it will only loop through that cell. I would add in your code (for Second Macro named AddComma) to either select first the data that you want to add the delimiter too or change the "For Each cell in Selection" to be "For each cell in Range("A1:A50")" for example.

    Re: Combining 3 VBA Codes Into 1


    It looks like the reason it's not adding the commas is becuase your second macro (AddComma) has "For each cell in Selection". I don't where you select the column or data that needs the comma. Looks like in your first macro, you select Column A, then select "A1", and then when you add the zero's, it activates the cells as it's going through the Do While Loop so when the second macro is ran, only that last cells that was selected in the first macro is still selected.


    Like the previous reply stated, I would step through the macros to verify the steps.


    Also an FYI, on your first macro (Macro3), it looks like you need to change the part that has "For i = 1 to endrow" to "For i = 1 to endrow -1" because if you are starting on cell A1 and going down, the loop will go one cell below the last cell.


    Hope this helps.

    Re: Find value between two numbers and display answer


    This might give you something to work with. You would have to change the formula if have more values under the "INFO" part, and you would also need to change the formula if actually wanted to check if there were any values in "between" the values and not equal to the numbers under the "RESULT" part. That's the only things I could think of. Maybe someone else can come up with a better solution.

    Re: Cell Value and Dates


    When you are copying the formula down, it's not "overwriting" the above row's value, it's just that that formula states that if today's date is greater than or equal to the date in column A then give me the value of cell C3 otherwise give me "" which is empty string ("blank cell"). I am not exactly sure what you are trying to do but if you want column B to always show the value of cell C3 irregardless of what the date in column A is then all you have to do is enter the formula as =$C$3 making sure you enter the dollar signs so that when you copy the formula, the cell's reference will remain absolute. However, if you want to compare the date in column A first as you are doing in your formula, then change your formula to =IF(TODAY()>=A1,$C$3,"") making sure you enter the dollar signs (short cut is pressing F4 as you are typing the formula or when you edit the formula make sure the mouse selector is next to or in the cell value C3, for example). Hope this helps.