Re: $$Excel-Macros$$ Links

['Minta Spriggs' via MS EXCEL AND VBA MACROS]

On Fri, 5/26/17, KAUSHIK SAVLA <savla.kaus...@gmail.com> wrote:

 Subject: Re: $$Excel-Macros$$ Links
 To: "MS EXCEL AND VBA MACROS" <excel-macros@googlegroups.com>
 Date: Friday, May 26, 2017, 10:10 PM
 
 Hi Paul, 
 
 
 I followed the instructions. 
 
 However what's happening is the macro
 is just showing running and not giving any outcome. 
 
 I kept it running for nearly 3 hours. 
 
 Regards, 
 Kaushik
 
 -- 
 Are you =EXP(E:RT) or =NOT(EXP(E:RT))
 in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) !
 Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
 FORUM RULES
 
 1) Use concise, accurate thread titles.
 Poor thread titles, like Please Help, Urgent, Need Help,
 Formula Problem, Code Problem, and Need Advice will not get
 quick attention or may not be answered.
 2) Don't post a question in the thread
 of another member.
 3) Don't post questions regarding
 breaking or bypassing any security measure.
 4) Acknowledge the responses you
 receive, good or bad.
 5) Jobs posting is not allowed.
 6) Sharing copyrighted material and
 their links is not allowed.
 
 NOTE  : Don't ever post
 confidential data in a workbook. Forum owners and members
 are not responsible for any loss.
 --- 
 You received this message because you
 are subscribed to the Google Groups "MS EXCEL AND VBA
 MACROS" group.
 To unsubscribe from this group and stop
 receiving emails from it, send an email to 
excel-macros+unsubscr...@googlegroups.com.
 To post to this group, send email to excel-macros@googlegroups.com.
 Visit this group at
 https://groups.google.com/group/excel-macros.
 For more options, visit
 https://groups.google.com/d/optout.61in extrema stanga a vietii politice 
interbelice s-au aflat comunistii. De fapt  idul Comunist din Romania a 
actionat ca sectie a Cominternului. in 1920  in ui Partidului Socialist a 
existat o confruntare intre opinia constituirii unui d comunist si afilierea la 
Comintern si cea a unificarii intregii miscari citoresti si apoi afilierea sa 
la forul mai sus mentionat. in toamna lui 1920  o gatie a socialistilor romani 
a vizitat Rusia Sovietica  unde a discutat conditiile fii la organismul 
comunist mondial. Neacceptarea lor de catre losif anca  loan Flueras  G. 
Grigorovici a dus ulterior la excluderea lor din jucerea socialista sub 
presiunea elementelor stangiste. La 8 mai 1921  lentele de extrema stanga din 
Partidul Socialist au pus bazele Partidului nunist. Partidul Socialist s-a 
disociat de acesta. Autoritatile  constiente de  O|UI reprezentat de noua 
organizatie  au intervenit si au arestat pe unii cipanti la Congresul de 
constituire. in 1922 a avut loc  totusi  la Ploiesti  gresul al II  la care au 
participat 34 delegati.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Hi Paul, 


I followed the instructions. 

However what's happening is the macro is just showing running and not giving 
any outcome. 

I kept it running for nearly 3 hours. 

Regards, 
Kaushik

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Links

[Paul Schreiner]

I suspect it's getting an error but the"on error resume next" line is making it 
ignore the error.
Did you change the macro to open your data file?DatFldr = "C:\temp\vba\Savla\"
DatFile = "DataFile.xlsb"
??
Add a line after the "If/Endif" that has the Workbooks.Open line.
the line should read:On Error Goto 0

or maybe put it right BEFORE the workbooks.Open line, so you know if it fails. 
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Friday, May 26, 2017 12:49 PM, KAUSHIK SAVLA <savla.kaus...@gmail.com> 
wrote:
 

 Macro is just running and not fetching any outcome. 
Regards,Kaushik
On May 26, 2017 16:11, "'Martin George' via MS EXCEL AND VBA MACROS" 
<excel-macros@googlegroups.com> wrote:


