0

I've got probably what is a simple question but I can't figure out what's going wrong. I'm trying to create one big Macro in excel that will do multiple things to multiple files. The context:

The excel sheet I am running the Macro from is in its own folder. I've set up a little testing folder with this structure: C:\Users\schris\Desktop\Eprime Testing\

  • This folder has two folders in it:

    • \Master Dataset\

      • In this folder is where the excel file with the macro is

    • \Eprime Processing\

      • There are three folders in this folder, named 'Fear', 'Gender', and 'Happy'. In each of those folders is an excel file that I wish to open.

What I wanted to do was create a Sub RunAll that would call various other subs because there are many different things I want the Macro to do, and I wanted to keep it organized.

So:

Sub RunAll()
Call OpenWorkbooks
Call ProcessFear
Call ProcessGender
Call ProcessHappy

End Sub

Here is my OpenWorkbooks code:

Sub OpenWorkbooks()
Dim wb1 As Workbook
Dim wbFear As Workbook
Dim wbGender As Workbook
Dim wbHappy As Workbook
Dim FileToOpen As Variant
Dim FileToOpen2 As Variant
Dim FileToOpen3 As Variant
Dim Sheet As Worksheet

' Must be workbook with the Macros I'm running
Set wb1 = ActiveWorkbook

' Opens Fear
ChDir "C:\Users\schris\Desktop\Eprime Testing\Eprime Processing\Fear"

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose Fear file")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wbFear = Workbooks.Open(fileName:=FileToOpen)
End If

Set wbFear = ActiveWorkbook

' Opens Gender
ChDir "C:\Users\schris\Desktop\Eprime Testing\Eprime Processing\Gender"

FileToOpen2 = Application.GetOpenFilename _
(Title:="Please choose Gender file")

If FileToOpen2 = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wbGender = Workbooks.Open(fileName:=FileToOpen2)
End If

Set wbGender = ActiveWorkbook

' Opens Happy
ChDir "C:\Users\schris\Desktop\Eprime Testing\Eprime Processing\Happy"

FileToOpen3 = Application.GetOpenFilename _
(Title:="Please choose Happy file")

If FileToOpen3 = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wbHappy = Workbooks.Open(fileName:=FileToOpen3)
End If

Set wbHappy = ActiveWorkbook
End Sub

Now I want to be able to reference these three workbooks (wbFear, wbGender, wbHappy) and move seamlessly between them. When testing in Sub OpenWorkbooks(), doing wbFear.Activate would work and process correctly... But when I separated out the tasks of each macro (i.e., OpenWorkbooks now only opens the workbooks, ProcessFear only processes the data in the Fear workbook), I get a Run-time error '91': Object variable or With block variable not set.

I'm assuming this has something to do with the declared workbook names being 'lost' as it switches Subs, but when I put the code from OpenWorkbooks into RunAll and only had ProcessFear run, it still couldn't activate the proper workbooks.

Basically, my question is this:

How can I have one Macro open three workbooks, and declare them all as something that other macros can reference? There are many tasks I need to do, so I really want to have separate Subs for each one, that way I don't get lost in the code.

Thank you for your time!

Christie Smith
  • 25
  • 1
  • 6
  • tl;dr but regarding`declare them all as something that other macros can reference` set them to the variables you created...`wb1`, `wbFear`,`wbGender`, `wbHappy`. – findwindow Apr 06 '16 at 20:55
  • I do set the workbooks that are opened as those variables, but they no longer work if I run a different sub. – Christie Smith Apr 06 '16 at 20:57
  • Oh. Declare them as `public`? Edit: that is do so outside of any subs. Usually at the very top above everything else. Edit2: public scope is bad for debugging though. Have you considered passing the books as arguments? – findwindow Apr 06 '16 at 20:57
  • 1
    Add a `wb As WorkBook` parameter to each of ProcessFear, ProcessGender and ProcessHappy. Within each of those you can refer to `wb` as the workbook to work with. At the end of `OpenWorkbooks` add `ProcessFear wbFear` etc – Tim Williams Apr 06 '16 at 20:59
  • Tim Williams, I am not quite sure what you mean... @findwindow I haven't considered that... Would I still be able to have ProcessFear/Gender/Happy be different subs, with code separate from OpenWorkbooks? – Christie Smith Apr 06 '16 at 21:05
  • @ChristieSmith Tim is saying the same thing I am but yes to your question. Also, you want Tim, not me ^_^; – findwindow Apr 06 '16 at 21:07
  • I'd have "RunAll" show a UserForm with 4 buttons each launching one of those subs (OpenWorkbooks, ProcessFear, ProcessGender, ProcessHappy) and have the first one write workbooks names in userform hidden (visible=false) labels so that any sub could access them and set a proper workbook variable from. – user3598756 Apr 06 '16 at 21:48
  • Possible duplicate of [Run same excel macro on multiple excel files](https://stackoverflow.com/q/14766238/608639) – jww Oct 17 '18 at 05:25

1 Answers1

0

There are better, more elegant solutions. But this is the simplest and also the easiest for you to implement. Declare 3 global object variables to hold the workbook references. Open the workbooks and assign to these variables in OpenWorkbooks. Use them as needed. Close them and set them to Nothing in a new procedure CloseWorkbooks. (Call is not needed in this context)

Public gwbFear as WorkBook
Public gwbGender as WorkBook
Public gwbHappy as WorkBook

Sub RunAll()
    OpenWorkbooks
    ProcessFear
    ProcessGender
    ProcessHappy
    CloseWorkbooks
End Sub
MikeC
  • 960
  • 1
  • 7
  • 15