Word VBA 2007 vs 2010 1

[DrSimon]

Below is an extract of some code that runs in Word 2007 calling Excel 2007 that works fine.

Set AppExcel = CreateObject("Excel.Application")
    SpreadFullName = "C:\Users\Username\Workbook.xlsm"
    SheetName = "Worksheet"
    With AppExcel
        ' Open Spreadsheet, hide and activate
        Set VarBook = .Workbooks.Open(SpreadFullName)
        VarBook.Worksheets(SheetName).Range("A1").Select
        VarBook.Close
        .Quit
    End With

However when I run it from Word 2010 calling Excel 2010, the following line:

         VarBook.Worksheets(SheetName).Range("A1").Select

gives me Run-time error '1004' Select method of Worksheet class failed.
One difference of course is that 2007 uses Excel 12.0 Object Library and 2010 version 14.0, but this is hardly complicated code. Has anyone any idea what to do? It doesn't make any diffference if I declare the variables in Dim statements.

Thanks in advance.

 

[DrSimon]

Ahhh there was one other difference, that make all the difference. That sheet was hidden in the 2010 version!

Sorry if you've looked to solve this!

 

[SkipVought]

Hi,

In your posted code, there is no reason to SELECT anything. It actually does nothing. And in practice, I generally avoid using the select method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrSimon]

Skip
You nearly always provide what is needed rather than what I think I want, so let me explain.

This is obviously just a chuck of code. After selecting "A1" the next line is:
Selection.CurrentRegion.Select

which selects all the contiguious rows and columns to give me my 2D list of data that can change 'size' according to circumstances. I then count the rows and columns and off I go.

Is there any other way to do this, it would be great if there was.

Simo

 

[SkipVought]

with VarBook.Worksheets(SheetName).Range("A1").currentregion
  lFirstRow = .Row
  lLastRow = .rows.count + lFirstRow - 1
  iFirstCol = .column
  iLastCol = .columns.count + iFirstCol - 1
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrSimon]

You are the wind beneath my wings!

Thanks Skip - I'd not looked at that property. Far more efficient

Simon

 

[SkipVought]

Pleased to assist.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}