• 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...

  • 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

C

claudia80

Member
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
 
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
 
claudia80 said:
the macro is not embedded in the file
Click to expand...
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.
 
claudia80 said:
I entered the code and saved the file with macro activation. After I press f8 it will not find anything.
Click to expand...
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.
 
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
 
claudia80 said:
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.
Click to expand...
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.
 
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.
 
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.
 
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.
 
Yes. Replace complete routine "cmdUpdateCapital_Click" in the code by below one.
Code: 
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
 
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".
 
claudia80 said:
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".
Click to expand...
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: 
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 said:
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".
Click to expand...
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:
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
 
stefanoste78 said:
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
Click to expand...
I am little confused:confused:

Do you have two IDs on Chandoo.org forums, stefanoste78 and claudia80?
 
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.
 
You must log in or register to reply here.
Back
Top