Why doesn't MOVE work?? - Xtreme Visual Basic Talk

07-14-2008, 10:44 AM

LadyAmazon LadyAmazon is offline

Junior Contributor

Join Date: Mar 2004

Location: New York

Posts: 316

Why doesn't MOVE work??

Code:

Set xlApp = New Excel.Application
For I = LBound(a) To UBound(a)
    fname = fGetSpecialFolder(CSIDL_DOCUMENTS) & "\" & a(I) & ".xls"
     Set xlWB = xlApp.Workbooks.Open(fname)
    xlWB.Worksheets.Move After:=ThisWorkbook.Worksheets(Worksheets.Count)
    xlWB.Close
Next I

Hi,

Using vba, I have the above code in an XL Module. I am trying to Move "a(I).xls" worksheet into the current open excel session, but keep getting an error at the Move statement - the move never happens. Can anyone see what I'm doing wrong?

Any help appreciated.

07-14-2008, 11:55 AM

shg

Junior Contributor

* Expert *

Join Date: May 2008

Posts: 297

I don't think you can apply the Move to the collection; you can create an array of worksheets and move them en masse, or move them one at a time in a loop.

07-14-2008, 12:11 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

I have specifically named a source sheet and removed 'ThisWorkbook' to see a sheet actually move:

Code:

'by number
xlWB.Worksheets(1).Move after:=Worksheets(Worksheets.Count)
'or by name:
xlWB.Worksheets("Sheet1").Move after:=Worksheets(Worksheets.Count)

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

07-14-2008, 01:09 PM

LadyAmazon LadyAmazon is offline

Junior Contributor

Join Date: Mar 2004

Location: New York

Posts: 316

Thanks for replies.

I can't understand why it still doesn't work.

I removed "ThisWorkbook" and I get the error:

1004, Move method of WorkSheet class failed.

Code:

For I = LBound(a) To UBound(a)
    fname = fGetSpecialFolder(CSIDL_DOCUMENTS) & "\" & a(I) & ".xls"
    Set xlWB = xlApp.Workbooks.Open(fname)
     xlWB.Worksheets(1).Move After:=Worksheets(Worksheets.Count)
    xlWB.Close
Next I

It's odd because I have IntelliSense on and whenever I type "(1)." after the dot, no methods appear.
if I leave off (1), and type "Worksheets.", Move, copy and the other methods appear. Strange... what does it mean??

Last edited by LadyAmazon; 07-14-2008 at 01:26 PM.

07-14-2008, 02:02 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

1. Does it fail in the first iteration when I=LBound(a)?
2. If you explicitly make the workbook visible using 'xlApp.Visible = True' before the .Move, what do you see in Excel when it errors (how many sheets, etc.)?

Edit: Are you actually trying to move a worksheet between workbooks as Colin suggests? I wasn't gathering that.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

07-14-2008, 02:15 PM

LadyAmazon LadyAmazon is offline

Junior Contributor

Join Date: Mar 2004

Location: New York

Posts: 316

Hi Guys,

Cerian Knight: It does fail on the first iteration.
I know that there are valid values, and that both workbooks are opened inside XL. It hits the Move and fails. I see that nothing was moved. One WB has 2 Sheets (I would like to move the other WS to the end of this one) The other has one worksheet.

Now I'm trying this code, there is no error, but still the move does not happen:

Code:

    Set xlWB = xlApp.Workbooks.Open(fname)
    xlWB.Activate
    ActiveSheet.Move after:=ThisWorkbook.Sheets(Sheets.Count)
    xlWB.Close

07-14-2008, 02:47 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

I believe Colin is correct...you need to create a new workbook in the same instance of Excel. I don't think you should be using 'New', but haven't yet worked out the rest of the details.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

Last edited by Cerian Knight; 07-14-2008 at 03:05 PM.

07-14-2008, 01:58 PM

Colin Legg

Out Of Office

Retired Moderator
* Expert *

Join Date: Mar 2005

Location: London, UK

Posts: 3,398

Hi LA

I suspect you can't move a sheet between workbooks in two different instances. I will check this tomorrow.

Colin

Edit by Colin_L:
Hi, 'm back again. Right... I just fired up two instances of Excel on my laptop. Each instance has a blank workbook open.
I tried to drag a worksheet from one workbook to the other and it wouldn't let me.

I think that's your answer...

__________________
RAD Excel Blog

Last edited by Colin Legg; 07-14-2008 at 02:32 PM. Reason: follow up

07-14-2008, 02:35 PM

Colin Legg

Out Of Office

Retired Moderator
* Expert *

Join Date: Mar 2005

Location: London, UK

Posts: 3,398

Hi LA,

See my edited answer above.

You will be able to move the worksheet between workbooks within the SAME instance of Excel without issue (provided that the workbook structures aren't protected and there are no version compatability issues between the workbooks etc....)

Hope that helps.
Colin

__________________
RAD Excel Blog

#10

07-14-2008, 02:48 PM

LadyAmazon LadyAmazon is offline

Junior Contributor

Join Date: Mar 2004

Location: New York

Posts: 316

Hi,

Is this what you mean?

I open "dupe.xls" and create an instance of XL to open "recs.xls"

Then I attempt to do the move. So in dupe.xls there's this code:

Sub importWBs()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim fname As String

Set xlApp = New Excel.Application
For I = LBound(a) To UBound(a)
fname = fGetSpecialFolder(CSIDL_DOCUMENTS) & "\" & a(I) & ".xls"
Set xlWB = xlApp.Workbooks.Open(fname)
xlWB.Sheets(1).Select
xlWB.Sheets(1).Move After:=Workbooks("dupe.xls").Sheets(2)
xlWB.Close
Next I

I open the 2nd Workbook using an instance, then try to move the Sheet.

So can I Move it with the file closed? If not, how do I proceed?
Thanks

Last edited by LadyAmazon; 07-14-2008 at 02:52 PM. Reason: clarity

#11

07-14-2008, 03:04 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

Get rid of Dim xlApp and all references to it.
Use 'Set xlWB = Workbooks.Open(fname)' instead.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

#12

07-14-2008, 03:14 PM

LadyAmazon LadyAmazon is offline

Junior Contributor

Join Date: Mar 2004

Location: New York

Posts: 316

Yippe!

It did move the Worksheet now.

Only thing is, in reading I discovered that:

Quote:

When moving a sheet out of a workbook that only contains one sheet then it wont move as you must have at least one sheet in a workbook minimum at all times.

As i said, the WS moved, but I got an error msg (at the Close statement) and the app stopped.
I'll use Copy to get around this, then go back and delete the un-needed copied xls files.