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