none
How my use can run my VBA in the Excle at newwork from their Excel as needed? RRS feed

  • Question

  • Hi,

    I created the VBA code under one button, Button_Click(),  to do some tasks in the Excel file. I put it at network and as a xlsm file.  My user needs run use the VBA code monthly/weekly to treat their own Excel file data.

    How it can be done?

    Thanks!


    • Edited by Daphne12 Tuesday, April 18, 2017 6:58 PM
    Tuesday, April 18, 2017 5:47 PM

All replies

  • Hello,

    You could use Application.Run to call macros in another workbooks. You could also save your current file into Excel add-in (.xlam) or Excel 97-2003 add-in (.xla) and distribute the add-in to your users. Please visit Add or remove add-ins.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 19, 2017 8:56 AM
    Moderator
  • Thanks so much! Celeste!

    I got saved my xlsx file to xlam. My question:

    1. How I do distribute the add-in? does my user need to install Add in as well?

    2. How my user import my VBA code from my xlam file?

    3. Also, should I use a auto run macro instead of the current design?: All code are contained in the Button Click_

    How I make my VBA code to to a auto run macro? 



    • Edited by Daphne12 Wednesday, April 19, 2017 5:04 PM
    Wednesday, April 19, 2017 12:40 PM
  • Hello,

    To distribute the add-in, we just need to share the file to users. You could find your add-ins in %userprofile%\AppData\Roaming\Microsoft\AddIns

    To use the add-in, we need to load it from Developer tab -> Add-ins group -> Excel Add-ins.

    To import your VBA code, I think users need to copy it to their own module.

    To run your macro, we need to use Application.Run. You could visit http://stackoverflow.com/questions/2814116/running-a-macro-from-an-add-in for detail information and options.

    To create a auto run macro, please visit Automatically run a macro when opening a workbook

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 21, 2017 7:07 AM
    Moderator
  • After add-in my xlam file to the Test User Excel file.  All codes are in the VBA code section of the Test file.  Click run, Didn't get data treated in the test file at all. I guess it still treat the data in that original xlam file.   ???  Thanks for any advice?

    Friday, April 21, 2017 1:14 PM
  • Hi,

    I need run VBA from other Excel file as needed. I add_in to get VBA code and can't get treat data in the current Excel after run VBA.

    What I miss?

    Thanks!

    Monday, April 24, 2017 1:48 PM
  • Hello,

    It depends on how you write the code and how you get the objects. The objects might refer to the original sheets or workbook in your add-in.

    For example, I create a new workbook, insert "TEST" in the A1 and write the following subroutines to get the value of Range("A1"). In current file, they all return "TEST". Then save it as an Excel add-in.

    Sub test()
    MsgBox ActiveSheet.Range("A1").Value
    End Sub
    Sub test1()
    MsgBox Sheets(1).Range("A1").Value
    End Sub
    
    Sub test2()
    MsgBox Sheet1.Range("A1").Value
    End Sub
    Sub test3()
    MsgBox ThisWorkbook.Sheets(1).Range("A1").Value
    End Sub

    Then I create a new workbook and install the add-in.

    Insert "ABC" in the A1 and call its subroutines. 

    test() and test1() return "ABC".

    test2() and test3() return "TEST".

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 25, 2017 7:48 AM
    Moderator
  • Hello,

    I suggest you see the reply in

    https://social.msdn.microsoft.com/Forums/office/en-US/c13f6969-7abb-4e28-ade8-baf52f6006e0/how-my-use-can-run-my-vba-in-the-excle-at-newwork-from-their-excel-as-needed?forum=exceldev

    You need to modify the method or parameters you specify the object.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 28, 2017 2:39 AM
    Moderator
  • Thanks! Celeste,

    Not very clear your solution yet.

    I am looking for:

    I have the Excel with one icon is added on the tool bar. It does 1. create a new worksheet, 2. load data from other sheet, 3. Inset the new column using VLook formula.

    My user have their updated Excel file monthly, need treat their data using my VBA save as xlam file.

    Hope I figure out the way to allow my user way the can run it by themselves instead of I do my side.



    • Edited by Daphne1212 Friday, April 28, 2017 1:35 PM
    Friday, April 28, 2017 12:49 PM
  • Hello,

    I suggest you share your detail step and your code here.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 1, 2017 6:23 AM
    Moderator
  • rivate Sub InsertAssignmentColumn()

    '  Sub InsertAssignmentColumn()
    'File copy and overwrite the file if the file exist
    FileCopy "\Network-b379\FISCAL\Test.xlsx", "C:\Temp\Test.xlsx"
     
        'Define Object for all the Workbooks and worksheet
         Dim x As Workbook
         Dim y As Workbook
         Dim s As Worksheet
         Dim t As Worksheet
         Dim u As Worksheet
         Dim r As Range
         Dim cell As Range
         Dim Ret
      
        Set x = Workbooks.Open("C:\Temp\Test.xlsx")
        Set y = ThisWorkbook
       
        'Convert all Number text data to Number in the Column N. will extended the row number.
        Set s = x.Worksheets("Excel_Destination")
         With s.Range("N:N")
             .NumberFormat = "0"
             .Value = .Value
         End With
        
         'Delete sheet MySheet if it already exists
         Application.DisplayAlerts = False
         On Error Resume Next
         y.Worksheets("MySheet").Delete
         On Error GoTo 0
         Application.DisplayAlerts = True
        
         On Error GoTo ErrHandler
         Set t = y.Worksheets.Add(After:=y.Worksheets(1))
         t.Name = "MySheet"
         s.Columns(14).Copy Destination:=t.Columns(1) 'copy ID column to MySheet's column 1
         s.Columns(10).Copy Destination:=t.Columns(2) 'copy ID Assignment to MySheet's column 2
     
        
        'Close X without save anything
         x.Close False
        
        'Insert new column in column 3 as the Assignment column.
         Set u = y.Worksheets("OHMReport")
     
         'Delete the Assignment column if it already exists
         If u.Range("C5").Value = "Assignment" Then u.Columns(3).Delete
         
        'Insert the new column in third column as Assignment column
          u.Columns(3).Insert
          u.Columns(3).HorizontalAlignment = xlLeft
          u.Columns(3).ColumnWidth = 40
          u.Range("C5").Value = "Assignment"
          u.Range("C5").Font.Bold = True
         
       'Convert all Number text data to Number in the Column B.
           With u.Range(u.Range("B1"), u.Cells(u.Rows.Count, "B").End(xlUp))
            .NumberFormat = "0"
            .Value = .Value
          End With
          
       'VLookup to get the data in the assignment column.
       u.Range("C7:C" & u.Cells(u.Rows.Count, "B").End(xlUp).Row).Formula = "=VLOOKUP(B7,MySheet!A:B,2,FALSE)"
       
        
       'Save all the formula to value in the Assigmnet column
       With u.Range(u.Range("C1"), u.Cells(u.Rows.Count, "C").End(xlUp))
        .Value = .Value
       End With
     
       MsgBox "Task Completed"
       Exit Sub

    ErrHandler:
         MsgBox "There was an error"
    End Sub

    Monday, May 1, 2017 6:08 PM
  • Hello,
    ThisWorkbook is the workbook where the code written or stored and ActiveWorkbook is current workbook when you run the macro. I think you need to use ActiveWorkbook and set y before x because activeworkbook changed after opening x. Besides, access modifier should be changed to Public so the sub could be accessed. After your user loading it, your user could also create one shortcut on tool bar to call the method.
    Like this

    Set y = ActiveWorkbook
      Set x = Workbooks.Open("C:\Temp\Test.xlsx")
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 2, 2017 6:29 AM
    Moderator
  • Great Help!  If I attach the file and Email to my user. so, she doesn't need create one shortcut on tool bar. instead of, cut all old data and paste new data. click the short cut get new data treated? Another question: I have there VBA Project in my VBA window. Other two(VBAProject(2017 File Name, VBAProject(Personal.XLSB) I don't need. Can I remove them before I send the file out? Many Thanks !
    Wednesday, May 3, 2017 12:38 PM
  • Yes, your macro treats current data in range so it will treat the new data. However, your users still need to create one shortcut on tool bar because the xlam file only shares VBA code but not your custom Ribbon.

    You don't need remove other VBA project because your file just includes the code in itself.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 4, 2017 5:30 AM
    Moderator