Reusing VBA Sub to open excel files

Question

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.

What you are asking for sounds like a Class - heres a good like to start your research - CPearson.com — chris neilsen
– chris neilsen, Commented Feb 21, 2017 at 2:02

Slai · Accepted Answer · 2017-02-20 23:06:17Z

1

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

answered Feb 20, 2017 at 23:06

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wedge · Accepted Answer · 2017-02-20 23:28:52Z

0

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

answered Feb 20, 2017 at 23:28

Wedge

1,8561 gold badge11 silver badges15 bronze badges

1 Comment

NotJasonStatham Over a year ago

Thanks wedge. It works perfectly, I like this answer the most as it "outsources" a lot of the code elsewhere which is what I was trying to do. Also I think I can tailor the error handling to do more, which when shared with other people is crucial. Otherwise all I'm going to get back is... "it doesn't work..."

n8. · Accepted Answer · 2017-02-20 22:12:36Z

0

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.

answered Feb 20, 2017 at 22:12

n8.

1,7443 gold badges16 silver badges42 bronze badges

Collectives™ on Stack Overflow

Reusing VBA Sub to open excel files

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related