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.

[VBA]'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[/VBA]

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

[Bob Phillips]

[vba]

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

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