How to automatically hyperlink to folders from Excel

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have 500 folders with a specific string of names
e.g., Aseem-tb-rd-001, Taj-tb-rd-006, etc..

Each folder has two sub-folders, exactly named In and Out.

Now I have an Excel file with sheets named In and Out in it,
Both these sheets have the respective string of names. I need Excel to automatically fetch the folder and add the hyperlink.

e.g., if cell H3 in sheet In has the name Taj-tb-rd-006, I want the hyperlink to link to the folder Taj-tb-rd-006In
and if cell H8 in sheet Out has the name Taj-tb-rd-006, I want the hyperlink to link to the folder Taj-tb-rd-006Out.

Can this be done by any means?

edited Jan 3 '17 at 18:54

Scott

16.2k113990

asked Jan 3 '17 at 13:59

muhammed ayyoob

Welcome to SU! Yes, you can write a VBA macro that uses the content of the cells to make a hyperlink.

– duDE
Jan 3 '17 at 14:03

There is a formula called hyperlink. A combo of this formula and the name manager is how I would approach this task. IS this enough or is there more that you need?

– bvaughn
Jan 3 '17 at 14:46

If you could help me with how to do it, I would be more than gratefull.

– muhammed ayyoob
Jan 3 '17 at 15:12

add a comment |

I have 500 folders with a specific string of names
e.g., Aseem-tb-rd-001, Taj-tb-rd-006, etc..

Each folder has two sub-folders, exactly named In and Out.

Now I have an Excel file with sheets named In and Out in it,
Both these sheets have the respective string of names. I need Excel to automatically fetch the folder and add the hyperlink.

Can this be done by any means?

edited Jan 3 '17 at 18:54

Scott

16.2k113990

asked Jan 3 '17 at 13:59

muhammed ayyoob

Welcome to SU! Yes, you can write a VBA macro that uses the content of the cells to make a hyperlink.

– duDE
Jan 3 '17 at 14:03

There is a formula called hyperlink. A combo of this formula and the name manager is how I would approach this task. IS this enough or is there more that you need?

– bvaughn
Jan 3 '17 at 14:46

If you could help me with how to do it, I would be more than gratefull.

– muhammed ayyoob
Jan 3 '17 at 15:12

add a comment |

I have 500 folders with a specific string of names
e.g., Aseem-tb-rd-001, Taj-tb-rd-006, etc..

Each folder has two sub-folders, exactly named In and Out.

Now I have an Excel file with sheets named In and Out in it,
Both these sheets have the respective string of names. I need Excel to automatically fetch the folder and add the hyperlink.

Can this be done by any means?

edited Jan 3 '17 at 18:54

Scott

16.2k113990

asked Jan 3 '17 at 13:59

muhammed ayyoob

I have 500 folders with a specific string of names
e.g., Aseem-tb-rd-001, Taj-tb-rd-006, etc..

Each folder has two sub-folders, exactly named In and Out.

Now I have an Excel file with sheets named In and Out in it,
Both these sheets have the respective string of names. I need Excel to automatically fetch the folder and add the hyperlink.

Can this be done by any means?

microsoft-excel microsoft-excel-2010 microsoft-excel-2007

edited Jan 3 '17 at 18:54

Scott

16.2k113990

asked Jan 3 '17 at 13:59

muhammed ayyoob

edited Jan 3 '17 at 18:54

Scott

16.2k113990

asked Jan 3 '17 at 13:59

muhammed ayyoob

edited Jan 3 '17 at 18:54

Scott

16.2k113990

edited Jan 3 '17 at 18:54

Scott

16.2k113990

edited Jan 3 '17 at 18:54

Scott

16.2k113990

asked Jan 3 '17 at 13:59

muhammed ayyoob

asked Jan 3 '17 at 13:59

muhammed ayyoob

asked Jan 3 '17 at 13:59

muhammed ayyoob

Welcome to SU! Yes, you can write a VBA macro that uses the content of the cells to make a hyperlink.

– duDE
Jan 3 '17 at 14:03

There is a formula called hyperlink. A combo of this formula and the name manager is how I would approach this task. IS this enough or is there more that you need?

– bvaughn
Jan 3 '17 at 14:46

If you could help me with how to do it, I would be more than gratefull.

– muhammed ayyoob
Jan 3 '17 at 15:12

add a comment |

Welcome to SU! Yes, you can write a VBA macro that uses the content of the cells to make a hyperlink.

– duDE
Jan 3 '17 at 14:03

There is a formula called hyperlink. A combo of this formula and the name manager is how I would approach this task. IS this enough or is there more that you need?

– bvaughn
Jan 3 '17 at 14:46

If you could help me with how to do it, I would be more than gratefull.

– muhammed ayyoob
Jan 3 '17 at 15:12

Welcome to SU! Yes, you can write a VBA macro that uses the content of the cells to make a hyperlink.

– duDE
Jan 3 '17 at 14:03

There is a formula called hyperlink. A combo of this formula and the name manager is how I would approach this task. IS this enough or is there more that you need?

– bvaughn
Jan 3 '17 at 14:46

