Consulting

Results 1 to 7 of 7

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

  1. #1

    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)?

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    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
    Last edited by SidCharming; 05-03-2018 at 09:53 AM.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    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.

  6. #6
    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.

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •