how to reference a range in a different worksheet

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
I am stumped. how do you reference a cell range in another worksheet.
from sheet1 I want to reference the range called 'mail' in sheet2. is this possible?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

marcodane

New Member
Joined
Oct 27, 2002
Messages
21
In the cell in 'sheet1' enter:

='<tabname>'!<col><row>

In your case:

='mail'!B15

or whatever cell/range you are trying to reference.

Hope it helps!

MD
 

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
I am doing something wrong. I am still getting an error.
Here is the line I keep getting erros on:

olMail.To = Worksheets("Sheet2").Range("mail")

This is part of the message that says 'ok, who do you want emails going to?' - it works fine if the cell range 'mail' is on the same worksheet. But it doesnt want to reference a cell range on another worksheet that I have the user define email addresses. I am not sure what "! does either. I wouldnt know where to put it.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Try:

mailto = Worksheets("Sheet2").Range("mail")
olMail.To = mailto

or:

olMail.To = ThisworkBook.Worksheets("Sheet2").Range("mail")
 

mikefromUK

New Member
Joined
Oct 15, 2002
Messages
42
All

The eventual purpose of the program below is the to ask the user into input two strings FindString and ReplaceString. It then opens all the excel workbooks (both current and in directory(s) below it), find the links within a workbook, find Findstring, substitute it with ReplaceString, and see if the link exists.

At the moment, I just want to display all the links in a excel workbook, for all the workbooks.

The following piece of code is giving me an type mismatch error.

Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)

As far as I can see, it is fine.

Any ideas on what is wrong?

Thanks

Michael



Option Explicit


Sub main()
' Global search and replace (including subdirectories) text so as to update the links in a series of spreadsheets
' by Michael Gibson
' Version 1.00
' 27 Aug 2002


Application.Calculation = xlCalculationManual ' this will force the sheet not to recalculate as their is no explicit calculate command in this program


Dim strFindString 'as string
Dim strReplaceString 'as string
Dim objFSO 'as object
Dim objFolder 'as object
Dim objFile 'as object
Dim objCurrentFolder 'as folder
Dim File 'as file
Dim intResponse 'as interger
Dim bolFinishProgram, bolStopProgram As Boolean
Dim intResponse2, intResponse3, intReplaceCheck, intReverse
Dim strSecondReplaceString 'as string
Dim ComboBox1 As ComboBox
Dim intUserOption As Integer
Dim strInputOptions(1 To 10) As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName("."))
Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName(Workbooks(1).Path))
'MsgBox ("Workbooks(1).path =" & Workbooks(1).Path)
'MsgBox ("Workbooks(1).Name =" & Workbooks(1).Name)
'MsgBox ("objFSO.GetDriveName(Workbooks(1).Path) = " & objFSO.GetDriveName(Workbooks(1).Path))

'MsgBox ("Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & " = (Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & ""))

Do

strFindString = InputBox("Please input the string you are thinking of replacing?")
strReplaceString = InputBox("Please input the string you are thinking replacing '" & strFindString & "' with?")
intResponse = MsgBox("Do you wish to search for possible errors if '" & strFindString & "' is replaced with '" & strReplaceString & "'", 3)

Loop Until intResponse <> vbNo

If intResponse = vbYes Then FindPossibleErrors objFSO, objFolder, strFindString, strReplaceString


End Sub


Sub FindPossibleErrors(objFSO, objCurrentFolder, strFindString, strReplaceString)

Dim objFile
Dim objWorkSheet
Dim objLocalFolder
Dim rngCurrentCell As Range
Dim strCurrentCellFormula
Dim arrWorkbookLinks
Dim intLinksCounter

Open Workbooks(1).Path & "PossibleErrorsList.txt" For Output As #1

For Each objFile In objCurrentFolder.Files
If (objFSO.GetExtensionName(objFile.Name) = "xls") And (objFile.Name <> "findpossibleerrors.xls") Then 'check if program is Excel
Workbooks.Open FileName:=objCurrentFolder.Path & "" & objFile.Name, UpdateLinks:=False
Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)
For intLinksCounter = 1 To UBound(arrWorkbookLinks)
MsgBox (arrWorkbookLinks(arrWorkbookLinks))
Next
Workbooks.Item(objFile.Name).Close
End If
Next

For Each objLocalFolder In objCurrentFolder.subfolders
FindPossibleErrors objFSO, objLocalFolder, strFindString, strReplaceString
Next

Close #1

End Sub
 

mikefromUK

New Member
Joined
Oct 15, 2002
Messages
42

ADVERTISEMENT

All

The eventual purpose of the program below is the to ask the user into input two strings FindString and ReplaceString. It then opens all the excel workbooks (both current and in directory(s) below it), find the links within a workbook, find Findstring, substitute it with ReplaceString, and see if the link exists.

At the moment, I just want to display all the links in a excel workbook, for all the workbooks.

The following piece of code is giving me an type mismatch error.

Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)

As far as I can see, it is fine.

Any ideas on what is wrong?

Thanks

Michael



