Copy Worksheet from unopened workbook 1

[kjv1611]

I am attempting to copy a worksheet from an unopened workbook into an open (the active) workbook. My method is getting errors.

First, here is the code I'm using:

Dim strWS As String 'ws1 name
Dim strWBPath 'To store the path and name of active workbook
strWS = ws1.Name
strWBPath = ActiveWorkbook.Path & ActiveWorkbook.Name
    [HIGHLIGHT]Workbooks("\\networkpath\Folder\WorkbookFrom.xls").Sheets("Query"). _
       Copy After:= Workbooks(strWBPath).Sheets(ws1.Name)[/HIGHLIGHT]

Next, the error message I'm getting:

Run-time error '9':

Subscript out of range

Any suggestions? It may be that I have to first open the other workbook, but I was hoping I could do it without opening it. But if I have to, I will.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

The key word is UNOPENED.

The Workbooks Collection is the collection of OPENED workbooks.

Must be OPENED.

Skip,

_[red][/red]

 

[kjv1611]

Oh, fooey!

Any way to do it OUTSIDE the workbooks collection?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Alright, let me know what else I need to do in this context:

I want to copy from a closed workbook to an open workbook.

So, do I do this?

Workbooks.Open "WorksheetPath", , , , , , , , , , False
   'The False there is to tell it not to notify, but I may not need that...
Then...
Workbooks("PrevClosedWorkbook.xls").Sheets("Query").Copy After:=Workbooks(strWBPath).Sheets(ws1.Name)

Or is that being to complicated..

I'm guessing that what I have to do is to select the worksheet, then copy it from the then "active workbook" to the other named workbook.

Does that make sense, or how else should I approach it?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

are you dealing with sheets as tables or other?

Skip,

_[red][/red]

 

[kjv1611]

What I was thinking was to keep my Table (with the query to a SQL server) in a separate workbook, so that it did not matter what workbook is used by the "user". Then, in code, I want to copy the spreadsheet that contains the table with the query to a SQL server table into the "active" workbook.

So, I'm wanting to copy a spreadsheet that has a table. Does that help any?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Use MS Query to do that.

Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[kjv1611]

The query is already setup with MS Query in the other sheet. So, I was thinking it would be simple just to copy the sheet over. I tried this manually copying from one to another, and it worked without a hitch.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

FYI, any file or database that you access in any way, must be OPENED.

Skip,

_[red][/red]

 

[kjv1611]

Alright, I've got code in place to open the "from" workbook, and now I've just got to get the copy part working correctly...

I found that I had the wrong Workbook name in the copy line, so I've updated that, but I'm still getting the same original message. I'm sure it's not as difficult as I'm making it.. Just needs some tweaking.. will hopefully fix here in a jiffy.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Thanks for the suggestions, Skip.

But for this one, I just did a strait copy, and it all worked just fine so far.

Here is what I ended up with:

Dim wbPrevActive As Workbook
Set wbPrevActive = ActiveWorkbook

Workbooks.Open "ClosedWorkbookPath"
ActiveWorkbook.ActiveSheet.Copy After:=wbPrevActive.Worksheets(ws1.Name)

Now, I just have to close the previously closed workbook, and clean up a wee bit, and all is good!


--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

BTW, I like to assign a workbook object to the new workbook...

Dim wbPrevActive As Workbook, wbNew as workbook
Set wbPrevActive = ActiveWorkbook

set wbnew = Workbooks.Open "ClosedWorkbookPath"
wbnew.sheets(1).Copy After:=wbPrevActive.Worksheets(ws1.Name)

ActiveWorkbook.ActiveSheet will cause you problems beings that the workbook is implied in the activesheet object.

Skip,

_[red][/red]

 

[kjv1611]

Gotcha! I'll clean that up as well. Thanks.

I did get the closing part to work as well...

I added:
Workbooks("WorkbookToClose.xls").Close

But with that idea in mind that you mentioned, I can clean this part up as well, by referencing the wbNew object.

Thanks again!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Skip,

For whatever reason, Excel didn't like this context:

set wbnew = Workbooks.Open "ClosedWorkbookPath"

So, I added parentheses to get this:

set wbnew = Workbooks.Open [b]([/b]"ClosedWorkbookPath"[b])[/b]

That seems to work fine.

And it worked fine.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

yes, I should have included...

Dim wbPrevActive As Workbook, wbNew as workbook
Set wbPrevActive = ActiveWorkbook

set wbnew = Workbooks.Open "ClosedWorkbookPath"
wbnew.sheets(1).Copy After:=wbPrevActive.Worksheets(ws1.Name)[b]
wbnew.close 
set wbnew = nothing[/b]

Skip,

_[red][/red]