-- --
On Thu, 5/25/17, Paul Schreiner <schreiner_p...@att.net> wrote:

 Subject: Re: $$Excel-Macros$$ Links
 To: "excel-macros@googlegroups.com " <excel-macros@googlegroups.com >
 Date: Thursday, May 25, 2017, 8:17 PM

 The workbook that you add the VBA macro to is
 identified as "ThisWorkbook".
 so, in the
 macro:

 ThisWorkbook.Sheets(1).Range(" A2:Z65000").ClearContents
 clears
 the contents of the first sheet in
 "Thisworkbook"then:

 ThisWorkbook.Sheets(1).Cells( nRow, "A").Value =
 Sht.Name

 ThisWorkbook.Sheets(1).Cells( nRow, "B").Value =
 Rng.Address 

 If (InStr(1, Rng.Formula, "[") > 0) Then

 ThisWorkbook.Sheets(1).Cells( nRow, "C").Value =
 "'" & Rng.Formula

 Else

 ThisWorkbook.Sheets(1).Cells( nRow, "D").Value =
 "'" & Rng.Formula

 End Ifputs
 the listing on the first sheet of the workbook.
 Paul-- ---
 “Do
 all the good you can,
 By all the means you
 can,
 In all the ways you can,
 In all the places you can,
 At
 all the times you can,
 To all the people you
 can,
 As long as ever you can.” - John
 Wesley
 -- ---



   On Thursday, May 25, 2017 12:50 PM,
 KAUSHIK SAVLA <savla.kaus...@gmail.com> wrote:


  Hi Paul, 
 Macro got run and gave finished
 message. 
 Where can I
 find the desired output? 
 Regards, Kaushik
 On May 25, 2017 19:06,
 "Paul Schreiner" <schreiner_p...@att.net>
 wrote:
 OK.Spent
 WAY longer on this than I should have.
 I
 should've had you create a "dummy" set of
 workbooks that looked like yours instead of me spending an
 hour+ guessing what yours looks like.
 So,
 I put together 20 separate workbooks with data.then
 added 15 sheets to the "data" workbook and
 created 150 random links to both the 20 external workbooks
 and  the internal worksheets.
 It took about 15 minutes
 to write some code to look at the formulas and determine
 which are internal/external formulas.
 It
 DOESN'T distinguish between calculations and other
 formulas.
 Since I don't know what you want to do with
 it, and how you want it to list the matches, I'll just
 give you what I have and you can decide what you want to
 change.
 If it isn't what you
 want, then send me a sample to write against.
 Here it is:
 Sub
 Link_Summary()

 Dim DatFldr, DatFile

 Dim aLinks

 Dim nRow

 ' Location
 and name of main Data file.

 DatFldr = "C:\temp\vba\Savla\"

 DatFile = "DataFile.xlsb"

 On Error Resume Next

 Workbooks(DatFile).Activate

 If (Err.Number <> 0) Then

 Workbooks.Open DatFldr & DatFile, UpdateLinks:=False

 Err.Clear

 End If

 nRow = 1

 ThisWorkbook.Sheets(1).Range("
 A2:Z65000").ClearContents

 Dim Sht As Worksheet, Rng As Range

 For Each Sht In ActiveWorkbook.Sheets

 For Each Rng In Sht.UsedRange

 If (Rng.Value & "X" <> Rng.Formula &
 "X") Then

 nRow = nRow + 1

 ThisWorkbook.Sheets(1).Cells( nRow, "A").Value =
 Sht.Name

 ThisWorkbook.Sheets(1).Cells( nRow, "B").Value =
 Rng.Address

 If (InStr(1, Rng.Formula, "[") > 0) Then

 ThisWorkbook.Sheets(1).Cells( nRow, "C").Value =
 "'" & Rng.Formula

 Else

 ThisWorkbook.Sheets(1).Cells( nRow, "D").Value =
 "'" & Rng.Formula

 End If

 End If

 Next Rng

 Next Sht

 ThisWorkbook.Activate

 MsgBox "Finished"End
 Sub
 Paul-

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Macro is just running and not fetching any outcome.

Regards,
Kaushik

On May 26, 2017 16:11, "'Martin George' via MS EXCEL AND VBA MACROS" <
excel-macros@googlegroups.com> wrote:

>
> 
> On Thu, 5/25/17, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>  Subject: Re: $$Excel-Macros$$ Links
>  To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
>  Date: Thursday, May 25, 2017, 8:17 PM
>
>  The workbook that you add the VBA macro to is
>  identified as "ThisWorkbook".
>  so, in the
>  macro:
>
>  ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
>  clears
>  the contents of the first sheet in
>  "Thisworkbook"then:
>
>  ThisWorkbook.Sheets(1).Cells(nRow, "A").Value =
>  Sht.Name
>
>  ThisWorkbook.Sheets(1).Cells(nRow, "B").Value =
>  Rng.Address
>
>  If (InStr(1, Rng.Formula, "[") > 0) Then
>
>  ThisWorkbook.Sheets(1).Cells(nRow, "C").Value =
>  "'" & Rng.Formula
>
>  Else
>
>  ThisWorkbook.Sheets(1).Cells(nRow, "D").Value =
>  "'" & Rng.Formula
>
>  End Ifputs
>  the listing on the first sheet of the workbook.
>  Paul-
>  “Do
>  all the good you can,
>  By all the means you
>  can,
>  In all the ways you can,
>  In all the places you can,
>  At
>  all the times you can,
>  To all the people you
>  can,
>  As long as ever you can.” - John
>  Wesley
>  -
>
>
>
>On Thursday, May 25, 2017 12:50 PM,
>  KAUSHIK SAVLA <savla.kaus...@gmail.com> wrote:
>
>
>   Hi Paul,
>  Macro got run and gave finished
>  message.
>  Where can I
>  find the desired output?
>  Regards, Kaushik
>  On May 25, 2017 19:06,
>  "Paul Schreiner" <schreiner_p...@att.net>
>  wrote:
>  OK.Spent
>  WAY longer on this than I should have.
>  I
>  should've had you create a "dummy" set of
>  workbooks that looked like yours instead of me spending an
>  hour+ guessing what yours looks like.
>  So,
>  I put together 20 separate workbooks with data.then
>  added 15 sheets to the "data" workbook and
>  created 150 random links to both the 20 external workbooks
>  and  the internal worksheets.
>  It took about 15 minutes
>  to write some code to look at the formulas and determine
>  which are internal/external formulas.
>  It
>  DOESN'T distinguish between calculations and other
>  formulas.
>  Since I don't know what you want to do with
>  it, and how you want it to list the matches, I'll just
>  give you what I have and you can decide what you want to
>  change.
>  If it isn't what you
>  want, then send me a sample to write against.
>  Here it is:
>  Sub
>  Link_Summary()
>
>  Dim DatFldr, DatFile
>
>  Dim aLinks
>
>  Dim nRow
>
>  ' Location
>  and name of main Data file.
>
>  DatFldr = "C:\temp\vba\Savla\"
>
>  DatFile = "DataFile.xlsb"
>
>  On Error Resume Next
>
>  Workbooks(DatFile).Activate
>
>  If (Err.Number <> 0) Then
>
>  Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
>
>  Err.Clear
>
>  End If
>
>  nRow = 1
>
>  ThisWorkbook.Sheets(1).Range("
>  A2:Z65000").ClearContents
>
>  Dim Sht As Worksheet, Rng As Range
>
>  For Each Sht In ActiveWorkbook.Sheets
>
>  For Each Rng In Sht.UsedRange
>
>  If (Rng.Value & "X" <> Rng.Formula &
>  "X") Then
>
>  nRow = nRow + 1
>
>  ThisWorkbook.Sheets(1).Cells( nRow, "A").Value =
>  Sht.Name
>
>  ThisWorkbook.Sheets(1).Cells( nRow, "B").Value =
>  Rng.Address
>
>  If (InStr(1, Rng.Formula, "[") > 0) Then
>
>  ThisWorkbook.Sheets(1).Cells( nRow, "C").Value =
>  "'" & Rng.Formula
>
>  Else
>
>  ThisWorkbook.Sheets(1).Cells( nRow, "D").Value =
>  "'" & Rng.Formula
>
>  End If
>
>  End If
>
>  Next Rng
>
>  Next Sht
>
>  ThisWorkbook.Activate
>
>  MsgBox "Finished"End
>  Sub
>  Paul--
>  ---
>  “Do all the good you can,
>  By all the means you can,
>  In
>  all the ways you can,
>  In all the places you
>  can,
>  At all the times you can,
>  To all the people you can,
>  As
>  long as ever you can.” - John Wesley
>  --
>  ---
>
>
>On Thursday, May 25, 2017 2:51 AM,
>  KAUSHIK SAVLA <savla.kaus.

Re: $$Excel-Macros$$ Links

['Martin George' via MS EXCEL AND VBA MACROS]

On Thu, 5/25/17, Paul Schreiner <schreiner_p...@att.net> wrote:

 Subject: Re: $$Excel-Macros$$ Links
 To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
 Date: Thursday, May 25, 2017, 8:17 PM
 
 The workbook that you add the VBA macro to is
 identified as "ThisWorkbook".
 so, in the
 macro:
    
 ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
 clears
 the contents of the first sheet in
 "Thisworkbook"then:
    
 ThisWorkbook.Sheets(1).Cells(nRow, "A").Value =
 Sht.Name
    
 ThisWorkbook.Sheets(1).Cells(nRow, "B").Value =
 Rng.Address 
    
 If (InStr(1, Rng.Formula, "[") > 0) Then
    
 ThisWorkbook.Sheets(1).Cells(nRow, "C").Value =
 "'" & Rng.Formula
    
 Else
    
 ThisWorkbook.Sheets(1).Cells(nRow, "D").Value =
 "'" & Rng.Formula
    
 End Ifputs
 the listing on the first sheet of the workbook.
 Paul-
 “Do
 all the good you can,
 By all the means you
 can,
 In all the ways you can,
 In all the places you can,
 At
 all the times you can,
 To all the people you
 can,
 As long as ever you can.” - John
 Wesley
 -
 
 
  
   On Thursday, May 25, 2017 12:50 PM,
 KAUSHIK SAVLA <savla.kaus...@gmail.com> wrote:
   
 
  Hi Paul, 
 Macro got run and gave finished
 message. 
 Where can I
 find the desired output? 
 Regards, Kaushik
 On May 25, 2017 19:06,
 "Paul Schreiner" <schreiner_p...@att.net>
 wrote:
 OK.Spent
 WAY longer on this than I should have.
 I
 should've had you create a "dummy" set of
 workbooks that looked like yours instead of me spending an
 hour+ guessing what yours looks like.
 So,
 I put together 20 separate workbooks with data.then
 added 15 sheets to the "data" workbook and
 created 150 random links to both the 20 external workbooks
 and  the internal worksheets.
 It took about 15 minutes
 to write some code to look at the formulas and determine
 which are internal/external formulas.
 It
 DOESN'T distinguish between calculations and other
 formulas.
 Since I don't know what you want to do with
 it, and how you want it to list the matches, I'll just
 give you what I have and you can decide what you want to
 change.
 If it isn't what you
 want, then send me a sample to write against.
 Here it is:
 Sub
 Link_Summary()
    
 Dim DatFldr, DatFile
    
 Dim aLinks
    
 Dim nRow
    
 ' Location
 and name of main Data file.
    
 DatFldr = "C:\temp\vba\Savla\"
    
 DatFile = "DataFile.xlsb"
    
 On Error Resume Next
    
 Workbooks(DatFile).Activate
    
 If (Err.Number <> 0) Then
    
 Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
    
 Err.Clear
    
 End If
    
 nRow = 1
    
 ThisWorkbook.Sheets(1).Range("
 A2:Z65000").ClearContents
    
 Dim Sht As Worksheet, Rng As Range
    
 For Each Sht In ActiveWorkbook.Sheets
    
 For Each Rng In Sht.UsedRange
    
 If (Rng.Value & "X" <> Rng.Formula &
 "X") Then
    
 nRow = nRow + 1
    
 ThisWorkbook.Sheets(1).Cells( nRow, "A").Value =
 Sht.Name
    
 ThisWorkbook.Sheets(1).Cells( nRow, "B").Value =
 Rng.Address
    
 If (InStr(1, Rng.Formula, "[") > 0) Then
    
 ThisWorkbook.Sheets(1).Cells( nRow, "C").Value =
 "'" & Rng.Formula
    
 Else
    
 ThisWorkbook.Sheets(1).Cells( nRow, "D").Value =
 "'" & Rng.Formula
    
 End If
    
 End If
    
 Next Rng
    
 Next Sht
    
 ThisWorkbook.Activate
    
 MsgBox "Finished"End
 Sub
 Paul--
 ---
 “Do all the good you can,
 By all the means you can,
 In
 all the ways you can,
 In all the places you
 can,
 At all the times you can,
 To all the people you can,
 As
 long as ever you can.” - John Wesley
 --
 --- 
 
  
   On Thursday, May 25, 2017 2:51 AM,
 KAUSHIK SAVLA <savla.kaus...@gmail.com>
 wrote:
   
 
  Hi
 Paul
 You are correct,  I
 don't want to break links. Not possible to send workbook
 as it contains sensitive confidential information. 
 Just suppose there is a worksheet
 named A in workbook.  It is linked to external workbook
 named say X,  Y and it is also linked to worksheets in
 workbook say name B,  C,  D,  E in different
 cells. 
 What I want
 is in new workbook it should displayWorksheet A -
  Linked from external workbook X,  Y and internal
 worksheets B, C,  D,  E. 
 This process repeat for all
 worksheets within a workbook. 
 Regards, Kaushik
 Savla8373916768
 On
 May 25, 2017 

Re: $$Excel-Macros$$ Links

['Storey Thomas' via MS EXCEL AND VBA MACROS]

On Thu, 5/25/17, KAUSHIK SAVLA <savla.kaus...@gmail.com> wrote:

 Subject: Re: $$Excel-Macros$$ Links
 To: "MS EXCEL AND VBA MACROS" <excel-macros@googlegroups.com>
 Cc: schreiner_p...@att.net
 Date: Thursday, May 25, 2017, 9:26 PM
 
 Could you
 please share your dummy workbook which you created?
 Regards,Kaushik Savla
 
 On Thursday, May 25, 2017 at
 11:47:39 PM UTC+5:30, Paul Schreiner wrote:The workbook that you add the VBA 
macro to is
 identified as "ThisWorkbook".
 so, in the
 macro:
    
 ThisWorkbook.Sheets(1).Range("
 A2:Z65000").ClearContents
 clears the contents of the
 first sheet in "Thisworkbook"then:
    
 ThisWorkbook.Sheets(1).Cells( nRow, "A").Value =
 Sht.Name
    
 ThisWorkbook.Sheets(1).Cells( nRow, "B").Value =
 Rng.Address 
    
 If (InStr(1, Rng.Formula, "[") > 0) Then
    
 ThisWorkbook.Sheets(1).Cells( nRow, "C").Value =
 "'" & Rng.Formula
     Else
    
 ThisWorkbook.Sheets(1).Cells( nRow, "D").Value =
 "'" & Rng.Formula
     End Ifputs the listing on the first sheet of the
 workbook.
 Paul--
 ---
 “Do all the good you can,
 By all the means you can,
 In
 all the ways you can,
 In all the places you
 can,
 At all the times you can,
 To all the people you can,
 As
 long as ever you can.” - John Wesley
 --
 --- 
 
 On Thursday, May 25,
 2017 12:50 PM, KAUSHIK SAVLA <savla@gmail.com> wrote:
   
 
  Hi Paul, 
 Macro got run and gave finished
 message. 
 Where can I
 find the desired output? 
 Regards, Kaushik
 On May 25, 2017 19:06, "Paul
 Schreiner" <schrein...@att.net> wrote:
 OK.Spent
 WAY longer on this than I should have.
 I should've had you create a
 "dummy" set of workbooks that looked like yours
 instead of me spending an hour+ guessing what yours looks
 like.
 So, I put
 together 20 separate workbooks with data.then added 15 sheets to the "data"
 workbook and created 150 random links to both the 20
 external workbooks and  the internal worksheets.
 It took
 about 15 minutes to write some code to look at the formulas
 and determine which are internal/external
 formulas.
 It DOESN'T distinguish between calculations
 and other formulas.
 Since I don't know
 what you want to do with it, and how you want it to list the
 matches, I'll just give you what I have and you can
 decide what you want to change.
 If it isn't what you
 want, then send me a sample to write against.
 Here it
 is:
 Sub Link_Summary()
     Dim
 DatFldr, DatFile
     Dim aLinks
     Dim nRow
    
 ' Location and name of main Data
 file.
     DatFldr =
 "C:\temp\vba\Savla\"
     DatFile =
 "DataFile.xlsb"
     On Error Resume Next
     Workbooks(DatFile).Activate
     If (Err.Number <> 0) Then
     Workbooks.Open DatFldr &
 DatFile, UpdateLinks:=False
    
 Err.Clear
     End If
     nRow = 1
    
 ThisWorkbook.Sheets(1).Range("
 A2:Z65000").ClearContents
     Dim
 Sht As Worksheet, Rng As Range
     For
 Each Sht In ActiveWorkbook.Sheets
     For Each Rng In Sht.UsedRange
     If (Rng.Value &
 "X" <> Rng.Formula & "X")
 Then
     nRow =
 nRow + 1
    
 ThisWorkbook.Sheets(1).Cells( nRow, "A").Value =
 Sht.Name
    
 ThisWorkbook.Sheets(1).Cells( nRow, "B").Value =
 Rng.Address
    
 If (InStr(1, Rng.Formula, "[") > 0) Then
    
 ThisWorkbook.Sheets(1).Cells( nRow, "C").Value =
 "'" & Rng.Formula
     Else
    
 ThisWorkbook.Sheets(1).Cells( nRow, "D").Value =
 "'" & Rng.Formula
     End If
     End If
     Next Rng
    
 Next Sht
     ThisWorkbook.Activate
     MsgBox "Finished"End Sub
 Paul--
 ---
 “Do all the good you can,
 By all the means you can,
 In
 all the ways you can,
 In all the places you
 can,
 At all the times you can,
 To all the people you can,
 As
 long as ever you can.” - John Wesley
 --
 --- 
 
 On Thursday, May 25,
 2017 2:51 AM, KAUSHIK SAVLA <savla@gmail.com> wrote:
   
 
  Hi Paul
 You are correct,  I don't want
 to break links. Not possible to send workbook as it contains
 sensitive confidential information. 
 Just suppose there is a worksheet
 named A in workbook.  It is linked to external workbook
 named say X,  Y and it is also linked to worksheets in
 workbook say name B,  C,  D,  E in different
 cells. 
 What I want
 is in new workbook it should displayWorksheet A -
  Linked from external workbook X,  Y and int

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Could you please share your dummy workbook which you created?

Regards,
Kaushik Savla

On Thursday, May 25, 2017 at 11:47:39 PM UTC+5:30, Paul Schreiner wrote:
>
> The workbook that you add the VBA macro to is identified as "ThisWorkbook".
>
> so, in the macro:
>
> ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
>
> clears the contents of the first sheet in "Thisworkbook"
> then:
> ThisWorkbook.Sheets(1).Cells(nRow, "A").Value = Sht.Name
> ThisWorkbook.Sheets(1).Cells(nRow, "B").Value = 
> Rng.Address 
> If (InStr(1, Rng.Formula, "[") > 0) Then
> ThisWorkbook.Sheets(1).Cells(nRow, "C").Value = "'" & 
> Rng.Formula
> Else
> ThisWorkbook.Sheets(1).Cells(nRow, "D").Value = "'" & 
> Rng.Formula
> End If
> puts the listing on the first sheet of the workbook.
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you 
> can,In all the places you can,At all the times you can,To all the people 
> you can,As long as ever you can.” - John Wesley*
> -
>
>
> On Thursday, May 25, 2017 12:50 PM, KAUSHIK SAVLA  > wrote:
>
>
> Hi Paul, 
>
> Macro got run and gave finished message. 
>
> Where can I find the desired output? 
>
> Regards, 
> Kaushik
>
> On May 25, 2017 19:06, "Paul Schreiner"  
> wrote:
>
> OK.
> Spent WAY longer on this than I should have.
>
> I should've had you create a "dummy" set of workbooks that looked like 
> yours instead of me spending an hour+ guessing what yours looks like.
>
> So, I put together 20 separate workbooks with data.
> then added 15 sheets to the "data" workbook and created 150 random 
> links to both the 20 external workbooks and  the internal worksheets.
>
> It took about 15 minutes to write some code to look at the formulas and 
> determine which are internal/external formulas.
>
> It DOESN'T distinguish between calculations and other formulas.
>
> Since I don't know what you want to do with it, and how you want it to 
> list the matches, I'll just give you what I have and you can decide what 
> you want to change.
>
> If it isn't what you want, then send me a sample to write against.
>
> Here it is:
>
> Sub Link_Summary()
> Dim DatFldr, DatFile
> Dim aLinks
> Dim nRow
> 
> ' Location and name of main Data file.
> DatFldr = "C:\temp\vba\Savla\"
> DatFile = "DataFile.xlsb"
>
> On Error Resume Next
> Workbooks(DatFile).Activate
> If (Err.Number <> 0) Then
> Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
> Err.Clear
> End If
> nRow = 1
> ThisWorkbook.Sheets(1).Range(" A2:Z65000").ClearContents
> Dim Sht As Worksheet, Rng As Range
> For Each Sht In ActiveWorkbook.Sheets
> For Each Rng In Sht.UsedRange
> If (Rng.Value & "X" <> Rng.Formula & "X") Then
> nRow = nRow + 1
> ThisWorkbook.Sheets(1).Cells( nRow, "A").Value = Sht.Name
> ThisWorkbook.Sheets(1).Cells( nRow, "B").Value = 
> Rng.Address
> If (InStr(1, Rng.Formula, "[") > 0) Then
> ThisWorkbook.Sheets(1).Cells( nRow, "C").Value = "'" & 
> Rng.Formula
> Else
> ThisWorkbook.Sheets(1).Cells( nRow, "D").Value = "'" & 
> Rng.Formula
> End If
> End If
> Next Rng
> Next Sht
> ThisWorkbook.Activate
> MsgBox "Finished"
> End Sub
> *Paul*
> -- ---
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you 
> can,In all the places you can,At all the times you can,To all the people 
> you can,As long as ever you can.” - John 
> Wesley*-- 
> ---
>
>
> On Thursday, May 25, 2017 2:51 AM, KAUSHIK SAVLA  > wrote:
>
>
> Hi Paul
>
> You are correct,  I don't want to break links. Not possible to send 
> workbook as it contains sensitive confidential information. 
>
> Just suppose there is a worksheet named A in workbook.  It is linked to 
> external workbook named say X,  Y and it is also linked to worksheets in 
> workbook say name B,  C,  D,  E in different cells. 
>
> What I want is in new workbook it should display
> Worksheet A -  Linked from external workbook X,  Y and internal worksheets 
> B, C,  D,  E. 
>
> This process repeat for all worksheets within a workbook. 
>
> Regards, 
> Kaushik Savla
> 8373916768
>
> On May 25, 2017 00:53, "Paul Schreiner"  
> wrote:
>
> It sounds like you're not wanting to REMOVE the links,
> but basically generate a list of worksheets and the name of the external 
> workbooks linked to those sheets.
>
> That doesn't sound difficult.
>
> But the error you're suggesting doesn't make sense because you're not 
> "pasting" 

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

ok...Thanks

On Thursday, May 25, 2017 at 11:47:39 PM UTC+5:30, Paul Schreiner wrote:
>
> The workbook that you add the VBA macro to is identified as "ThisWorkbook".
>
> so, in the macro:
>
> ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
>
> clears the contents of the first sheet in "Thisworkbook"
> then:
> ThisWorkbook.Sheets(1).Cells(nRow, "A").Value = Sht.Name
> ThisWorkbook.Sheets(1).Cells(nRow, "B").Value = 
> Rng.Address 
> If (InStr(1, Rng.Formula, "[") > 0) Then
> ThisWorkbook.Sheets(1).Cells(nRow, "C").Value = "'" & 
> Rng.Formula
> Else
> ThisWorkbook.Sheets(1).Cells(nRow, "D").Value = "'" & 
> Rng.Formula
> End If
> puts the listing on the first sheet of the workbook.
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you 
> can,In all the places you can,At all the times you can,To all the people 
> you can,As long as ever you can.” - John Wesley*
> -
>
>
> On Thursday, May 25, 2017 12:50 PM, KAUSHIK SAVLA  > wrote:
>
>
> Hi Paul, 
>
> Macro got run and gave finished message. 
>
> Where can I find the desired output? 
>
> Regards, 
> Kaushik
>
> On May 25, 2017 19:06, "Paul Schreiner"  
> wrote:
>
> OK.
> Spent WAY longer on this than I should have.
>
> I should've had you create a "dummy" set of workbooks that looked like 
> yours instead of me spending an hour+ guessing what yours looks like.
>
> So, I put together 20 separate workbooks with data.
> then added 15 sheets to the "data" workbook and created 150 random 
> links to both the 20 external workbooks and  the internal worksheets.
>
> It took about 15 minutes to write some code to look at the formulas and 
> determine which are internal/external formulas.
>
> It DOESN'T distinguish between calculations and other formulas.
>
> Since I don't know what you want to do with it, and how you want it to 
> list the matches, I'll just give you what I have and you can decide what 
> you want to change.
>
> If it isn't what you want, then send me a sample to write against.
>
> Here it is:
>
> Sub Link_Summary()
> Dim DatFldr, DatFile
> Dim aLinks
> Dim nRow
> 
> ' Location and name of main Data file.
> DatFldr = "C:\temp\vba\Savla\"
> DatFile = "DataFile.xlsb"
>
> On Error Resume Next
> Workbooks(DatFile).Activate
> If (Err.Number <> 0) Then
> Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
> Err.Clear
> End If
> nRow = 1
> ThisWorkbook.Sheets(1).Range(" A2:Z65000").ClearContents
> Dim Sht As Worksheet, Rng As Range
> For Each Sht In ActiveWorkbook.Sheets
> For Each Rng In Sht.UsedRange
> If (Rng.Value & "X" <> Rng.Formula & "X") Then
> nRow = nRow + 1
> ThisWorkbook.Sheets(1).Cells( nRow, "A").Value = Sht.Name
> ThisWorkbook.Sheets(1).Cells( nRow, "B").Value = 
> Rng.Address
> If (InStr(1, Rng.Formula, "[") > 0) Then
> ThisWorkbook.Sheets(1).Cells( nRow, "C").Value = "'" & 
> Rng.Formula
> Else
> ThisWorkbook.Sheets(1).Cells( nRow, "D").Value = "'" & 
> Rng.Formula
> End If
> End If
> Next Rng
> Next Sht
> ThisWorkbook.Activate
> MsgBox "Finished"
> End Sub
> *Paul*
> -- ---
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you 
> can,In all the places you can,At all the times you can,To all the people 
> you can,As long as ever you can.” - John 
> Wesley*-- 
> ---
>
>
> On Thursday, May 25, 2017 2:51 AM, KAUSHIK SAVLA  > wrote:
>
>
> Hi Paul
>
> You are correct,  I don't want to break links. Not possible to send 
> workbook as it contains sensitive confidential information. 
>
> Just suppose there is a worksheet named A in workbook.  It is linked to 
> external workbook named say X,  Y and it is also linked to worksheets in 
> workbook say name B,  C,  D,  E in different cells. 
>
> What I want is in new workbook it should display
> Worksheet A -  Linked from external workbook X,  Y and internal worksheets 
> B, C,  D,  E. 
>
> This process repeat for all worksheets within a workbook. 
>
> Regards, 
> Kaushik Savla
> 8373916768
>
> On May 25, 2017 00:53, "Paul Schreiner"  
> wrote:
>
> It sounds like you're not wanting to REMOVE the links,
> but basically generate a list of worksheets and the name of the external 
> workbooks linked to those sheets.
>
> That doesn't sound difficult.
>
> But the error you're suggesting doesn't make sense because you're not 
> "pasting" anything (with the macro I suggested).
>
> It would take me at least an 

Re: $$Excel-Macros$$ Links

[Paul Schreiner]

The workbook that you add the VBA macro to is identified as "ThisWorkbook".
so, in the macro:
    ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
clears the contents of the first sheet in "Thisworkbook"then:
    ThisWorkbook.Sheets(1).Cells(nRow, "A").Value = Sht.Name
    ThisWorkbook.Sheets(1).Cells(nRow, "B").Value = Rng.Address 
    If (InStr(1, Rng.Formula, "[") > 0) Then
    ThisWorkbook.Sheets(1).Cells(nRow, "C").Value = "'" & 
Rng.Formula
    Else
    ThisWorkbook.Sheets(1).Cells(nRow, "D").Value = "'" & 
Rng.Formula
    End Ifputs the listing on the first sheet of the workbook.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Thursday, May 25, 2017 12:50 PM, KAUSHIK SAVLA  
wrote:
 

 Hi Paul, 
Macro got run and gave finished message. 
Where can I find the desired output? 
Regards, Kaushik
On May 25, 2017 19:06, "Paul Schreiner"  wrote:

OK.Spent WAY longer on this than I should have.
I should've had you create a "dummy" set of workbooks that looked like yours 
instead of me spending an hour+ guessing what yours looks like.
So, I put together 20 separate workbooks with data.then added 15 sheets to the 
"data" workbook and created 150 random links to both the 20 external workbooks 
and  the internal worksheets.
It took about 15 minutes to write some code to look at the formulas and 
determine which are internal/external formulas.
It DOESN'T distinguish between calculations and other formulas.
Since I don't know what you want to do with it, and how you want it to list the 
matches, I'll just give you what I have and you can decide what you want to 
change.
If it isn't what you want, then send me a sample to write against.
Here it is:
Sub Link_Summary()
    Dim DatFldr, DatFile
    Dim aLinks
    Dim nRow
    ' Location and name of main Data file.
    DatFldr = "C:\temp\vba\Savla\"
    DatFile = "DataFile.xlsb"
    On Error Resume Next
    Workbooks(DatFile).Activate
    If (Err.Number <> 0) Then
    Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
    Err.Clear
    End If
    nRow = 1
    ThisWorkbook.Sheets(1).Range(" A2:Z65000").ClearContents
    Dim Sht As Worksheet, Rng As Range
    For Each Sht In ActiveWorkbook.Sheets
    For Each Rng In Sht.UsedRange
    If (Rng.Value & "X" <> Rng.Formula & "X") Then
    nRow = nRow + 1
    ThisWorkbook.Sheets(1).Cells( nRow, "A").Value = Sht.Name
    ThisWorkbook.Sheets(1).Cells( nRow, "B").Value = Rng.Address
    If (InStr(1, Rng.Formula, "[") > 0) Then
    ThisWorkbook.Sheets(1).Cells( nRow, "C").Value = "'" & 
Rng.Formula
    Else
    ThisWorkbook.Sheets(1).Cells( nRow, "D").Value = "'" & 
Rng.Formula
    End If
    End If
    Next Rng
    Next Sht
    ThisWorkbook.Activate
    MsgBox "Finished"End Sub
Paul-- ---
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-- --- 

On Thursday, May 25, 2017 2:51 AM, KAUSHIK SAVLA  
wrote:
 

 Hi Paul
You are correct,  I don't want to break links. Not possible to send workbook as 
it contains sensitive confidential information. 
Just suppose there is a worksheet named A in workbook.  It is linked to 
external workbook named say X,  Y and it is also linked to worksheets in 
workbook say name B,  C,  D,  E in different cells. 
What I want is in new workbook it should displayWorksheet A -  Linked from 
external workbook X,  Y and internal worksheets B, C,  D,  E. 
This process repeat for all worksheets within a workbook. 
Regards, Kaushik Savla8373916768
On May 25, 2017 00:53, "Paul Schreiner"  wrote:

It sounds like you're not wanting to REMOVE the links,but basically generate a 
list of worksheets and the name of the external workbooks linked to those 
sheets.
That doesn't sound difficult.
But the error you're suggesting doesn't make sense because you're not "pasting" 
anything (with the macro I suggested).
It would take me at least an hour to TRY to create a file set that MIGHT 
duplicate your structure. Is it at all possible for you to send me the workbook 
directly (schreiner_p...@att.net)?
If not, can you send me the macro you're using that is causing the 
problems?Paul-- ---
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the 

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Hi Paul,

Macro got run and gave finished message.

Where can I find the desired output?

Regards,
Kaushik

On May 25, 2017 19:06, "Paul Schreiner"  wrote:

> OK.
> Spent WAY longer on this than I should have.
>
> I should've had you create a "dummy" set of workbooks that looked like
> yours instead of me spending an hour+ guessing what yours looks like.
>
> So, I put together 20 separate workbooks with data.
> then added 15 sheets to the "data" workbook and created 150 random
> links to both the 20 external workbooks and  the internal worksheets.
>
> It took about 15 minutes to write some code to look at the formulas and
> determine which are internal/external formulas.
>
> It DOESN'T distinguish between calculations and other formulas.
>
> Since I don't know what you want to do with it, and how you want it to
> list the matches, I'll just give you what I have and you can decide what
> you want to change.
>
> If it isn't what you want, then send me a sample to write against.
>
> Here it is:
>
> Sub Link_Summary()
> Dim DatFldr, DatFile
> Dim aLinks
> Dim nRow
>
> ' Location and name of main Data file.
> DatFldr = "C:\temp\vba\Savla\"
> DatFile = "DataFile.xlsb"
>
> On Error Resume Next
> Workbooks(DatFile).Activate
> If (Err.Number <> 0) Then
> Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
> Err.Clear
> End If
> nRow = 1
> ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
> Dim Sht As Worksheet, Rng As Range
> For Each Sht In ActiveWorkbook.Sheets
> For Each Rng In Sht.UsedRange
> If (Rng.Value & "X" <> Rng.Formula & "X") Then
> nRow = nRow + 1
> ThisWorkbook.Sheets(1).Cells(nRow, "A").Value = Sht.Name
> ThisWorkbook.Sheets(1).Cells(nRow, "B").Value =
> Rng.Address
> If (InStr(1, Rng.Formula, "[") > 0) Then
> ThisWorkbook.Sheets(1).Cells(nRow, "C").Value = "'" &
> Rng.Formula
> Else
> ThisWorkbook.Sheets(1).Cells(nRow, "D").Value = "'" &
> Rng.Formula
> End If
> End If
> Next Rng
> Next Sht
> ThisWorkbook.Activate
> MsgBox "Finished"
> End Sub
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>
> On Thursday, May 25, 2017 2:51 AM, KAUSHIK SAVLA 
> wrote:
>
>
> Hi Paul
>
> You are correct,  I don't want to break links. Not possible to send
> workbook as it contains sensitive confidential information.
>
> Just suppose there is a worksheet named A in workbook.  It is linked to
> external workbook named say X,  Y and it is also linked to worksheets in
> workbook say name B,  C,  D,  E in different cells.
>
> What I want is in new workbook it should display
> Worksheet A -  Linked from external workbook X,  Y and internal worksheets
> B, C,  D,  E.
>
> This process repeat for all worksheets within a workbook.
>
> Regards,
> Kaushik Savla
> 8373916768
>
> On May 25, 2017 00:53, "Paul Schreiner"  wrote:
>
> It sounds like you're not wanting to REMOVE the links,
> but basically generate a list of worksheets and the name of the external
> workbooks linked to those sheets.
>
> That doesn't sound difficult.
>
> But the error you're suggesting doesn't make sense because you're not
> "pasting" anything (with the macro I suggested).
>
> It would take me at least an hour to TRY to create a file set that MIGHT
> duplicate your structure.
> Is it at all possible for you to send me the workbook directly (
> schreiner_p...@att.net)?
>
> If not, can you send me the macro you're using that is causing the
> problems?
> *Paul*
> -- ---
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*--
> ---
>
>
> On Wednesday, May 24, 2017 3:07 PM, KAUSHIK SAVLA 
> wrote:
>
>
> Hi Paul,
>
> What I am looking for is below.
>
> 1. I have a workbook with 100 worksheets
> 2. Each worksheet is linked to several external Workbooks and several
> tabs/worksheets within workbook.
> 3. What I want to do is first list all name of tabs sequentially in a new
> workbook and against each worksheet name mention the unique source with
> name say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e,
> f Worksheets.
> This I want to do for all 100 worksheets.
>
> Hope this helps.
>
> Regards,
> Kaushik
>
> On May 25, 2017 00:23, "KAUSHIK SAVLA"  wrote:
>
> Hi Paul,
>
> 

Re: $$Excel-Macros$$ Links

[Paul Schreiner]

OK.Spent WAY longer on this than I should have.
I should've had you create a "dummy" set of workbooks that looked like yours 
instead of me spending an hour+ guessing what yours looks like.
So, I put together 20 separate workbooks with data.then added 15 sheets to the 
"data" workbook and created 150 random links to both the 20 external workbooks 
and  the internal worksheets.
It took about 15 minutes to write some code to look at the formulas and 
determine which are internal/external formulas.
It DOESN'T distinguish between calculations and other formulas.
Since I don't know what you want to do with it, and how you want it to list the 
matches, I'll just give you what I have and you can decide what you want to 
change.
If it isn't what you want, then send me a sample to write against.
Here it is:
Sub Link_Summary()
    Dim DatFldr, DatFile
    Dim aLinks
    Dim nRow
    ' Location and name of main Data file.
    DatFldr = "C:\temp\vba\Savla\"
    DatFile = "DataFile.xlsb"
    On Error Resume Next
    Workbooks(DatFile).Activate
    If (Err.Number <> 0) Then
    Workbooks.Open DatFldr & DatFile, UpdateLinks:=False
    Err.Clear
    End If
    nRow = 1
    ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
    Dim Sht As Worksheet, Rng As Range
    For Each Sht In ActiveWorkbook.Sheets
    For Each Rng In Sht.UsedRange
    If (Rng.Value & "X" <> Rng.Formula & "X") Then
    nRow = nRow + 1
    ThisWorkbook.Sheets(1).Cells(nRow, "A").Value = Sht.Name
    ThisWorkbook.Sheets(1).Cells(nRow, "B").Value = Rng.Address
    If (InStr(1, Rng.Formula, "[") > 0) Then
    ThisWorkbook.Sheets(1).Cells(nRow, "C").Value = "'" & 
Rng.Formula
    Else
    ThisWorkbook.Sheets(1).Cells(nRow, "D").Value = "'" & 
Rng.Formula
    End If
    End If
    Next Rng
    Next Sht
    ThisWorkbook.Activate
    MsgBox "Finished"End Sub
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Thursday, May 25, 2017 2:51 AM, KAUSHIK SAVLA  
wrote:
 

 Hi Paul
You are correct,  I don't want to break links. Not possible to send workbook as 
it contains sensitive confidential information. 
Just suppose there is a worksheet named A in workbook.  It is linked to 
external workbook named say X,  Y and it is also linked to worksheets in 
workbook say name B,  C,  D,  E in different cells. 
What I want is in new workbook it should displayWorksheet A -  Linked from 
external workbook X,  Y and internal worksheets B, C,  D,  E. 
This process repeat for all worksheets within a workbook. 
Regards, Kaushik Savla8373916768
On May 25, 2017 00:53, "Paul Schreiner"  wrote:

It sounds like you're not wanting to REMOVE the links,but basically generate a 
list of worksheets and the name of the external workbooks linked to those 
sheets.
That doesn't sound difficult.
But the error you're suggesting doesn't make sense because you're not "pasting" 
anything (with the macro I suggested).
It would take me at least an hour to TRY to create a file set that MIGHT 
duplicate your structure. Is it at all possible for you to send me the workbook 
directly (schreiner_p...@att.net)?
If not, can you send me the macro you're using that is causing the 
problems?Paul-- ---
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-- --- 

On Wednesday, May 24, 2017 3:07 PM, KAUSHIK SAVLA  
wrote:
 

 Hi Paul,

What I am looking for is below.

1. I have a workbook with 100 worksheets
2. Each worksheet is linked to several external Workbooks and several 
tabs/worksheets within workbook.
3. What I want to do is first list all name of tabs sequentially in a new 
workbook and against each worksheet name mention the unique source with name 
say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e, f 
Worksheets.
This I want to do for all 100 worksheets.

Hope this helps.

Regards,
Kaushik
On May 25, 2017 00:23, "KAUSHIK SAVLA"  wrote:

Hi Paul,

What I am looking for is below.

1. I have a workbook with 100 worksheets
2. Each worksheet is linked to several external Workbooks and several table 
within workbook.
3. What I want to do is first list all name of tabs sequentially in a new 
workbook and against each worksheet name mention the unique source with name 
say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e, f 
Worksheets.
This I want to do for all 100 

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Hi Paul

You are correct,  I don't want to break links. Not possible to send
workbook as it contains sensitive confidential information.

Just suppose there is a worksheet named A in workbook.  It is linked to
external workbook named say X,  Y and it is also linked to worksheets in
workbook say name B,  C,  D,  E in different cells.

What I want is in new workbook it should display
Worksheet A -  Linked from external workbook X,  Y and internal worksheets
B, C,  D,  E.

This process repeat for all worksheets within a workbook.

Regards,
Kaushik Savla
8373916768

On May 25, 2017 00:53, "Paul Schreiner"  wrote:

> It sounds like you're not wanting to REMOVE the links,
> but basically generate a list of worksheets and the name of the external
> workbooks linked to those sheets.
>
> That doesn't sound difficult.
>
> But the error you're suggesting doesn't make sense because you're not
> "pasting" anything (with the macro I suggested).
>
> It would take me at least an hour to TRY to create a file set that MIGHT
> duplicate your structure.
> Is it at all possible for you to send me the workbook directly (
> schreiner_p...@att.net)?
>
> If not, can you send me the macro you're using that is causing the
> problems?
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>
> On Wednesday, May 24, 2017 3:07 PM, KAUSHIK SAVLA 
> wrote:
>
>
> Hi Paul,
>
> What I am looking for is below.
>
> 1. I have a workbook with 100 worksheets
> 2. Each worksheet is linked to several external Workbooks and several
> tabs/worksheets within workbook.
> 3. What I want to do is first list all name of tabs sequentially in a new
> workbook and against each worksheet name mention the unique source with
> name say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e,
> f Worksheets.
> This I want to do for all 100 worksheets.
>
> Hope this helps.
>
> Regards,
> Kaushik
>
> On May 25, 2017 00:23, "KAUSHIK SAVLA"  wrote:
>
> Hi Paul,
>
> What I am looking for is below.
>
> 1. I have a workbook with 100 worksheets
> 2. Each worksheet is linked to several external Workbooks and several
> table within workbook.
> 3. What I want to do is first list all name of tabs sequentially in a new
> workbook and against each worksheet name mention the unique source with
> name say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e,
> f Worksheets.
> This I want to do for all 100 worksheets.
>
> Hope this helps.
>
> Regards,
> Kaushik
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
> https://www.facebook.com/
> discussexcel 
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE  : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/excel-macros/ lYmOGDBlWFU/unsubscribe
> .
> To unsubscribe from this group and all its topics, send an email to 
> excel-macros+unsubscribe@
> googlegroups.com .
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/ group/excel-macros
> .
> For more options, visit https://groups.google.com/d/ optout
> .
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 

Re: $$Excel-Macros$$ Links

[Paul Schreiner]

It sounds like you're not wanting to REMOVE the links,but basically generate a 
list of worksheets and the name of the external workbooks linked to those 
sheets.
That doesn't sound difficult.
But the error you're suggesting doesn't make sense because you're not "pasting" 
anything (with the macro I suggested).
It would take me at least an hour to TRY to create a file set that MIGHT 
duplicate your structure. Is it at all possible for you to send me the workbook 
directly (schreiner_p...@att.net)?
If not, can you send me the macro you're using that is causing the 
problems?Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Wednesday, May 24, 2017 3:07 PM, KAUSHIK SAVLA  
wrote:
 

 Hi Paul,

What I am looking for is below.

1. I have a workbook with 100 worksheets
2. Each worksheet is linked to several external Workbooks and several 
tabs/worksheets within workbook.
3. What I want to do is first list all name of tabs sequentially in a new 
workbook and against each worksheet name mention the unique source with name 
say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e, f 
Worksheets.
This I want to do for all 100 worksheets.

Hope this helps.

Regards,
Kaushik
On May 25, 2017 00:23, "KAUSHIK SAVLA"  wrote:

Hi Paul,

What I am looking for is below.

1. I have a workbook with 100 worksheets
2. Each worksheet is linked to several external Workbooks and several table 
within workbook.
3. What I want to do is first list all name of tabs sequentially in a new 
workbook and against each worksheet name mention the unique source with name 
say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e, f 
Worksheets.
This I want to do for all 100 worksheets.

Hope this helps.

Regards,
Kaushik

--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/ discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
---
You received this message because you are subscribed to a topic in the Google 
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/ 
topic/excel-macros/ lYmOGDBlWFU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


   

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick 

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Hi Paul,

What I am looking for is below.

1. I have a workbook with 100 worksheets
2. Each worksheet is linked to several external Workbooks and several
tabs/worksheets within workbook.
3. What I want to do is first list all name of tabs sequentially in a new
workbook and against each worksheet name mention the unique source with
name say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e,
f Worksheets.
This I want to do for all 100 worksheets.

Hope this helps.

Regards,
Kaushik

On May 25, 2017 00:23, "KAUSHIK SAVLA"  wrote:

> Hi Paul,
>
> What I am looking for is below.
>
> 1. I have a workbook with 100 worksheets
> 2. Each worksheet is linked to several external Workbooks and several
> table within workbook.
> 3. What I want to do is first list all name of tabs sequentially in a new
> workbook and against each worksheet name mention the unique source with
> name say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e,
> f Worksheets.
> This I want to do for all 100 worksheets.
>
> Hope this helps.
>
> Regards,
> Kaushik
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE  : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/excel-macros/lYmOGDBlWFU/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Hi Paul, 

What I am looking for is below. 

1. I have a workbook with 100 worksheets
2. Each worksheet is linked to several external Workbooks and several table 
within workbook. 
3. What I want to do is first list all name of tabs sequentially in a new 
workbook and against each worksheet name mention the unique source with name 
say linked to external ABC,  XYZ,  files and linked to a, b, c, d, e, f 
Worksheets. 
This I want to do for all 100 worksheets. 

Hope this helps. 

Regards, 
Kaushik

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Links

[Paul Schreiner]

So, you're wanting to find out what other documents are being linked?
something like this will count the number of referenced links:
    cnt = 0
    alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
'    MsgBox alinks.Count
    If Not IsEmpty(alinks) Thenmsgbox "Links: " & UBound(alinks)
    End If
You can loop through the array to list unique values.
 Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Tuesday, May 23, 2017 2:07 PM, KAUSHIK SAVLA  
wrote:
 

 Hi Team,
I have several huge data files.
Each file has links to several worksheets and several workbooks.
Can anyone help me with a vba code which will go in each worksheet of workbook 
and will fetch me result mentioning each worksheet has source from which 
worksheets/workbooks.
Doing it manually is impossible.
Regards,Kaushik Savla-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


   

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

$$Excel-Macros$$ Links

[KAUSHIK SAVLA]

Hi Team,

I have several huge data files.

Each file has links to several worksheets and several workbooks.

Can anyone help me with a vba code which will go in each worksheet of 
workbook and will fetch me result mentioning each worksheet has source from 
which worksheets/workbooks.

Doing it manually is impossible.

Regards,
Kaushik Savla

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.