VBA – Excel forumla referencing a different workbo

Topic

New Reply

I have an Access app that builds excel workbooks. In 1 I have a cell that needs to reference a cell in another workbook on my hard drive.

I have the formula built like this:

='[WorkbookName.xls]SheetName’!$A$1

the formula works, but when stepping thru the code, as soon as it does this line:
xlapp.ActiveCell.Value = “='[WorkbookName.xls]SheetName’!$A$1”

Excel open the Open File dialog.

Not good for automating.

Is there a way around this?

Reply | Quote

Replies

If the other workbook is not open, you should specify the full path:

xlapp.ActiveCell.Value = "='C:MyFolder[WorkbookName.xls]SheetName'!$A$1"

You must, of course, substitute the actual path. Otherwise Excel cannot resolve the external reference.

Reply | Quote

Hans,

If I could hug you right now I would.

Consider yourself hugged!!

Reply | Quote

Is it possible to have that cell = the VALUE rather than be linked?

Reply | Quote

You could try this:

With xlapp.ActiveCell
.Value = "='C:MyFolder[WorkbookName.xls]SheetName'!$A$1"
.Value = .Value
End With

Reply | Quote