Linking to unopened excel workbook

Microsoft Excel 2003 (full product)
November 4, 2009 at 11:18:24
Specs: Excel 2003
Hi All,

I am trying to reference an excel workbook that is on a LAN (at work). The idea is to copy the contents that is on sheet1 and paste it on sheet1 of the workbook where the macro is running. It seems to work when I have the workbook open. The reference to the workbook in this case was created using the 'Record Macro' feature and does not specify the full path name. However when the workbook is not open I get a subscript out of range error. Any help and a quick response will be greatly appreciated THANKS


See More: Linking to unopened excel workbook

Report •


#1
November 4, 2009 at 14:50:05
Hi,

1. If the workbook is not open, your macro will not know where it is - as you said yourself, it does not specify the full path name.
2. You can't copy and paste from a closed workbook.

You need to include the full path to the Workbook in your macro, then get the Macro to open it, copy & paste and then close it.

Regards


Report •

#2
November 4, 2009 at 16:41:22
While it's true that you cannot copy and paste from a closed workbook, you can link to it, pull in the data and then Copy/PasteSpecial the values.

Just as a basic example, this will essentially "copy" the data from A1:J10 of a closed workbook into A1:J10 of the workbook where the macro is run by creating formulae to link to the closed workbook and then eliminating the formulae to leave just the data.

Sub CreateLink()
'Fill A1:J10 with Link Formulae
  For myRw = 1 To 10
   For myCol = 1 To 10
     Cells(myRw, myCol).FormulaR1C1 = _
        "='C:\[Book1.xls]Sheet1'!R" & myRow & "C" & myCol
   Next
  Next
'Copy/PasteSpecial Values
  With Range(Cells(1, 1), Cells(10, 10))
   .Copy
   .PasteSpecial Paste:=xlPasteValues
  End With
End Sub

Obviously, if you need to deal with formatting and other "stuff", then the code would need to be more complex.


Report •

#3
November 5, 2009 at 06:14:30
Thanks Guys for your replies. I will explore both options to see which one is a better given the environmental parametres. Just as an aside and for my own edification, based on your example DerbyDad, how does one deal with a dynamic range since the number of records (from the source) changes from week to week. Really appreciate the help.

Wayne


Report •

Related Solutions

#4
November 5, 2009 at 07:56:53
After a little research, I found this, tried it and it worked great:

http://www.ozgrid.com/VBA/ExtractFr...

It's the same concept as mine, but a lot more elegant.


Report •

#5
November 5, 2009 at 08:14:16
Hi DerbyDad03,

Thanks for that info. from OzGrid. I hadn't seen that before.

Interesting, but I think I'll stick to opening workbooks, it's just as easy and a lot less code.

Regards


Report •

#6
November 5, 2009 at 08:43:11
re: but I think I'll stick to opening workbooks, it's just as easy and a lot less code.

Don't take this the wrong way, but "Are you serious?" :-)

It took me less than a minute to set up 2 workbooks to try the code offered at the Ozgrid site.

If you add the final piece to have it run as soon as you open the destination workbook, it will be automatic. It doesn't get much easier than that.


Report •

#7
November 5, 2009 at 08:51:04
Yep,

Seriously serious.


Report •


Ask Question