From Access VBA I need to do things to Excel window open from within a SUB function

What is good practice to call an open Excel workbook (from Access) so I can manipulate it (Copy tab, add rows, remove blanks)?

Code:

---

Sub PopulateExcel(strName, strNameID, strPractice, strRole, strServiceType, strFileName)
              Dim MySheetName As String
              Dim MyXL As Object
'              Set MyXL = excel_App.Workbooks.c


        'copy Sheet1
              Set MyXL = AppActivate.Workbooks(strFileName).Windows(1).Caption
              Select Case strRole
                Case "CRM"
                    Debug.Print "Making CRM tab"
                'open Excel file as defined in CompiledDirectory
                    'set focus on new EXCEL filename
                  MyXL.Sheets(1).copy after:=MyXL.worksheets(MyXL.worksheets.Count)  'copy TAMPLATE tab and place it after all sheets
                  MySheetName = strNameID & "-" & strName                                          'rename new tab
                    Debug.Print "New Tab Name = '" & MySheetName & "'"
                  ActiveSheet.Name = MySheetName                                                      'activate new sheet
            'begin populating tab with custom information pertaining to Associate
                  MyXL.ActiveSheet.range("B5") = strPractice & " " & strRole & " Assessment Form"

---

Let me know if you need to know more. My apologies for the possible randomness of the code, I am learning how to go from Access VBA, call to create an Excel workbook, copy the one tab (called TEMPLATE) to last worksheet (because there will be more sheets as it goes). Hence I found I can make a sub to do the Excel Manipulation. The catch is I am uncertain what is carrying forward from Function vs needing to re-declare variables, etc.

Thanks!
Rob

Sub PopulateExcel is going to get really big if you have all that code in every Case statement, plus it will be difficult to troubleshoot, maintain, and modify your code.
Instead, write a different sub to handle each Role and call those subs in the Select Case

Code:

---

Case "CRM": MakeCRM
Case Other: MakeOther
Case YetOther: MakeYetOther
Case Etc: Make Etc
'
'
End Sub

Sub MakeCRM()
CRM Code here
End Sub

Small Subs and Functions used by all Role Making Subs here

---

Avoid the use of Sheets(numerical Index), Instead use the Tab Name as the index... Sheets("Template"). Sometimes ya gotta use Magic number, but use them as soon as possible

Code:

---


Dim Sht As Worksheet
Sheets("Template").Copy After:Sheets(Sheets.Count)
Set Sht =Sheets(Sheets.Count)
Sht.Name = MySheetName

'THereafter use the Sht Object Variable
Sht.Range("A1") = 42

---

Quote:

---

uncertain what is carrying forward from Function vs needing to re-declare variables,

---

Function can assign values to external variables

Code:

---

Sub A()
X = Make4
End SUb
'--------------------------------------------
Function Make4() As Long
Dim X
X = 2 + 2
Make4 = X
End Function

---

Subs can set Module and Global scoped Variables

Code:

---


Dim A as String
Private B As Long
Public C As Date
'--------------------------------
Sub Test()
A = "rutyeoi"
B = 42
C = Now
End Sub

---

Subs and Functions can use external variables

Code:

---


Sub ShowAll()
MsgBox A
MsgBox B
MsgBox C
End Sub
'----------------------------------------
Function BeSquared() As Double
BeSquared = B^2
End Function

---

Hi Sam, that's awesome help! However what I am asking is I have a primary function in MS Access that has Excel defined so I could do some things with it. here is a sample of what I did in the primary function:

Code:

---

              Dim MyXL As Object
            Set MyXL = CreateObject("Excel.Application")
              With MyXL
                .Application.Visible = True
                .Workbooks.Open CompiledDirectory
              End With

---

I need to minimize my code by making a sub function that'll be called within a loop (vs typing it many times within the main function). I feel the sub function lost or does not have the definitions carried forward from primary function to work with the open Excel file. So I am thinking I need to define Excel as an object as I did in the primary function (correct me if I am wrong). In the sub function I will be manipulating the Excel file by making a tab, inserting data, deleting rows and running a query to populate a range. This will happen x number of times based on the Loop parameters.

** - am I saying it correctly (sub function)? I have a primary function that's calling a smaller function to do an action while within a loop.


Thanks a bunch!
Rob

Quote:

---

am I saying it correctly (sub function)?

---

"Subs" Are Procedures that usually operate on an external Object like an Application, Workbook, Worksheet, Control, Database, Etc.

"Event Subs" Are Procedures that are triggered by an Event such as WorkBook_Open, Selection_Change, AutoOpen, etc

Functions are Procedures that usually operate on only internal things, and usually RFeturn a Value derived from that operation, Which is why Function Declaration USually have a Type statement after the Input parameters. [Function Whatever(Inputs) As "Type Statement"]

Macros are Recorded Sub type Procedures that the Programmer has yet to clean out all extraneous lines.


Sample loop calling a sub that uses a function

Code:

---


For i = 1 to 10
  SubProcedure Array1(i), Array2(i) ' "SubProcedure" is the name of the Procedure called
Next

'-------------------------------------------------------------------------------------------
Private Sub SubProcedure(Input1 As Long, Input2 As Long)

Range(Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = FunctionProcedure(Input1, Input2)

'Inputs parameters to a Function that returns a value must be in Parentheses)
'If you write a Function Procedure that does NOT return a value, Don't use parentheses.
'Sub Procedures never return a value, so never get called with parentheses.
End Sub

'-------------------------------------------------------------------------------
Function FunctionProcedure(Parameter1 As Long, Parameter2 As Long) As Double

'Assign Function Procedure the Value to be returned
FunctionProcedure = Parameter1 / Parameter2

'Note the Function Type does not have to be the same as the input types
End Function

---

Inputs and Parameters (different viewpoint of the same thing,) must be in the same order, unless you assign inputs by Parameter name

Code:

---


X = Function(Input1, input2)
'is the same as
X = Function(Parameter2:=Input2, Parameter1:=Input1

---

Sam, I feel I am lost in what a function or sub or whatever are called...

What I am doing is building code that is going into a Form (Access Class Objects) VBA. That code in order to not have it be LENGTHY with repetition lines, I made a loop that will call code that I store in the Modules (Module1) which I am calling sub function. When I leave the Form VBA code and begin using the Module1 code I want to continue using the Excel sheet I have open. But with the Module1 coding I am not figuring out how to make the EXCEL file (open on my screen) to be active so I can manipulate (Populate, Add rows, Delete rows, copy Tabs, rename tabs, etc). I have code I cut from the Form VBA, which worked there, but now it doesn't in the Module1 code. I think I need to redefine the Excel.Application variable? Looking for a source of learning on how to do this online would be helpful as well.

Sam, Sorry for taking your time on something that seems derailed in definitions rather than from it's intended question.

Sam, I have given up on using the Module1 and keeping all code within the Form. This works and I'll deal with having more code there unless we can come up with a way that I can continue manipulating the open excel file by using whatever code is necessary in the called function that lives outside the FORM Class Objects area.

Sorry, I was misunderstanding the question.


Module1 Code

Code:

---

Public MyXL as As Excel.Application
Public Wkbk As Object
Public WkSht As Object

---

Form Code

Code:

---

Sub Whatever()
Set MyXL = CreateObject("Excel.Application")
Set Wkbk = MyXL.Workbooks.Open ThisWorkbook.FullName, ReadOnly:=False
Set WkSht = WkBk.Sheets("MyWorkSheet")

---

Module1 code can then use MyXL, Wkbk, and Wksht in any procedure. For that matter any procedure anywhere can also use them.