Getting name of last worksheet in EXTERNAL workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CompleteNewb@comcast.net - 19 Sep 2007 20:51 GMT

I'm trying to find out how to make a string variable's value the name of the
LAST sheet in an EXTERNAL workbook. In lieu of actually setting a string
variable, I've been using msgbox just to see instantly upon running the sub
whether I've got it or not.

I've tried:

msgbox Workbooks("D:\Big Folder\Little Folder\Last
Folder\WorkbookName.xls").Worksheets(Worksheets.Count).Name

I've tried:

Dim LastSht As String

Dim wkbk As Workbook
Dim wks As Worksheet

set wkbk =

...actually, I forget what all I did with that last starting point, I've
been trying so many things I delete the last 5 permutations when nothing
seems to work, and then start fresh. I think I was trying to declare the
workbook and worksheet objects, then setting the paths, then using the .name
property to set the string variable to the name of the last worksheet.

Anyway, nothing I'm trying has been working. It doesn't seem to work
whether the external workbook is open or not. Can anyone help me figure out
how to get the name of the LAST sheet (the text in the tab of the sheet that
is rightmost in the workbook) of an external workbook (not in same
directory)? Let's assume my current workbook is in D:\Whatever\ and the
external workbook is in D\Somewhere else\Somewhere else again\.

Any help would be greatly appreciated, and thanks for reading.

CompleteNewb

Reply to this Message

JW - 20 Sep 2007 03:04 GMT

On Sep 19, 3:51 pm, "CompleteN...@comcast.net"
<completen...@comcast.net> wrote:

> I'm trying to find out how to make a string variable's value the name of the
> LAST sheet in an EXTERNAL workbook. In lieu of actually setting a string
[quoted text clipped - 31 lines]
>
> CompleteNewb

While it is possible to access a closed workbook, I find it easier to
just open the workbook in question and keep it hidden. Something like
this:
Sub foofer()
Dim wb As Workbook
On Error GoTo errHandler
Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:\VBATest.xls", , True)
MsgBox wb.Sheets(wb.Sheets.Count).Name
wb.Close False
errHandler:
Set wb = Nothing
Application.ScreenUpdating = True
End Sub

You might want to consider throwing in some code to check if the
workbook in question is already open.

Reply to this Message

CompleteNewb - 22 Sep 2007 21:29 GMT

Many thnaks, JW, worked like a charm. I'm cool with opening and keeping
hidden, it's a perfectly fine substitute, and from what I've seen about
accessing closed workbooks, it's worth saving the trouble.

Thanks a lot, you saved me hours of fruitless experimentation.

The Complete Newb

> On Sep 19, 3:51 pm, "CompleteN...@comcast.net"
> <completen...@comcast.net> wrote:
[quoted text clipped - 56 lines]
> You might want to consider throwing in some code to check if the
> workbook in question is already open.

Reply to this Message