[RESOLVED] Accessing Excel workbooks

[AccessShell]

I have these two routines in my program

Routine 1

Sub GetAData()Dim strDirectory  As String
Dim strFileName   As String
Dim intLastRow    As Integer
Dim Col           As Integer
Dim Row           As Integer
    
    'Get the file name
    strDirectory = "C:\MY\Daily Activity\"
    strFileName = strDirectory & "MY Daily Activity.xls"
    
    'create Excel object
    Set mExcelAApp = CreateObject("Excel.Application")
   
    'open the workbook
    Set mExcelAWorkbook = mExcelAApp.Workbooks.Open(strFileName)
    Set mExcelAySheet = mExcelAWorkbook.Worksheets(1)
    
    'Add Today's Data
    With Sheets("Activity")
        intLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        mstrHoldAARow = CStr(intLastRow)
    End With


    With Sheets("Activity")
        mcurCMA99999Value = .Range("E" & mstrHoldAARow).Value
    End With


'***  Close the A Workbook
   ' mExcelWorkbook.Save
  'MsgBox "closeing the workbook"
    'Close Excel
    mExcelAWorkbook.Close savechanges:=False
    mExcelAApp.Quit
    Set mExcelAApp = Nothing
    Set mExcelAWorkbook = Nothing
    Set mExcelASheet = Nothing


End Sub

Routine 2

Sub GetBData()Dim strDirectory  As String
Dim strFileName   As String
Dim intLastRow    As Integer
Dim Col           As Integer
Dim Row           As Integer
Dim i             As Integer
Dim iMax          As Integer
Dim R             As Integer
    
    'Get the file name
    strDirectory = "C:\MY\Daily Activity\Volume\"
    strFileName = strDirectory & "Amounts.xls"
    
    'create Excel object
    Set mExcelBApp = CreateObject("Excel.Application")
   
    'open the workbook0
    Set mExcelBWorkbook = mExcelBApp.Workbooks.Open(strFileName)
    Set mExcelBSheet = mExcelBWorkbook.Worksheets(1)
    
    i = 0
    With Sheets("B List")
        For R = 2 To 19
            i = i + 1
            iMax = i
            If i = 1 Then
                ReDim BN(1 To i)
            Else
                ReDim Preserve BN(1 To i)
            End If
            BN(i).BondName = .Range("A" & R).Value
        Next R
    End With


'***  Close the BWorkbook
   ' mExcelWorkbook.Save
  'MsgBox "closeing the workbook"
    'Close Excel


    mExcelBWorkbook.Close savechanges:=False
    mExcelBApp.Quit
    Set mExcelBApp = Nothing
    Set mExcelBWorkbook = Nothing
    Set mExcelBSheet = Nothing


End Sub

If I run Routine 1, then Routine 2, the program crashes in Routine 2 on the purple line.
If I run Routine 2, then Routine 1, the program crashes in Routine 1 on the blue line.

In each case I get the same error message
Run-time error '1004';
Method 'Sheets' of object'_Global' failed

Can someone tell what's going on?

Thanks

[Elroy]

You've never declared "Sheets" in what you've shown. I guess a reference is created somewhere outside of those two procedures, but, the way you're opening Excel right there, that doesn't seem like the case.

With what you've got, I don't know why it's not crashing everytime it runs across that "Sheets" object.

Are you running this in VB6, or are you using Excel's VBA. If you're in Excel's VBA, then it's an entirely different problem, and that "Sheets" object is referencing the current workbook, and not the one you've just opened. The VBA has all kinds of "implicit" references that VB6 doesn't have. VB6 has them (like our Me. when accessing form objects), but it doesn't have them for Excel objects.

[AccessShell]

I am running this in VB6.

I'll follow up on the "Sheets" object.

[AccessShell]

I solved it.

Replace the blue line with mExcelAApp.Sheets("Activity")
Replace the purple line With mExcelBApp.Sheets("B List")

[Dan_W]

I solved it.

Replace the blue line with mExcelAApp.Sheets("Activity")
Replace the purple line With mExcelBApp.Sheets("B List")

Are you sure you don't mean to use the Workbook variables instead? Your workbook variables are already properly and fully qualified: mExcelAWorkbook and mExcelBWorkbook

[AccessShell]

Dan_W,
It also works as you suggested. I don't know enough to understand why both ways work. I, also, don't which way is correct.

[Elroy]