If you could help me with how to do it, I would be more than gratefull.

– muhammed ayyoob
Jan 3 '17 at 15:12

add a comment |

1 Answer
1

active

oldest

votes

You can do it using a formula. I would start with:

=HYPERLINK(H3&"out")

Edit
Sorry, misread the question. The other half of the answer is that you need some VBA code like this, modified from this answer on SO:

Sub LoopThroughFiles()

    Dim MyObj As Object, MySource As Object, file As Variant, i As Integer

    file = Dir("D:My Folder", vbDirectory)

    While (file <> "")

       i = i + 1

       ActiveSheet.Cells(i, 1) = file

       file = Dir

   Wend

End Sub

edited May 23 '17 at 12:41

Community♦

answered Jan 3 '17 at 14:42

TheCatsTail

313

Could you elaborate it please. I'm a complete idiot in this .

– muhammed ayyoob
Jan 3 '17 at 15:14

I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in in and one ending in out.

– TheCatsTail
Jan 3 '17 at 15:18

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1162899%2fhow-to-automatically-hyperlink-to-folders-from-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You can do it using a formula. I would start with:

=HYPERLINK(H3&"out")

Edit
Sorry, misread the question. The other half of the answer is that you need some VBA code like this, modified from this answer on SO:

Sub LoopThroughFiles()

    Dim MyObj As Object, MySource As Object, file As Variant, i As Integer

    file = Dir("D:My Folder", vbDirectory)

    While (file <> "")

       i = i + 1

       ActiveSheet.Cells(i, 1) = file

       file = Dir

   Wend

End Sub

edited May 23 '17 at 12:41

Community♦

answered Jan 3 '17 at 14:42

TheCatsTail

313

Could you elaborate it please. I'm a complete idiot in this .

– muhammed ayyoob
Jan 3 '17 at 15:14

I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in in and one ending in out.

– TheCatsTail
Jan 3 '17 at 15:18

add a comment |

You can do it using a formula. I would start with:

=HYPERLINK(H3&"out")

Edit
Sorry, misread the question. The other half of the answer is that you need some VBA code like this, modified from this answer on SO:

Sub LoopThroughFiles()

    Dim MyObj As Object, MySource As Object, file As Variant, i As Integer

    file = Dir("D:My Folder", vbDirectory)

    While (file <> "")

       i = i + 1

       ActiveSheet.Cells(i, 1) = file

       file = Dir

   Wend

End Sub

edited May 23 '17 at 12:41

Community♦

answered Jan 3 '17 at 14:42

TheCatsTail

313

Could you elaborate it please. I'm a complete idiot in this .

– muhammed ayyoob
Jan 3 '17 at 15:14

I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in in and one ending in out.

– TheCatsTail
Jan 3 '17 at 15:18

add a comment |

You can do it using a formula. I would start with:

=HYPERLINK(H3&"out")

Edit
Sorry, misread the question. The other half of the answer is that you need some VBA code like this, modified from this answer on SO:

Sub LoopThroughFiles()

    Dim MyObj As Object, MySource As Object, file As Variant, i As Integer

    file = Dir("D:My Folder", vbDirectory)

    While (file <> "")

       i = i + 1

       ActiveSheet.Cells(i, 1) = file

       file = Dir

   Wend

End Sub

edited May 23 '17 at 12:41

Community♦

answered Jan 3 '17 at 14:42

TheCatsTail

313

You can do it using a formula. I would start with:

=HYPERLINK(H3&"out")

Edit
Sorry, misread the question. The other half of the answer is that you need some VBA code like this, modified from this answer on SO:

Sub LoopThroughFiles()

    Dim MyObj As Object, MySource As Object, file As Variant, i As Integer

    file = Dir("D:My Folder", vbDirectory)

    While (file <> "")

       i = i + 1

       ActiveSheet.Cells(i, 1) = file

       file = Dir

   Wend

End Sub

edited May 23 '17 at 12:41

Community♦

answered Jan 3 '17 at 14:42

TheCatsTail

313

edited May 23 '17 at 12:41

Community♦

edited May 23 '17 at 12:41

Community♦

edited May 23 '17 at 12:41

Community♦

answered Jan 3 '17 at 14:42

TheCatsTail

313

answered Jan 3 '17 at 14:42

TheCatsTail

313

answered Jan 3 '17 at 14:42

TheCatsTail

313

Could you elaborate it please. I'm a complete idiot in this .

– muhammed ayyoob
Jan 3 '17 at 15:14

I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in in and one ending in out.

– TheCatsTail
Jan 3 '17 at 15:18

add a comment |

Could you elaborate it please. I'm a complete idiot in this .

– muhammed ayyoob
Jan 3 '17 at 15:14

I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in in and one ending in out.

– TheCatsTail
Jan 3 '17 at 15:18

Could you elaborate it please. I'm a complete idiot in this .

– muhammed ayyoob
Jan 3 '17 at 15:14

I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in in and one ending in out.

– TheCatsTail
Jan 3 '17 at 15:18

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Jtdylktuy