referencing unopen workbooks 1

[Luis939]

would there be a way to reference a workbook if its not open

I knew the path of the file and the filename so i tried
workbooks(path & filename)......

but that didnt work because the path was not part of the file name, so is there a way around this so that i can do work on the workbook without opening it? thanks!

 

[SkipVought]

4335,

What do you want to do in the unopened workbook.

If its tabular data, you can write an insert/update/delete query -- not recommended, 'cuz Excel eventually spits at you.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

well the reason im asking is becuase i used this

mypath = "C:\"
MyFile = Dir(mypath, vbNormal)

Do
Workbooks.Open Filename:=mypath & MyFile, UpdateLinks:=0
MyFile = Dir
Loop While MyFile <> &quot;&quot;

to determine what files were in the path i specified, i wanted to run one set of instructions with each file after i opened them....but then i wanted to go through each file again to do another set of instructions, so i dont want to reopen them becuase UPDATELINKS:=0 alters the workbook so that if it is reopen, it forces me to either end the program or reopen it unsaved, so what should i do? should i store the filenames in an array to reference them or what is best?? thanks!!

 

[SkipVought]

Why don't you

1. open all the workbooks

2. loop thru the WorkBooks Collection as many times as you need to

for each wb in WorkBooks
   'do stuff to workbook wb
...
next

3. close/save each wb

Or am I missing something?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

well the only thing is that the macro i am running is stored in one workbook that can't have all those instructions run on it, whats the best way to avoid &quot;thisworkbook&quot; from having those instructions run....

For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name And wb.Name <> &quot;PERSONAL.XLS&quot; Then
'execute instructions
Next
??

 

[SkipVought]

Yea, sure.

For multiple conditions like that, I like the Select case statement for clarity of whats heppnin'...

For Each wb In Workbooks
  With wb
     Select case .Name 
     case ThisWorkbook.Name, "PERSONAL.XLS" 

     case else
        'execute instructions
  End with
Next
[code]
Both ways work fine.  :-)

Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[Luis939]

On true true true, thanks!!!