Incremental loop in excel

January 15, 2010 at 07:34:55
Specs: Windows XP
Hi all,
I would like to know if I can create a macro to move through cells from 1-60, copy each of their names and paste it out into a hyperlink window to a .doc file with the same title as the data in the cell. Is this possible?
thanks

See More: Incremental loop in excel

Report •


#1
January 15, 2010 at 08:11:11
Hi,

When you say paste it out into a hyperlink window to a .doc file do you mean that the text in the 60 cells are used to create hyperlinks in another group of 60 cells.

For example if cell A2 contains "C:\MyDocuments\TestDoc01.doc"
then have cell B2 contain text "TestDoc01" which is hyperlinked to that document.
So that clicking cell B2 will open TestDoc01.doc in Word.

Or are you looking to do something else?

It is certainly possible to have a macro go through 60 cells, take the text from the cells and create hyperlinks.

Regards


Report •

#2
January 15, 2010 at 09:13:28
What Im hoping to do is. Say for example in cell d5 there is a name, Mr Alan Green. I want to be able to put a hyperlink beside his name that will link to a file called Mr Alan Green.doc. However, I have 56 sheets with different names and want to be able to create a macro that will go to d5 on every page, get the name on that cell, open hyperlink properties, paste the name on the cell in, followed by '.doc', then repeat the same thing for every sheet, so that at the end I have 56 documents (already created) that are linked by hyperlink on that persons sheet. thanks again

Report •

#3
January 15, 2010 at 14:42:26
Hi,

This macro will copy names from cell D5 on every worksheet (except the master sheet) and put the names into column A on the master worksheet starting at row 2. The names are hyperlinked to Word documents based on the names.

In the macro there is a line to set the path to the Word documents.
'path to Word documents
strPath = "C:\Temp\"
The last "\" is required.

The code assumes that the Macro is called from a master sheet. You may find this to be more robust if you change the following line to have the actual name of the master worksheet.
strMasterName = ActiveSheet.Name
Instead use strMasterName ="YourMasterSheetName"

The macro could be linked to a command button on the master worksheet.
Worksheet command button:
On the master worksheet create a command button from the Control Toolbox toolbar.
(If this toolbar isn't visible, right click on an existing toolbar and check the Control Toolbox from the list that appears).
Select the button Icon and draw a button.
Right-click the button and select Command Button - Edit and change the name to something useful!
Right-click the button again and select View Code.
In the visual basic code window that opens enter the code.
Note that Private Sub CommandButton1_Click() and End Sub will already be there, so don't duplicate them.
Option Explicit goes before the line Private Sub CommandButton1_Click().

Option Explicit

Private Sub CommandButton1_Click()
Dim strMasterName As String
Dim wsEach As Worksheet
Dim intRowCount As Integer
Dim strPath As String

On Error GoTo ErrHnd

'path to Word documents
strPath = "C:\Temp\"

'assume that the worksheet calling this macro is the master
strMasterName = ActiveSheet.Name

'set first row offset
intRowCount = 1
With ActiveWorkbook
    'loop through each worksheet
    For Each wsEach In .Worksheets()
        'don't look in the Master sheet for a name
        If wsEach.Name <> strMasterName Then
            'put name in first column
            .Worksheets(strMasterName).Range("A1").Offset(intRowCount, 0).Value = _
                wsEach.Range("D5").Text
            'create hyperlink
            .Worksheets(strMasterName).Hyperlinks.Add _
                Worksheets(strMasterName).Range("A1").Offset(intRowCount, 0), _
                    strPath & wsEach.Range("D5").Text & ".doc"
            'increment row
            intRowCount = intRowCount + 1
        End If
    Next wsEach
    .Worksheets(strMasterName).Range("A:A").Columns.AutoFit
End With
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

After entering the code click 'Save' from the Visual Basic Menu.
Use Alt+f11 (the Alt key and function key #11 together), to swap back to the main Excel window.
Exit Design mode - click the first icon on the Control Toolbox toolbar.
Click the Command button to run the code.

Regards


Report •

Related Solutions

#4
January 15, 2010 at 14:52:04
Humar,
I am very grateful for your help, I will try this at the office on Monday, I hope it works! I'll keep you posted.

Report •

#5
January 15, 2010 at 14:56:21
Hi,

I re-read your post and realized you wanted the hyperlinks on each page.
Here is a modified macro that puts the hyperlink in cell E5 on each sheet:

Private Sub CommandButton1_Click()
Dim strMasterName As String
Dim wsEach As Worksheet
Dim strPath As String

On Error GoTo ErrHnd

'path to Word documents
strPath = "C:\Temp\"

'assume that the worksheet calling this macro is the master
strMasterName = ActiveSheet.Name

With ActiveWorkbook
    'loop through each worksheet
    For Each wsEach In .Worksheets()
        'don't look in the Master sheet for a name
        If wsEach.Name <> strMasterName Then
            'put name in Cell E5 on each sheet
            wsEach.Range("E5").Value = wsEach.Range("D5").Text
            'create hyperlink
            wsEach.Hyperlinks.Add wsEach.Range("E5"), _
                strPath & wsEach.Range("D5").Text & ".doc"
            'adjust cell width
            wsEach.Range("E5").Columns.AutoFit
        End If
    Next wsEach
End With
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Regards


Report •


Ask Question