Setting a workbook object with full path

[DavidSCowan]

Can someone help please.

I want to Set wkb as the workbook<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264><COLGROUP><COL style="WIDTH: 198pt; mso-width-source: userset; mso-width-alt: 8448" width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 198pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=264>C:\Users\David\Documents\EasyXL\easy-xlIndex.xls

</TD></TR></TBODY></TABLE>
I thought that:
Dim wkb as workbook
Set wkb =workbooks("<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264><COLGROUP><COL style="WIDTH: 198pt; mso-width-source: userset; mso-width-alt: 8448" width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 198pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=264>C:\Users\David\Documents\EasyXL\easy-xlIndex.xls")
would do the trick but it doesn't (and I have tried every variant I can think of)
</TD></TR></TBODY></TABLE>

What it produces is: "Run-time error '9'"; "Subscript out of range"
(I don't want to use ActiveWorkbook or ThisWorkBook)

Can someone help please.

 

[shg]

But it still doesn't work.

Subscript out of range?

Take it apart piece by piece.

When the code errors, in the Immediate window, enter

Workbooks(fileia).Activate

Does that work?

This would be a good time to spend 30 minutes reading http://www.cpearson.com/excel/debug.htm

 

[DavidSCowan]

Hi

SUCCESS AT LAST!

After getting the very unhelpful error messages I took your advice and took it apart piece by piece. In the copy line I substituted the actual numbers e.g Cells(5,1) instead of Cells(finalrowi - 16, 1). When this was done it went through so the spotlight fell on the variables in that line. They were up the creak! I changed these and as suggested checked each one in the Immediate window. After spotting some other small errors the macro now works as intended.

I am really appreciative for all your help. In the past with the message board I have found that often what happens is that a completely different solution is suggested and I never find out what is wrong with mine and therefore there is no learning. And there is rarely any appetite for following through - the alternative solution is suggested and that is that - the caravan moves on - no replies to requests for clarification etc.. So I am doubly appreciative that you stayed on the case and sorted it out.

I have learnt a lot. And I have also started reading the Pearson reference.

Thanks again

David

 

[shg]

Good job!

You know that you can just hover the mouse over simple variables and see their values? Do take the time to read Chip Pearson's article for that and much more information.

 

[DavidSCowan]

Chip Pearson's article is great - I will use all the things outlined from now on. And hovering is great too.

And it has already pinpointed a problem. To keep things simple part of the code was hard wired e.g. Cells(finalrowi - 16, 1). But it seems that some measures didn't exist in 2002 so when, for the first of these files, the procedure gets to this line finalrowi =1 and finalrowi - 16 goes negative!

Thanks again for all your patience.

 

[shg]

Sorry, is that a question?

 

[DavidSCowan]

Thank you for asking but no not a question just reporting how the Chip Pearson article you suggested helped to track down the problem quickly.

 

[shg]

Excellent, well done.

Debugging your own code is the best way to get better.