CODEWITHSUNDEEP
vbaexcel
NotJasonStatham
NotJasonStatham

Reputation: 1

Reusing VBA Sub to open excel files

I'm slowly learning VBA and have achieved some good stuff essentially cutting and pasting from forums, but now I'm stuck.

Using this script here: WordMVP I can open excel files from Word. No problem. However now I have reused this script so many times my modules are starting to get long. Can I split this up into different subs? And call the parts when I need them? e.g: (hacking the linked code to bits)

Put this in one separate sub:

    Sub WorkOnAWorkbook(str As String)  

    Dim oXL As Excel.Application 
    Dim oWB As Excel.Workbook 
    Dim oSheet As Excel.Worksheet 
    Dim oRng As Excel.Range 
    Dim ExcelWasNotRunning As Boolean         

    'If Excel is running, get a handle on it; otherwise start a new instance of Excel 
    On Error Resume Next 
    Set oXL = GetObject(, "Excel.Application")

    If Err Then 
       ExcelWasNotRunning = True 
       Set oXL = New Excel.Application 
    End If 

    On Error GoTo Err_Handler 

    'Open the workbook 
    Set oWB = oXL.Workbooks.Open(FileName:=str)

    Exit Sub 

    Err_Handler: 
       MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
           "Error: " & Err.Number   

End Sub

and this in another:

Sub release()

'Make sure you release object references. 
Set oRng = Nothing 
Set oSheet = Nothing 
Set oWB = Nothing 
Set oXL = Nothing 

End Sub

and use them when I need them, e.g.:

Sub test()

Call WorkOnAWorkbook("somefile.xls")
oWB.Application.Run "Module1.TestMacro", "JasonX"
Call release()

End Sub

I get a 424 Object Required at oWB.Application.Run "Module1.TestMacro", "JasonX".

Is anything like this possible, part of me feels like I'm nearly there, part of me feels like I've totally screwed up, and part of me thinks its not possible...

Can anyone help please??

Thx a million.

Upvotes: 0

Views: 372

Answers (3)

Wedge
Wedge

Reputation: 1826

You can't just break up code however you want because your object references need to exist in the sub you are using them in. However you can use functions to return an object, and thus could do something like put all the code for finding/creating the Excel object in a function that returns the object.

Function OpenExcelInstance() As Excel.Application

    Dim oXL As Excel.Application

    'If Excel is running, get a handle on it; otherwise start a new instance of Excel
    On Error Resume Next
    Set oXL = GetObject(, "Excel.Application")

    If Err Then
       ExcelWasNotRunning = True
       Set oXL = New Excel.Application
    End If

    On Error GoTo Err_Handler

    Set OpenExcelInstance = oXL

    Exit Function

Err_Handler:
    MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, "Error: " & Err.Number
    Set OpenExcelInstance = Nothing

End Function

And then your test sub would be reduced to (though this code has no error checking if for some reason the Excel reference wasn't created or the workbook could not be opened).

Sub test()

    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook

    Set oXL = OpenExcelInstance()
    Set oWB = oXL.Workbooks.Open(FileName:="somefile.xls")

    oWB.Application.Run "Module1.TestMacro", "JasonX"

    Set oXL = Nothing
    Set oWB = Nothing

End Sub

Upvotes: 0

Slai
Slai

Reputation: 22866

You can shorten it to something like this (GetObject opens the file if it's not already opened):

Dim oWB As Object
Set oWB = GetObject("C:\somefile.xls", "Excel.Application")

oWB.Application.Visible = True   ' optional show the Excel Application to check the full path to the Macro
oWB.Application.Run "Module1.TestMacro", "JasonX"

If oWB.Application.Workbooks.Count = 1 Then oWB.Application.Quit Else oWB.Close  ' optional close the Excel Application or the Workbooks
Set oWB = Nothing

Upvotes: 1

n8.
n8.

Reputation: 1738

In your case the error occurs because oWB isn't declared in the sub that you are calling. Dim (declare), call/execute, and release all needs to happen in the same sub.

Upvotes: 0

Related Questions