1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hyperlink and other

Discussion in 'VBA Macros' started by claudia80, Oct 14, 2017.

  1. claudia80

    claudia80 Member

    Messages:
    34
    I would like to include in the "GEONAMES" file a macro (with the start button in cell "1I", with the text in the file in this cell), the hyperlink to cells in column "D" with excel files contained in the "GEONAMES - FILE EXCEL" folder.

    Then, I need two more macros:

    1) one that extracts from each excel file, the name of the capital (column "I" of the "GEONAMES" file. This must start with the "I3" cell button.

    2) a macro that calculates the number of times that the "GEONAMES" file for the "excel file" in the "GEONAMES - FILE EXCEL" folder is repeated (from the "4" line from the "j" column). This macro must start with a button at the cell "I2".

    For point 2) I inserted an excel page "IT" and I put in the formulas to understand what I would like. If you know in the "GEONAMES" column from the "J" column to the "R" column the values to be counted are in column 2G "of the excel files, while from the" S2 "column the values to be counted are in column" H " of each excel file.

    Thank you

    link:

    https://www.dropbox.com/s/17w2e3rbt6ce4rz/macro hyperlink and other.rar?dl=0
  2. claudia80

    claudia80 Member

    Messages:
    34
    there is someone who can help me?
  3. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    You will get help if you wait. I don't have time to build solution in one go. See attached file which handles first part.

    After pressing button, it will prompt you to select a folder (it will start in the same folder where macro file is!). After selecting the folder it will loop through files in the folder and link them to valid cells in column D. Test this and let me know if it works as you need.

    https://www.dropbox.com/s/rl5yvyycioom3sb/GEONAMES.xlsm?dl=0
  4. claudia80

    claudia80 Member

    Messages:
    34
    the macro is not embedded in the file
  5. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    It is there in the file. Just press ALT+F11 keys simultaneously to open Visual Basic Editor window to see the code.

    Once you download the file. You need to press "Enable Macros" and then press the button in cell I1.
  6. claudia80

    claudia80 Member

    Messages:
    34
    I entered the code and saved the file with macro activation. After I press f8 it will not find anything.
  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    You don't need to press ALT+F8 to run it. Button has been inserted in cell I1 as you had asked for. Just press the button.

    Initially, I thought there may be something incorrect about the link I provided but that is not the case as I tested it by downloading in office and it works.
  8. claudia80

    claudia80 Member

    Messages:
    34
    Compliments.
    Now I saw the start button. Maybe he does not go out on a blanket with a version below 2013 or, last night, even though he woke up, I was sleeping in old-fashioned eyes.
    If you go and if you will be free you can fix the other two.
    Thank you
  9. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    I will check those in the evening and see if I can work something out.
  10. claudia80

    claudia80 Member

    Messages:
    34
  11. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    You need to explain the basis for finding Capital in each individual file. I do not see anything that points to the capital identification in the file.
    i.e. Italy's capital is Rome is common knowledge but programme needs indication to pick capital.
    If I have missed it then let me know.
  12. claudia80

    claudia80 Member

    Messages:
    34
    the capitals of each state (corresponds to the text of the column cell "B" of the "PPLC" value of column "H"
  13. claudia80

    claudia80 Member

    Messages:
    34
    For the other macro, Note in the "GEONAMES" column from the "J" column to the "R" column the values to be counted are in the "G" column of the different excel files while, from the column "S22 onwards, the values to be counted are in the column "H" for each excel file. this for the number of times that the row "4" values from the "j-r" column, the "GEONAMES" file, are repeated for each excel file in the "GEONAMES - FILE EXCEL" folder.
  14. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
  15. claudia80

    claudia80 Member

    Messages:
    34
    Hello. the first macro is fine. When trying the second, the macro stuck at a certain point. I have the following error message:

    run time error '91'
    Variable block or object variable with not set

    Clicking on debug highlights this line:

    rng.Offset(0, 5).Value = wksSource.Cells(rngPPLC.Row, "B").Value

    the two macros are independent, that is, can you start the capital macro without first opening the hyperlinks macro, or do you have to activate the hyperlinks macro and capital after that?
    Thank you.
  16. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    1. Two macros are separate. However, first macro verifies all hyperlinks and maps to the available latest files so I will recommend running it before running Macro 2.

    2. The error 91 will occur when object rngPPLC is not set.
    i.e. the particular source workbook where macro is giving error does not have cell containing PPLC in column "H".
    So when it errors just check the currently open two lettered workbook and check its column H.
  17. claudia80

    claudia80 Member

    Messages:
    34
    can you avoid the error by going to other files without blocking the macro?
  18. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    Yes. Replace complete routine "cmdUpdateCapital_Click" in the code by below one.
    Code (vb):
    Private Sub cmdUpdateCapital_Click()
    Dim rng As Range, rngCptCol As Range, rngPPLC As Range
    Dim HLink As Hyperlink
    Dim wbkSource As Workbook
    Dim wksSource As Worksheet

    Application.ScreenUpdating = False
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    For Each rng In Range("D5:D" & Range("D" & Rows.Count).End(xlUp).Row)
        If rng.Hyperlinks.Count > 0 Then
            For Each HLink In rng.Hyperlinks
                If objFSO.FileExists(HLink.Address) Then
                    Set wbkSource = Workbooks.Open(HLink.Address)
                    Set wksSource = wbkSource.Sheets(1)
                    Set rngCptCol = wksSource.Range("1:1").Find("FEATURE CODE", wksSource.Range("A1"), , xlWhole)
                    If Not rngCptCol Is Nothing Then
                        Set rngPPLC = rngCptCol.EntireColumn.Find("PPLC", rngCptCol, , xlWhole)
                        If Not rngPPLC Is Nothing Then rng.Offset(0, 5).Value = wksSource.Cells(rngPPLC.Row, "B").Value
                    End If
                    wbkSource.Close False
                    Exit For
                End If
            Next
        End If
    Next
    Set objFSO = Nothing
    Application.ScreenUpdating = True
    End Sub
  19. claudia80

    claudia80 Member

    Messages:
    34
    I just started the macro with the new instructions.
    I would like to ask you something. I noticed that while the macro is working on the excel sheet it becomes white and in the other the sheet name comes out next to the writing the sheet does not respond. Although the macro seems stuck, the same thing works. To find out if the macro has actually ended its job without stopping, would it be better to enter a final message?
    Now that I've finished I have verified that it hangs in Luxembourg (I opened the Luxembourg file and there is the value "pplc".
  20. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    You can edit the codes. It doesn't mean that I can only do editing part. The codes supplied (generally) are more of guideline than end solution although it may appear so in many cases. User/OP is supposed to work out finer details that affect their situations specifically.

    Having said that you should be able to add line like:
    Code (vb):
    Msgbox "Finished Updating!"
    wherever you need.

    I will take a look at Luxembourg file (if in your uploaded sample) to see as what is happening.
    claudia80 likes this.
  21. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    Code failed on Antarctica (AQ) with the data that you supplied and it doesn't have PPLC row. Please check.

    For question asked on PM, it looks for exact value PPLC and it won't read data from PPLCH etc.

    EDIT: Taking look at third requirement, I think it will be best to handle it in the 2nd button itself as otherwise program will have to go through file opening and closing routine two times. Let me know if it is OK for you.
    Last edited: Oct 18, 2017
  22. stefanoste78

    stefanoste78 Member

    Messages:
    73
    tomorrow I will check all the files where the macro is not of value.
    For the third point, for me it goes as well as you wrote.
    Thank you for the time you are devoting me
  23. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    I am little confused:confused:

    Do you have two IDs on Chandoo.org forums, stefanoste78 and claudia80?
  24. claudia80

    claudia80 Member

    Messages:
    34
    Do not be scared. :)
    Me and my co-worker, we are building a project together. Yesterday at work I used his pc and account to answer you.
  25. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    I have updated the code to handle third part. I have added comments for your reference and information. Macro takes long to update (It took around 20 to 25 minutes for 250 files) as some of the workbooks are very large and their opening consumes time. So anyone who wants to run it shall wait patiently.

    Link on dropbox: https://www.dropbox.com/s/rl5yvyycioom3sb/GEONAMES.xlsm?dl=0

Share This Page