Dan_W,
It also works as you suggested. I don't know enough to understand why both ways work. I, also, don't which way is correct.

You should use the workbook object variables, not the app object variables.

You got away with it because it's defaulting to the last workbook you accessed (for each app object variable). However, you really shouldn't be opening Excel twice (two app variables). One app variable can open as many workbooks as you want. And, if you did it that way (which is the correct way), letting the workbook default (as your post #4 does) would no longer work.

When doing automation, you should always be explicit with your object variables. That prevents down-the-road confusion. In other words, if you're addressing a worksheet object, you should use a workbook (not app) object to address it. If you're addressing a workbook object, you'll always use some Excel app object variable to address it. And this same logic can be carried down to cells, and even objects/properties of cells, such as borders, font, etc, etc.

If you're just randomly trying things, that's not good at all. If you don't know the Excel object tree, then learn it!

[vb6forever]

I echo what Elroy said: This might get you started:https://learn.microsoft.com/en-us/of...l/object-model

Be aware there is a workbook and workbooks object, as well as sheet and sheets object, etc.

[AccessShell]

Thanks guys, I'll check it out.

[AccessShell]

I am still checking out the link. It is slow. However I tried to rewrite the code I published earlier. Here it is. Please evaluate.

Sub Main()   
    'Create Excel object
    Set mExcelApp = CreateObject("Excel.Application")
    
    GetAData
    GetBData
    getDislayWorkbook
    
    'Delete Excel Object
    mExcelApp.Quit
    Set mExcelApp = Nothing
          
End Sub


Sub GetAData()
Dim strDirectory  As String
Dim strFileName   As String
Dim intLastRow    As Integer
Dim Col           As Integer
Dim Row           As Integer


    'Get the file name of workbook 1
    strDirectory = "C:\MY\Daily Activity\"
    strFileName = strDirectory & "MY Daily Activity.xls"
    'open the workbook 1
    Set mExcelAWorkbook = mExcelApp.Workbooks.Open(strFileName)
    Set mExcelASheet = mExcelAWorkbook.Worksheets(1)
    'Get Last Row
    With mExcelAWorkbook.Sheets("Activity")
        intLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        mstrHoldAARow = CStr(intLastRow)
    End With
    
    
    With Sheets("Activity")
        mcurCMA99999Value = .Range("E" & mstrHoldAARow).Value
    End With
    
    
    'Close Workbook 1
    mExcelAWorkbook.Close savechanges:=False
    Set mExcelAWorkbook = Nothing
    Set mExcelASheet = Nothing


End Sub


Sub GetBData()
Dim strDirectory  As String
Dim strFileName   As String
Dim intLastRow    As Integer


     'Get the file name
    strDirectory = "C:\MY\Daily Activity\Volume\"
    strFileName = strDirectory & "Amounts.xls"
    'open the workbook 2
    Set mExcelBWorkbook = mExcelApp.Workbooks.Open(strFileName)
    Set mExcelBSheet = mExcelBWorkbook.Worksheets(1)
    'Get Last Row
    With mExcelBWorkbook.Sheets("B List")
        intLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    'Close Workbook 2
    mExcelBWorkbook.Close savechanges:=False
    Set mExcelBWorkbook = Nothing
    Set mExcelBSheet = Nothing


End Sub


Sub getDislayWorkbook()
Dim strDirectory  As String
Dim strFileName   As String
Dim intLastRow    As Integer


    'Get the file name of the Display Workbook
    strDirectory = "C:\MY\Daily Activity\Display\"
    strFileName = strDirectory & "Display.xls"
    'open the Display WOrkbook
    Set mExcelDisplayWorkbook = mExcelApp.Workbooks.Open(strFileName)
    Set mExcelDisplaySheet = mExcelDisplayWorkbook.Worksheets(1)
    'Get Last Row
    With mExcelDisplayWorkbook.Sheets("Allocations")
        intLastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1    '19
    End With
    'Add Allocation Data
    With mExcelDisplayWorkbook.Sheets("DisplayData")
        .Range("A" & intLastRow).Value = Format(Now, "ddd")
        .Range("B" & intLastRow).Value = Format(Now, "mm/dd/yyyy")
    End With


    'Close Display Workbook
    mExcelDisplayWorkbook.Close savechanges:=True
    Set mExcelDisplayWorkbook = Nothing
    Set mExcelDisplaySheet = Nothing


End Sub

Thanks