Solved: Reference another Excel workbook without Activating it

[scott56]

Hi,

I am running a command to convert some data in an Excel workbook to a new format. I would like to reference another Excel workbook while running the command. The only way I have been able to reference that data is by activating the other Excel workbook which I don't really want to do.....basically I would like only to have the initial workbook open and then read the data from the source workbook.

'Now lets Open the Source file to begin checking it is in the correct format
Workbooks.Open(strSourceFullFileName).Activate
Set mySourceRange = Worksheets("Sheet1").Range("A1")
MsgBox "Have found this value at A1" & mySourceRange.Offset(0, 0).Value

Is there anyway to reference that source workbook without activating it ?

[Bob Phillips]

Set wb = Workbooks.Open(strSourceFullFileName)
...
wb.Worksheets(1).Range("A1").Value = "something"

[scott56]

Thanks got that referencing ok.....but with the open the active workbook still changes to the book that is referenced....is there a way to reference a workbook without having to open it and display it to the user ?

[Bob Phillips]

Well, as you say, opening a workbook makes it active. But that should not be a problem if you use workbook variables for alal workbooks, and never refer to activeworkbook.