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

[SidCharming]

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



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

[SamT]

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


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


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


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

Function can assign values to external variables

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


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


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

[SidCharming]

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:


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

[SamT]

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


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


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

[SidCharming]

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.

[SidCharming]

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.

[SamT]

Sorry, I was misunderstanding the question.


Module1 Code

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

Form 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.