[RESOLVED] [Excel] Subscript out of range

[maurice_whittaker]

I am working in Excel version 6.5 and the following code opens the workbook but the attempt to copy gives "Subscript out of range" error.
The "Source" w/Book is known to have all the sheets named and the "Recipient w/book is known to have only one sheet.
MyBook is the total path plus file name plus extension (.xlsx).

How can I find out which "Subscript" is causing the error?

  Workbooks.Open Filename:=MyBook        ' Open the "Source" W/Book

                                               ' Copy the sheets over
Workbooks(MyBook).Sheets(Array("Income", "Expenditure", "Transfers", "Standing orders", "Consolidation", "Schedule B", _
  "Budget")).Copy Before:=Workbooks(ThisWorkbook.Name).Sheets(1)
  
'           ********************  Close the Slave WorkBook  *******************
  Workbooks(MyBook).Close

I have checked that the sheet names do not have any unwanted leading or trailing spaces but otherwise I am lost!
I would appreciate any help.

[Spoo]

MW

I can't see it off-hand, but that line contains several
"candidates". So, perhaps in the name of trouble-shooting,
the following code might help you drill down to the issue.

v1= Workbooks(ThisWorkbook.Name)
v2 = Workbooks(ThisWorkbook.Name).Sheets(1)
v3 = Workbooks(MyBook)
v4 = Workbooks(MyBook).Sheets(Array("Income", "Expenditure", "Transfers", "Standing orders", "Consolidation", "Schedule B",  "Budget")).

.. or something along those lines to see which line
triggers the error.

Spoo

[maurice_whittaker]

Thank you oops; Have you been up all night?

This is the sort of thing I was looking for, to help me get closer to the failure. I am still not !00% sure that the W/Books are good in every respect but they behave perfectly if I do these operations manually in excel. Is there any way that one can get a "health check" on workbooks to say what type they are (.xls, .xlsx, etc) and whether their internal format matches the extension? That would allay my fears, one way or the other!
I will now give it a try.

[Spoo]

MW

Re up all night .. well, it sometimes seems that way.

