0

I would like to open a workbook in a new instance of Excel, then copy ranges from within several different sheets in another (already open) workbook to specified ranges in the newly opened workbook.

Here's what I have so far:

Dim wbl As Workbook
Set wbl = Workbooks("exports  - 27thNov.xlsm")
Dim wsl As Worksheet
Set wsl = Sheets("odbd")
Dim lrl As Long
lrl = wsl.Range("A" & Rows.Count).End(xlUp).Row

Dim wbl2 As Workbook
Set wbl2 = Workbooks("pltemplate.xlsx")
Dim wsl2 As Worksheet
Set wsl2 = wbl2.Sheets("S")

wbl.Worksheets("odbd").Range("A2:AK" & lrl).Copy
wbl2.Activate
wsl2.Range("A4").Select
wsl2.Range("A4").PasteSpecial Paste:=xlPasteValues

Note: the Activate & Select commands were added to try to force the macro to work in some way.

Also, I changed the name of the destination workbook ("pltemplate.xlsx") to make it a more simple string, as well as the name of the source worksheet ("S"), yet I get the same run-time error message.

The line which throws out the error is:

Set wbl2 = Workbooks("pltemplate.xlsx")
Community
  • 1
  • 1
lynX
  • 1
  • 1
  • 1
    In the immediate window (or the variables brower), inspect the Workbooks collection. The name might be slightly wrong. i.e. put `? Workbooks(0).Name` then try `? Workbooks(1).Name` etc. – Nick.Mc Nov 27 '19 at 10:42
  • 1
    `Set wbl2 = Workbooks("pltemplate.xlsx")` When you execute this line, the workbook `pltemplate.xlsx` must be already opened **in the same instance of Excel**. IF the workbooks is not opened, you must use `Set wbl2 = Workbooks.Open("pathtofile/pltemplate.xlsx")` – Foxfire And Burns And Burns Nov 27 '19 at 10:42
  • @FoxfireAndBurnsAndBurns That makes sense, originally my code was Workbooks("source.xlsx").Worksheets("odbd").Range("A2:AK" & lrl).Copy - the same format for the paste destination but I would still get the same run-time error, which is when I thought to try defining the variables – lynX Nov 27 '19 at 10:46
  • @Nick.McDermaid could you kindly direct me on how to do so, I'm still quite new to VBA and not too familiar with all the terms and tools – lynX Nov 27 '19 at 10:47
  • Before using `Workbooks("source.xlsx").Worksheets("odbd").Range("A2:AK" & lrl).Copy`, the workbook must be already opened as I said. About what @Nick.McDermaid said, he means you must check the VBA Editor Project tree, and see if the Workbook `pltemplate.xlsx` is there. Check https://bettersolutions.com/vba/visual-basic-editor/projectexplorerwindow-window.png. The Workbook should be there – Foxfire And Burns And Burns Nov 27 '19 at 10:51
  • The destination workbook (pltemplate) is already open, there's some code before the above code which opens a new instance of excel then opens the pltemplate workbook into it. In regards to the editor project tree, the destination workbook isn't there, is there a way to add it to the source workbook? – lynX Nov 27 '19 at 10:55
  • The immediate window is `CTRL-G`. What does `? Workbooks(0).Name` say? What about `? Workbooks.Count`? There are a lot of excellent tips here https://www.myonlinetraininghub.com/debugging-vba-code – Nick.Mc Nov 27 '19 at 10:56
  • @Nick.McDermaid I opened the immediate window and typed in ? Workbooks(0).Name and hit enter (hope that's correct) and it just throws out the same run-time error again? Workbooks.count returns "1" – lynX Nov 27 '19 at 11:02
  • Sorry.. it's been a long time. `? Workbooks.Count` means "print the number of items in the workbook collection - you only have one item. I messed up the other one it should be `? Workbooks(1).Name` which means "print the name of the first iten in the workbooks collection". Basically the workbook isn't open for some reason. At a guess, you need to open the workbook in the _current_ excel instance, not a new one. – Nick.Mc Nov 27 '19 at 11:04
  • 1
    @Nick.McDermaid I see, I'm guessing that the most simple solution is to open the destination workbook in the same instance of excel as the source workbook? – lynX Nov 27 '19 at 11:09
  • Yep that'd be right but not certain of the syntax right at this minute – Nick.Mc Nov 27 '19 at 11:10
  • *The destination workbook (pltemplate) is already open, there's some code before the above code which opens a new instance of excel* Then you can't do anything between worbooks opened in diferent instances of Excel. Each VBA macro runs across own instance, so your only solution is opening both workbooks inside same instance of excel – Foxfire And Burns And Burns Nov 27 '19 at 11:23
  • Hey guys thanks for your advice, the macro runs fine now that the destination workbook is opened within the same instance of excel as the source workbook. – lynX Nov 27 '19 at 11:24

1 Answers1

0

After last OP comment, the solution was opening both workbooks in same instance of Excel.

The destination workbook (pltemplate) is already open, there's some code before the above code which opens a new instance of excel

Then you can't do anything between worbooks opened in diferent instances of Excel. Each VBA macro runs across own instance, so your only solution is opening both workbooks inside same instance of excel:

Set wbl2 = Workbooks("pltemplate.xlsx") When you execute this line, the workbook pltemplate.xlsx must be already opened in the same instance of Excel.

IF the workbooks is not opened, you must use:

Set wbl2 = Workbooks.Open("pathtofile/pltemplate.xlsx")

More info about Excel instances and/or related info:

How to reference a workbook from another excel instance

Running Multiple Instances of Excel simultaneously from VBA?

Can having multiple instances of Excel open cause VBA issues?

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38