Hey Paul,
first of all, thank you for getting back to me and sorry that it took me so
long to respond. Since I couldn't access this group from work, I tried
re-creating the same scenario at home - with success, except for the one
issue that things were fine when in debug mode. I figured out what the
issues was and hence, want to at least post that here:
When the code in my master workbook that contains INDIRECTs to other
workbooks opens another workbook, say alpha, the INDIRECTs pointing to
alpha then show values - just what we would expect. Whe the code closes
alpha and opens another files, say beta, the INDIRECTs pointing to beta
then show values but the ones to alpha no longer show values but they show
#REF again. It seems that there's an automatic RECALCULATE ALL SHEETS done
every time a workbook is opened (or closed). The work around for this is to
turn auto calc off and after alpha opens, only recalc the sheet that
had INDIRECTs that point to alpha, etc. Problem is that at the end you have
to stay in manaual calc mode. Switching back to autocalc at the end with
alpha, beta, ..., no longer open results in #REFs in all INDIRECTs to these
files.
Guess it's a negative of using INDIRECTs and not having all files open that
I link my INDIRECTs to. The whole mission was to find code that would open
and close files that I have INDIRECTs to in a master workbook so that the
#REFs are replaced with values.
Christoph
On Wednesday, August 13, 2014 8:13:59 AM UTC-4, Paul Schreiner wrote:
> It would take me an hour or more to put together an example that I HOPE
> would look like yours before I could even begin to test it.
> Then I'd have to hope it matches your conditions.
> If it DIDN'T respond the same way, I wouldn't know if it was because it
> was MY samples, or a problem with yours.
>
> So,
> Could you send me a sample set of files?
> you don't have to send all 100.
> alpha, beta and gamma (or George, Paul and Ringo) would suffice.
>
> I realize the data may be sensitive, so you may have to "change the names
> to protect the innocent".
>
> But unless I can see what YOUR situation is, I'd only be testing my
> INTERPRETATION of your situation...
>
> know what I mean?
>
> I'd be glad to take a look.
>
>
> *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*
> -----------------------------------------
>
> *From:* Chris <christ...@gmail.com <javascript:>>
> *To:* excel-...@googlegroups.com <javascript:>
> *Sent:* Tuesday, August 12, 2014 8:34 PM
> *Subject:* $$Excel-Macros$$ Values from INDIRECT not staying after file
> closes
>
>
> Hi,
>
> I have a rather strange problem: I have a master file with INDIRECT
> worksheet functions to three different files, alpha, beta and gamma (for
> simplicity's sake, the actual project has about 100 files). Without opening
> these three files, the cells with the INDIRECT calls in the master workbook
> obviously show a #REF error because the files they link to are not open. I
> use the following macro to open all three files in the folder "demofolder"
> and close them:
>
> Sub Open_All_Files()
>
> Dim wbOpen As Workbook
> Dim wbNew As Workbook
> Const strPath As String = "C:\Users\myuser\Desktop\demofolder\"
> Dim strExtension As String
>
> ChDir strPath
> strExtension = Dir("*.xlsx")
>
> Do While strExtension <> ""
> Set wbOpen = Workbooks.Open(strPath & strExtension)
>
> With wbOpen
> .Close SaveChanges:=False
> End With
>
> strExtension = Dir
> Loop
>
> End Sub
>
> The macro works fine, it opens and closes all three workbooks. However,
> and here's the issue, only the cells with the INDIRECT call to the very
> last workbook opened contain values now. Even though I opened two workbooks
> before, the INDIRECT worksheet functions still show #REF. And here's what's
> really bizarre - when I use the debugger and step through the code line by
> line until all three workbooks have been opened and closed, then all cells
> using INDIRECT contain values. I'm sure it's explainable - would love to
> know what causes this phenomenon. Also, would love to know how to fix it. I
> added a WAIT call prior to .Close because I thought that the workbook opens
> and then closes too fast but that didn't fix the issue.
>
> Thank you for all you help. Appreciate it a lot.
> Christoph
>
> --
> 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 <javascript:>.
> To post to this group, send email to excel-...@googlegroups.com
> <javascript:>.
> Visit this group at http://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 http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
