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?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
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
 
NorieVBA ExpertCommented:
Oops, don't know how I missed that out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.