--------------------------------------------
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 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" <schrein...@att.net> 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 (schrein...@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 <savla....@gmail.com> 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" <savla....@gmail.com> 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...@
googlegroups.com.
To post to this group, send email to excel-...@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...@ googlegroups.com.
To post to this group, send email to excel-...@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 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...@
googlegroups.com.
To post to this group, send email to excel-...@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...@ googlegroups.com.
To post to this group, send email to excel-...@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 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...@
googlegroups.com.
To post to this group, send email to excel-...@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...@ googlegroups.com.
To post to this group, send email to excel-...@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.
un. - Formarea unui guvern iu la Bucuresti.
--
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.
