Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Subscript out of range when trying to set worksheet variable

Posted on 2012-08-27
5
Medium Priority
?
794 Views
Last Modified: 2012-08-27
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
0
Comment
Question by:ssmith94015
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38337565
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38337591
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
 

Author Closing Comment

by:ssmith94015
ID: 38337612
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
 

Author Comment

by:ssmith94015
ID: 38337622
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
 
LVL 35

Expert Comment

by:Norie
ID: 38338036
Oops, don't know how I missed that out.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question