Subscript out of range when trying to set worksheet variable

i have the below which sometimes work and sometimes does not.  It stops at the Set wks line.  This module is in an ACCESS 2003 database opening the Excel workbook.

Sandra

Option Explicit
'Daily upload of debits and credits from accounting
Const strPath = "\\Sf1\User1\shared\EFT\ATM\Private\Backend\ATMUpload.xls" 'Path to directory
Const strTab = "Cumulative Journal Transaction " 'Name of data source tab
Const strWKBName = "ATMUpload.xls"
Public Sub DefineDataRange()
On Error GoTo ErrorHandler
'Late Binding (Needs no reference set)
    Dim objExcel As Object
    Dim wks As Worksheet
    Dim wkb As Workbook
    Dim intRow As Integer
    Dim rngDataSource As Range
   
     '   Create a new Excel instance
    Set objExcel = CreateObject("Excel.Application")
     
     '   Open it
    With objExcel
        .Visible = True
        .Workbooks.Open (strPath)
        .Workbooks(strWKBName).Activate
        .Workbooks(strWKBName).Worksheets(strTab).Select
       
 'SOMETIMES RUNS SOMETIMES DOESN'T-Subscript out of range error
Set wks = Workbooks(strWKBName).Worksheets(strTab)
    End With
   
    With wks
        .Range("A5").Activate
        intRow = .Range("A200").End(xlUp).Row
    Set rngDataSource = Range("A5", "G" & intRow)
        rngDataSource.Name = "DataSource"
    End With
   
    Workbooks(strWKBName).Close SaveChanges:=True
     
Exit_ErrorHandler:
    Set objExcel = Nothing
    Exit Sub
     
ErrorHandler:
    objExcel.Visible = False
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
    Resume Exit_ErrorHandler
End Sub
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Sandra

I've changed the code to use a few variables.

I know you had variables but you didn't seem to be using them and they shouldn't be declared as Excel objects like Worksheet, Range etc especially if you are using late-binding.

Option Explicit

'Daily upload of debits and credits from accounting
Const strPath = "\\Sf1\User1\shared\EFT\ATM\Private\Backend\ATMUpload.xls" 'Path to directory
Const strTab = "Cumulative Journal Transaction " 'Name of data source tab
Const strWKBName = "ATMUpload.xls"

Public Sub DefineDataRange()

    On Error GoTo ErrorHandler
    'Late Binding (Needs no reference set)
    Dim objExcel As Object
    Dim objWB As Object
    Dim objWS As Object

    Dim intRow As Integer
    Dim rngDataSource As Object

    '   Create a new Excel instance
    Set objExcel = CreateObject("Excel.Application")

    '   Open it
    With objExcel
        .Visible = True
        Set objExcel = .Workbooks.Open(strPath)
    End With

    Set objWS = objWB.Worksheets(strTab)

    With objWS
        intRow = .Range("A200").End(xlUp).Row
        Set rngDataSource = .Range("A5", "G" & intRow)
        rngDataSource.Name = "DataSource"
    End With

    objWB.Close SaveChanges:=True
    Set objWB = Nothing

    objExcel.Quit
    
Exit_ErrorHandler:
    Set objExcel = Nothing
    Exit Sub

ErrorHandler:
    objExcel.Visible = False
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
    Resume Exit_ErrorHandler
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
try this coding

Set objExcel = CreateObject("Excel.Application")
     
     '   Open it
      objExcel.Workbooks.Open (strPath)
    With objExcel
        .Visible = True
       
        .Activate
        .Worksheets(strTab).Select
       
           Set wks = .Worksheets(strTab)
    End With
0
Sandra SmithRetiredAuthor Commented:
I got a variations of this to work.  Capricon, I did try what you indicated prior to posting the question, but I could not get it to work.  Thank you both.

Sandra
0
Sandra SmithRetiredAuthor Commented:
imnoie, this is what I changed, the objWB was not set (typo, happens)

    '   Open it
    With objExcel
        .Visible = True
        Set objWB = .Workbooks.Open(strPath)
    End With
0
NorieAnalyst Assistant Commented:
Oops, don't know how I missed that out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.