Use the same workbook in multiple modules

Question

I have a workbook with two buttons. So an user can press them to open two workbooks (Input and Output). Then he will copy data from Input to Output. My problem here is that I don't know how to define the same workbook for my subs.

This is the code for opening the files:

Public wb1 As Workbook
Public wb2 As Workbook
Public result As Integer
Public fDialog As FileDialog

Public Sub inputs()
Set fDialog = Application.FileDialog(msoFileDialogOpen)
Set control = Workbooks("Control.xlsm").Worksheets("Control")
fDialog.Title = "Select a file"

fDialog.Title = "Select a file"

 If fDialog.Show = -1 Then
    If Right(fDialog.SelectedItems(1), 5) = ".xlsx" Or Right(fDialog.SelectedItems(1), 4) = ".xls" Then
        Set wb1 = Workbooks.Open(fDialog.SelectedItems(1))
        control.Cells(6, 2) = fDialog.SelectedItems(1)
    Else
        MsgBox ("Please select an excel file")
        Exit Sub
    End If
 End If
End Sub

This is the other sub for copying data:

Public Sub stack()
 For Each ws In wb1.Worksheets
  '---here is the code for copying data---
 Exit For 
Exit Sub

When I run this code, of course it gives me this error "object variable or With block not set". Do you know how can I resolve this? How can I use the same wb1 for both subs?

user_15597936 · Answer

It's better to limit the scope of your variable whenever possible so what you can do is this:

Public Sub stack()
    Dim wbControl As Worksheet
    Set wbControl = Workbooks("Control.xlsm").Worksheets("Control")

    'Check if there is possible input path in cell B6
    If wbControl.Cells(6, 2).Value2 = vbNullString Then
        MsgBox "Provide the Input workbook path first!"
        Exit Sub
    End If
    
    'Check if there is possible output path in cell B6
    If wbControl.Cells(6, 2).Value2 = vbNullString Then
        MsgBox "Provide the Output workbook path first!"
        Exit Sub
    End If
    
    'More error checking - e.g. check if both path are valid (file exist?)
            
    Dim wbInput As Workbook
    Set wbInput = Workbooks.Open(wbControl.Cells(6, 2).Value2) 'Input path in cell B6
    
    Dim wbOutput As Workbook
    Set wbOutput = Workbooks.Open(wbControl.Cells(6, 5).Value2) 'Output path in cell E6
    
    Dim ws As Worksheet
    For Each ws In wbInput.Worksheets
        'Do whatever you want in here
    Next ws
    
    'Remember to close the workbook if not needed later
End Sub

Remove this line from your inputs sub (and also similar one for selecting the output file)

Set wb1 = Workbooks.Open(fDialog.SelectedItems(1))

user_2233308 · Answer

Please, use the next way:

Declare public variables for both necessary workbooks, on top of the standard module:

Public wb1 As Workbook, wb2 As Workbook

Copy the next sub in a standard module, to open and Set the necessary workbooks:

Sub SetWorbooks()
Dim fdialog As FileDialog, i As Long
Set fdialog = Application.FileDialog(msoFileDialogFilePicker)

 For i = 1 To 2
    fdialog.Title = "Please, select """ & IIf(i = 1, "Input", "Output") & """ file"
    
     If fdialog.Show = -1 Then
        If Right(fdialog.SelectedItems(1), 5) = ".xlsx" Or Right(fdialog.SelectedItems(1), 4) = ".xls" Then
            If i = 1 Then
                Set wb1 = Workbooks.Open(fdialog.SelectedItems(1))
            Else
                Set wb2 = Workbooks.Open(fdialog.SelectedItems(1))
            End If
        Else
            MsgBox "Please select an excel file (xls, xlsx)!"
            Exit Sub
        End If
     Else
           MsgBox "You should select an Excel file...": Exit Sub
     End If
 Next i
End Sub

Then use them in this way:

Sub Mystack()
Dim ws As Worksheet

If wb1 Is Nothing Then SetWorbooks 'for cases when having an error and the wb(s) reference have been lost.
 For Each ws In wb1.Worksheets
  '---here is the code for copying data---
 Next
End Sub

Use the same workbook in multiple modules

2 Answers