Indirect References to Unopened Workbooks

[mattygriff]

I have a spreadsheet (A) which is being partly populated via use of the Indirect() function from another spreadsheet (B).

Now, obviously spreadsheet (A) will only populate if spreadsheet (B) is open so I'm looking for advice on the following :

1. What is the quickest way to force spreadsheet (B) to open?

2. Is it possible to force spreadsheet (B) to open in a minimised or somehow "hidden" state? Alternatively, can spreadsheet (B) be opened as "read-only"?

3. Is there another way of populating spreadsheet (A) without having to open spreadsheet (B)?

Thanks in advance.

 

[mattygriff]

EDIT: Should have added to my above post that I have a fairly simple understanding of VBA and have used it on a few occasions.

 

[SkipVought]

Hi,

I generally try to avoid workbook links.

Take a look at MS Query. If you are referencing tabular data, it's the way to go. faq68-5829.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mattygriff]

Thanks for the response, Skip. Unfortunately, it looks like the INDIRECT() function is going to have to be used.

So, can anyone let me know

(a) how to open one workbook from within another

(b) how (if it is possible) to make the opened workbook either read-only or minimised (or both)?

Moving slightly off-topic, is there a way using VBA to copy and paste formulas as values? If so, this would allow me to only open the external workbook once and populate the necessary cells.

Thanks again in advance.

 

[PHV]

Have a look at the Range.PasteSpecial method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

If the closed workbook is hidden when last saved then it will open hidden.
Edit,Links, open will open the linked workbook.
Using the recorder you can get the code.



Gavin

 

[mattygriff]

Thanks, all, for your replies.

On reflection, it would probably be best if the workbook supplying the data was to get opened but in a "read-only" state.

So, is it possible to use VBA to set the status of one workbook as "read-only" from another? Or would I have to set the status directly in the workbook that I want to protect in advance?

 

[mattygriff]

OK, I've moved on and decided to simply open workbook (b) so that workbook (a) populates and then immediately close workbook (b). At the moment, my code is

Sub PREVIOUS_PSC()
    Prev_PSC_Filename = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Please select previous PSC Report...", MultiSelect:=False)
    If Prev_PSC_Filename <> False Then
        Sheet1.Range("D24").Value = Prev_PSC_Filename
        Workbooks.Open Prev_PSC_Filename
        Workbooks(Prev_PSC_Filename).Close SaveChanges:=False
    Else
        STARTUP
    End If
End Sub

However, although the second workbook opens fine, I then get a "Subscript out of range" error on the Workbooks.Close line.

Any advice please?

 

[PHV]

Replace this:
Workbooks.Open Prev_PSC_Filename
Workbooks(Prev_PSC_Filename).Close SaveChanges:=False
with this:
Dim WB As Workbook
Set WB = Workbooks.Open(Prev_PSC_Filename)
WB.Close SaveChanges:=False
Set WB = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattygriff]

Perfect, PHV - thanks a lot.

 

[Gavona]

I know you have the problem solved but for future reference:

So, is it possible to use VBA to set the status of one workbook as "read-only" from another?

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

Or when you save the workbook use File, SaveAs, Tools, GeneralOptions and set a Password to modify.

Gavin