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  
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" 
 wrote:


-- --
On Thu, 5/25/17, Paul Schreiner  wrote:

 Subject: Re: $$Excel-Macros$$ Links
 To: "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  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 

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  wrote:
>
>  Subject: Re: $$Excel-Macros$$ Links
>  To: "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  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 

$$Excel-Macros$$ Populating right table from left table

[Izhar]

see the attachment, in the example the name NADEEM  is not showing thru the
formula, please do the needful

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


Populating right table from left table.xlsx
Description: MS-Excel 2007 spreadsheet

Re: $$Excel-Macros$$ Links

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

On Thu, 5/25/17, Paul Schreiner  wrote:

 Subject: Re: $$Excel-Macros$$ Links
 To: "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  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