Option Explicit


Sub main()
' Global search and replace (including subdirectories) text so as to update the links in a series of spreadsheets
' by Michael Gibson
' Version 1.00
' 27 Aug 2002


Application.Calculation = xlCalculationManual ' this will force the sheet not to recalculate as their is no explicit calculate command in this program


Dim strFindString 'as string
Dim strReplaceString 'as string
Dim objFSO 'as object
Dim objFolder 'as object
Dim objFile 'as object
Dim objCurrentFolder 'as folder
Dim File 'as file
Dim intResponse 'as interger
Dim bolFinishProgram, bolStopProgram As Boolean
Dim intResponse2, intResponse3, intReplaceCheck, intReverse
Dim strSecondReplaceString 'as string
Dim ComboBox1 As ComboBox
Dim intUserOption As Integer
Dim strInputOptions(1 To 10) As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName("."))
Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName(Workbooks(1).Path))
'MsgBox ("Workbooks(1).path =" & Workbooks(1).Path)
'MsgBox ("Workbooks(1).Name =" & Workbooks(1).Name)
'MsgBox ("objFSO.GetDriveName(Workbooks(1).Path) = " & objFSO.GetDriveName(Workbooks(1).Path))

'MsgBox ("Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & " = (Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & ""))

Do

strFindString = InputBox("Please input the string you are thinking of replacing?")
strReplaceString = InputBox("Please input the string you are thinking replacing '" & strFindString & "' with?")
intResponse = MsgBox("Do you wish to search for possible errors if '" & strFindString & "' is replaced with '" & strReplaceString & "'", 3)

Loop Until intResponse <> vbNo

If intResponse = vbYes Then FindPossibleErrors objFSO, objFolder, strFindString, strReplaceString


End Sub


Sub FindPossibleErrors(objFSO, objCurrentFolder, strFindString, strReplaceString)

Dim objFile
Dim objWorkSheet
Dim objLocalFolder
Dim rngCurrentCell As Range
Dim strCurrentCellFormula
Dim arrWorkbookLinks
Dim intLinksCounter

Open Workbooks(1).Path & "PossibleErrorsList.txt" For Output As #1

For Each objFile In objCurrentFolder.Files
If (objFSO.GetExtensionName(objFile.Name) = "xls") And (objFile.Name <> "findpossibleerrors.xls") Then 'check if program is Excel
Workbooks.Open FileName:=objCurrentFolder.Path & "" & objFile.Name, UpdateLinks:=False
Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)
For intLinksCounter = 1 To UBound(arrWorkbookLinks)
MsgBox (arrWorkbookLinks(arrWorkbookLinks))
Next
Workbooks.Item(objFile.Name).Close
End If
Next

For Each objLocalFolder In objCurrentFolder.subfolders
FindPossibleErrors objFSO, objLocalFolder, strFindString, strReplaceString
Next

Close #1

End Sub
 

mikefromUK

New Member
Joined
Oct 15, 2002
Messages
42
All

The eventual purpose of the program below is the to ask the user into input two strings FindString and ReplaceString. It then opens all the excel workbooks (both current and in directory(s) below it), find the links within a workbook, find Findstring, substitute it with ReplaceString, and see if the link exists.

At the moment, I just want to display all the links in a excel workbook, for all the workbooks.

The following piece of code is giving me an type mismatch error.

Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)

As far as I can see, it is fine.

Any ideas on what is wrong?

Thanks

Michael



Option Explicit


Sub main()
' Global search and replace (including subdirectories) text so as to update the links in a series of spreadsheets
' by Michael Gibson
' Version 1.00
' 27 Aug 2002


Application.Calculation = xlCalculationManual ' this will force the sheet not to recalculate as their is no explicit calculate command in this program


Dim strFindString 'as string
Dim strReplaceString 'as string
Dim objFSO 'as object
Dim objFolder 'as object
Dim objFile 'as object
Dim objCurrentFolder 'as folder
Dim File 'as file
Dim intResponse 'as interger
Dim bolFinishProgram, bolStopProgram As Boolean
Dim intResponse2, intResponse3, intReplaceCheck, intReverse
Dim strSecondReplaceString 'as string
Dim ComboBox1 As ComboBox
Dim intUserOption As Integer
Dim strInputOptions(1 To 10) As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName("."))
Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName(Workbooks(1).Path))
'MsgBox ("Workbooks(1).path =" & Workbooks(1).Path)
'MsgBox ("Workbooks(1).Name =" & Workbooks(1).Name)
'MsgBox ("objFSO.GetDriveName(Workbooks(1).Path) = " & objFSO.GetDriveName(Workbooks(1).Path))

'MsgBox ("Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & " = (Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & ""))

Do

strFindString = InputBox("Please input the string you are thinking of replacing?")
strReplaceString = InputBox("Please input the string you are thinking replacing '" & strFindString & "' with?")
intResponse = MsgBox("Do you wish to search for possible errors if '" & strFindString & "' is replaced with '" & strReplaceString & "'", 3)

Loop Until intResponse <> vbNo