Re health check .. I'm not quite sure what you mean
here. Perhaps you could elaborate by giving some examples
of what you are talking about, what environment you are
working in (one computer, LAN, who creates the WB's, etc).

Spoo

[maurice_whittaker]

Hi Spoo, Just one computer (one too many at the moment). I tried your suggestion but the computer didn't like it. Don't forget that I am "wet behind the ears" re: VBA. I typed your lines in exactly but I suspect it is my Dim statements that are the trouble. I tried Dim v1 as Workbook; Dim v2 as Worksheet and got rather lost after that. My problems all seem to be around the creation of a new workbook and then moving sheets into it. I think I have produced the new w/book OK but it would be nice to be sure. The problem seems to be very erratic, where I get one part to work and another part (which did work) goes wrong! I wonder whether I have corrupted my program somehow! I went back to a previous version of my prog. but it is not cured.
My latest attempt was to try and move just one sheet but "Subscript out of Range" occurred again.
Any other thoughts would be appreciated. Maurice

[Spoo]

MW

OK, re "computer didn't like it" ...

The concept I suggested was for debugging purposes
only, so you would insert those lines just above the
"big" line that has the several candidate sources of error.

Try this in the sub you already have:

Workbooks.Open Filename:=MyBook        ' Open the "Source" W/Book
'
v1 = Workbooks(ThisWorkbook.Name)
v2 = Workbooks(ThisWorkbook.Name).Sheets(1)
v3 = Workbooks(MyBook)
v4 = Workbooks(MyBook).Sheets(Array("Income", "Expenditure", "Transfers", "Standing orders", _
      "Consolidation",  "Schedule B",  "Budget"))
'

                                               ' Copy the sheets over
Workbooks(MyBook).Sheets(Array("Income", "Expenditure", "Transfers", "Standing orders", "Consolidation", "Schedule B", _
  "Budget")).Copy Before:=Workbooks(ThisWorkbook.Name).Sheets(1)
  
'           ********************  Close the Slave WorkBook  *******************
Workbooks(MyBook).Close

Then do these steps ...

1. Put a breakpoint on the "v1 = ..." line.
2. Then run you macro
3. The macro will stop at the breakpoint.
4. Then step through the lines of code one line at a time .. press the F8 key once.
5. If you get an error on the v1 line, then stop .. we'll need to deal with that
6. If you get over v1 ok, then press the F8 key one more time
7. If you get an error on v2, then stop ..
8. I think you get the idea.
9. I'm trying to find out which of the 4 lines cause an error

Let me know which line is the first one to cause an error
and what the error is.

EDIT:

My bad ..
I just tried the v1 line myself as a test and it crashes,
>> error: Object doesn't support this property or method.

I now realize that I was improperly trying to parse the
"big" line of code.

So, please disregard my above suggestions.

I must now confess that I do NOT currently have a
solution for you. Sorry to have misled you.

My only remaining hunch is that it has something to do
with the Array("Income" ... "Budget") parameter.

Hopefully someone who uses Excel more than I do
will have an answer for you.

Spoo

[westconn1]

assuming all your workbook and sheet names are valid

on reading forum posts over the last few years, this seems to be a common error in office 2007, which may have been fixed in some service pack, there are ways to work around this problem, but if you are on office 2007, it may be better to find if there are any service packs available for your version of office

to test using spoo's code you need to use the set keyword as workbooks and sheets are objects
set v1 =

[Spoo]

Pete

I was so close, but yet so far!
Brilliant ..

Spoo

[Spoo]

MW

I see that you've marked the thread as RESOLVED.
Just curious .. what turned out to be the answer?

Spoo

[maurice_whittaker]

Hi Spoo, I haven't got one, but Pete seemed to suggest that it was a fault in VBA so I thought it best not to bother people with something that might be insoluble. I have been on the Microsoft updates site and my computer has had many, many updates and it would surely have been cured in one of those. I just don't know what to do unless someone can suggest the "work around" that Pete said existed. I do intend to try your suggestion with Set inserted, so that might give me a clue. When I said about a "health check", I meant something to tell me the internal properties of the workbook I have created. Unfortunately it is possible to create a W/Book with (for example) a .xls extension even if it is a .xlsx W/Book. I can't be sure what I am doing because my VBA does not recognize the Format codeword. Thanks again for your help. Maurice

[Spoo]

MW

Fair enough.

Keep us posted.
Hopefully a more satisfactory resolution can be reached.

Spoo

[maurice_whittaker]

You need to get some rest Spoo!. I tried your method with Set in front and it worked! It gave "subscript out of range"

this was Set v3 =Workbooks(MyBook) where MyBook was the whole path + File Name as below

MyBook = "C:\Documents and Settings\Maurice\My Documents\My Spreadsheets\Dawn\Church Accounts 15-16.xlsx"

I don't think that the length is a problem for path otr for a string, but I have been using it instead of the "Local" name because Pete said it was a better way.

Seems very strange eh? I will have to hurry this as I keep getting chucked off the site. Are they trying to tell me something?
I will be back Maurice

[Spoo]

MW

Sounds like we're making progress..

I'm pleased that with Pete's input, we've established
which "part" is the culprit. Interestingly, it was v3 and
NOT v4, the Array() part.

We await your return

EDIT

BTW, I'm on the other side of the pond, so it's
still daylight here

Spoo

[maurice_whittaker]

Spoo, I told you that you needed some rest! I am going to try just that bit of code with the "Local" file Name, ie "Church Accounts 15-16.xlsx" One thing strikes me & that is that the minus sign could possibly cause trouble, but as far as I can tell it should be ok? Anyway, I will try something else as well. I have used it for some years without trouble but I'm desperate!
I will do some work like that and I shall get some sleep. Thanks again, Maurice

[westconn1]

this was Set v3 =Workbooks(MyBook) where MyBook was the whole path + File Name as below

no should only be the filename.ext, not include path

different when you set a workbook object on opening a workbook

[maurice_whittaker]

Hi Spoo and Pete; I have tried the filename.ext only now and it fails this time on "Type Mismatch", so I then tried the literal string "Church Accounts 15-16.xlsx" which went through the Set v3 line ok but failed "Subscript out of range" on the line that contains the array as you suspected. I thought maybe it was my Dim's that were wrong but one doesn't need to dimension literals so it does not seem to be that. If Pete is watching over us perhaps he would elucidate on the work around? Incidentally, the code was originally recorded as a Macro so heaven knows what's wrong! Thanks again for your interest gentlemen.

[maurice_whittaker]

If anyone is still awake after my marathon problem, I have muddled through to what seems to be a more tractable problem which I will raise on a new string since this one may have "died the death". Thanks to Spoo and Pete who helped me a lot.