Called Procedure Pulling Objects/Variables from Calling Procedure

[appealdenied]

I am trying to write a couple of vba procedures that do the following:
1) Procedure 1 in Workbook 1 creates Workbook 2
2) Procedure 1 then Calls on Procedure 2 (also in Workbook 1). I am trying to pass through all variables. However, I'm having trouble passing a few of them.
3) Procedure 2 takes the incoming parameters and proceeds to process them and generate a report.
4) Procedure 1 saves the report.

For those that insist on a business case: I am developing a reporting worksheet that generates 2 formatted reports and 1 powerpoint presentation from two sets of (non-merged) data
My intent for the procedures that build the reports and presentation is that:
1) the workbooks are created
2) the query is executed and formatted in the new workbook
3) the workbooks and presentation are saved.

I have included below, what I think to be the necessary code to allow you to solve the problem. The error is shown in the bolded like of code.

Sub BuildL2Report_Click()
    
'''''Variable Declarations, Initializations and Instantiations
    
    'Worksheet Variables
    Dim wbStart As Workbook, wbEnd As Workbook
    Dim wsStart As Worksheet, wsEnd As Worksheet, wsVariables As Worksheet
    Dim strSupport As String, strReport As String, strVariables As String, strPath As String
    Dim thisBook As String

    strReport = "CQReport" & Year(Date) & Month(Date) & Day(Date)
    strVariables = "Variables"
    thisBook = ThisWorkbook.Name
    
    'MsgBox ThisWorkbook.FullName  'ThisWorkbook.Name
       
    Set wbStart = ActiveWorkbook
    Set wbEnd = Workbooks.Add
    ActiveWorkbook.Worksheets(1).Name = strReport
     
    Set wbStart = Application.Workbooks(thisBook)
      
    strPath = Application.DefaultFilePath
    
'''''User Interface Variables
    Dim strUsername As String, strPassword As String, strDBValue As String
    Dim maxLength As Long
    strUsername = txtBoxUserName.Value
    strPassword = txtBoxPassword.Value
    strDBValue = txtBoxDatabase.Value
    maxLength = txtBoxFieldMaxSize.Value
  
      If strUsername = "" Or strPassword = "" Then
        MsgBox "Please enter a valid User Name and Password."
        End
    End If
    
    Dim objFile As Object
    Dim strYesNo As String, strFile As String
    strFile = strReport & ".xls"
    Set objFile = CreateObject("Scripting.FileSystemObject")
    If objFile.FileExists(strFile) Then
        strYesNo = MsgBox(strReport & " already exists! Do you want to re-run the report?", vbYesNo)
        If strYesNo = vbYes Then
            Call GenerateLevel2Report_Click(wbStart, wbEnd, wsEnd, strReport, wsVariables, strUsername, strPassword, strDBValue, maxLength)
        Else
            End
        End If
    Else
        Call GenerateLevel2Report_Click(wbStart, wbEnd, wsEnd, strReport, wsVariables, strUsername, strPassword, strDBValue, maxLength)
    End If
    
    wbStart.SaveAs Filename:="CQReport" & Year(Date) & Month(Date) & Day(Date) & ".xls"

End Sub

Sub GenerateLevel2Report_Click(wbBook As Workbook, toBook As Workbook, toSheet As Worksheet, reportName As String, toVariables As Worksheet, strUsername As String, strPassword As String, strDBValue As String, maxLength As Long)
   
   'Worksheet Variables
    Dim newBook As Workbook
    Dim newSheet As Worksheet
    Dim newName As String
    
    'newName = "CQReport"
    newName = reportName
    Set newBook = ActiveWorkbook
    Set newSheet = newBook.Worksheets(newName)
    
    'Application.Calculation = xlCalculateManual            'Set Calculation Type
'n    'newSheet.Range("A1").Value = ""                       'Remove Subtotal
'n    'newSheet.Cells.Clear                                 'Clear All Cells
    'newSheet.Activate                                    'Set Report as active workheet

''''User Interface Variables
    Dim inUsername, inPassword, inDBValue As String
    Dim inMaxLength As Long
    inUsername = strUsername
    inPassword = strPassword
    inDBValue = strDBValue
    inMaxLength = inMaxLength
  
'''''SQL String Information
    Dim strSQL As String
[b]    strSQL = toBook.toSheet.Range("C2").Value  'Get SQL from desired cell [/b]
 
......

code continues

The bolded line is where this barfs. I'm sure this is a very simple fix, but I'm not a VB(A) programmer (but I'm working on it!!!).

The error message I receive is Object doesn't support this property or method.

Any suggestions?

"Life is like a purple antelope walking through a sea of dead tuna."

 

[PHV]

strSQL = toSheet.Range("C2").Value 'Get SQL from desired cell

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886