If intResponse = vbYes Then FindPossibleErrors objFSO, objFolder, strFindString, strReplaceString


End Sub


Sub FindPossibleErrors(objFSO, objCurrentFolder, strFindString, strReplaceString)

Dim objFile
Dim objWorkSheet
Dim objLocalFolder
Dim rngCurrentCell As Range
Dim strCurrentCellFormula
Dim arrWorkbookLinks
Dim intLinksCounter

Open Workbooks(1).Path & "PossibleErrorsList.txt" For Output As #1

For Each objFile In objCurrentFolder.Files
If (objFSO.GetExtensionName(objFile.Name) = "xls") And (objFile.Name <> "findpossibleerrors.xls") Then 'check if program is Excel
Workbooks.Open FileName:=objCurrentFolder.Path & "" & objFile.Name, UpdateLinks:=False
Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)
For intLinksCounter = 1 To UBound(arrWorkbookLinks)
MsgBox (arrWorkbookLinks(arrWorkbookLinks))
Next
Workbooks.Item(objFile.Name).Close
End If
Next

For Each objLocalFolder In objCurrentFolder.subfolders
FindPossibleErrors objFSO, objLocalFolder, strFindString, strReplaceString
Next

Close #1

End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The LinkSources Method returns an array of links in the workbook not an object. So you don't need the Set keyword.

You can loop around the array like this:

Code:
Option Base 1
For x = 1 To Ubound(arrWorkbookLinks)
   MsgBox x
Next x
 

mikefromUK

New Member
Joined
Oct 15, 2002
Messages
42
All

The eventual purpose of the program below is the to ask the user into input two strings FindString and ReplaceString. It then opens all the excel workbooks (both current and in directory(s) below it), find the links within a workbook, find Findstring, substitute it with ReplaceString, and see if the link exists.

At the moment, I just want to display all the links in a excel workbook, for all the workbooks.

The following piece of code is giving me an type mismatch error.

Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)

As far as I can see, it is fine.

Any ideas on what is wrong?

Thanks

Michael



Option Explicit


Sub main()
' Global search and replace (including subdirectories) text so as to update the links in a series of spreadsheets
' by Michael Gibson
' Version 1.00
' 27 Aug 2002


Application.Calculation = xlCalculationManual ' this will force the sheet not to recalculate as their is no explicit calculate command in this program


Dim strFindString 'as string
Dim strReplaceString 'as string
Dim objFSO 'as object
Dim objFolder 'as object
Dim objFile 'as object
Dim objCurrentFolder 'as folder
Dim File 'as file
Dim intResponse 'as interger
Dim bolFinishProgram, bolStopProgram As Boolean
Dim intResponse2, intResponse3, intReplaceCheck, intReverse
Dim strSecondReplaceString 'as string
Dim ComboBox1 As ComboBox
Dim intUserOption As Integer
Dim strInputOptions(1 To 10) As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName("."))
Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName(Workbooks(1).Path))
'MsgBox ("Workbooks(1).path =" & Workbooks(1).Path)
'MsgBox ("Workbooks(1).Name =" & Workbooks(1).Name)
'MsgBox ("objFSO.GetDriveName(Workbooks(1).Path) = " & objFSO.GetDriveName(Workbooks(1).Path))

'MsgBox ("Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & " = (Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & ""))

Do

strFindString = InputBox("Please input the string you are thinking of replacing?")
strReplaceString = InputBox("Please input the string you are thinking replacing '" & strFindString & "' with?")
intResponse = MsgBox("Do you wish to search for possible errors if '" & strFindString & "' is replaced with '" & strReplaceString & "'", 3)

Loop Until intResponse <> vbNo

If intResponse = vbYes Then FindPossibleErrors objFSO, objFolder, strFindString, strReplaceString


End Sub


Sub FindPossibleErrors(objFSO, objCurrentFolder, strFindString, strReplaceString)

Dim objFile
Dim objWorkSheet
Dim objLocalFolder
Dim rngCurrentCell As Range
Dim strCurrentCellFormula
Dim arrWorkbookLinks
Dim intLinksCounter

Open Workbooks(1).Path & "PossibleErrorsList.txt" For Output As #1

For Each objFile In objCurrentFolder.Files
If (objFSO.GetExtensionName(objFile.Name) = "xls") And (objFile.Name <> "findpossibleerrors.xls") Then 'check if program is Excel
Workbooks.Open FileName:=objCurrentFolder.Path & "" & objFile.Name, UpdateLinks:=False
Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)
For intLinksCounter = 1 To UBound(arrWorkbookLinks)
MsgBox (arrWorkbookLinks(arrWorkbookLinks))
Next
Workbooks.Item(objFile.Name).Close
End If
Next

For Each objLocalFolder In objCurrentFolder.subfolders
FindPossibleErrors objFSO, objLocalFolder, strFindString, strReplaceString
Next

Close #1

End Sub
 

Forum statistics

Threads
1,144,434
Messages
5,724,334
Members
422,545
Latest member
zillafreak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top