Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Why doesn't MOVE work??


Reply
 
Thread Tools Display Modes
  #1  
Old 07-14-2008, 10:44 AM
LadyAmazon LadyAmazon is offline
Junior Contributor
 
Join Date: Mar 2004
Location: New York
Posts: 321
Default 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.
Reply With Quote
  #2  
Old 07-14-2008, 11:55 AM
shg shg is offline
Junior Contributor

* Expert *
 
Join Date: May 2008
Posts: 297
Default

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.
Reply With Quote
  #3  
Old 07-14-2008, 12:11 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,813
Default

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)
__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to two-dimensional semi-circular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed re-definition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.
Reply With Quote
  #4  
Old 07-14-2008, 01:09 PM
LadyAmazon LadyAmazon is offline
Junior Contributor
 
Join Date: Mar 2004
Location: New York
Posts: 321
Default

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.
Reply With Quote
  #5  
Old 07-14-2008, 01:58 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,398
Default

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
Reply With Quote
  #6  
Old 07-14-2008, 02:02 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,813
Default

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.
__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to two-dimensional semi-circular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed re-definition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.
Reply With Quote
  #7  
Old 07-14-2008, 02:15 PM
LadyAmazon LadyAmazon is offline
Junior Contributor
 
Join Date: Mar 2004
Location: New York
Posts: 321
Default

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
Reply With Quote
  #8  
Old 07-14-2008, 02:35 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,398
Default

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
Reply With Quote
  #9  
Old 07-14-2008, 02:47 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,813
Default

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.
__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to two-dimensional semi-circular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed re-definition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.

Last edited by Cerian Knight; 07-14-2008 at 03:05 PM.
Reply With Quote
  #10  
Old 07-14-2008, 02:48 PM
LadyAmazon LadyAmazon is offline
Junior Contributor
 
Join Date: Mar 2004
Location: New York
Posts: 321
Default

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
Reply With Quote
  #11  
Old 07-14-2008, 03:04 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,813
Default

Get rid of Dim xlApp and all references to it.
Use 'Set xlWB = Workbooks.Open(fname)' instead.
__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to two-dimensional semi-circular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed re-definition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.
Reply With Quote
  #12  
Old 07-14-2008, 03:14 PM
LadyAmazon LadyAmazon is offline
Junior Contributor
 
Join Date: Mar 2004
Location: New York
Posts: 321
Default

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.
Reply With Quote
  #13  
Old 07-14-2008, 03:16 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,398
Default

Or just insert an empty sheet before you move out the last one that you want...
__________________
RAD Excel Blog

Last edited by Colin Legg; 07-14-2008 at 03:22 PM.
Reply With Quote
  #14  
Old 07-14-2008, 03:21 PM
LadyAmazon LadyAmazon is offline
Junior Contributor
 
Join Date: Mar 2004
Location: New York
Posts: 321
Default

Thanks Guys,

The Copy work like a charm. Great tip Colin, I'll try it. (CJ)

LadyA
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->