Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have made a module i.e. a .bas file which does the work I want to do in Excel. I have to work with this same module in four different files everyday.

Now I want, let's say, a script which automatically link this module with the Excel file and then the macro executes all by itself.

The piece of code that I have tried till now is:

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("C:\Denied_report.csv", 0, True) 
  xlApp.Run "PERSONAL.XLSB!AllowedMacro"
  xlApp.SaveAs
  xlApp.ActiveWorkbook.Close
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 

I am using Personal file so that it is available to all Excel files and doesn't have to import it everytime by opening the Excel file and doing so!

share|improve this question

2 Answers 2

You might want to try the Workbook_open function. This will run as soon as you open Excel.

Public WithEvents App As Application

Private Sub Workbook_Open()
    'runs once per Excel session when Personal.xlsb is opened.
    Set App = Application
    MsgBox "Running Workbook_Open from Personal.xlsb"
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Running App_WorkbookOpen in " & Wb.Name
    'add code you want to run upon opening each workbook here
   '.....
End Sub

See: http://www.mrexcel.com/forum/excel-questions/562394-workbook_open-personal-xlsb-problems-need-correct-sub-procedure.html

share|improve this answer
    
If you're happy with the solution proposed - please accept the answer by ticking it, this will mark the question solved. For the details see: Accepting Answers: How does it work? –  Peter Albert Feb 24 '13 at 19:01
    
@Robert Ilbrink The above one is answer of my question. Thanks for your concern. –  Developer by Blood Feb 26 '13 at 9:34

I tried this instead and this worked:

Option Explicit

    LaunchMacro

  Sub LaunchMacro() 
      Dim xl
      Dim xlBook      
      Dim strFile


        strFile = "C:\De1.csv"
        Set xl = CreateObject("Excel.application")
        Set xlBook = xl.Workbooks.Open("C:\De.csv", 0, False ) 

        xl.Application.Visible = True

        xl.Application.run "PERSONAL.XLSB!Allowed_Macro"
        xl.DisplayAlerts = False        
        xlBook.saved = True
        xl.ActiveWorkbook.SaveAs strFile

        xl.activewindow.close
        xl.Quit

        Set xlBook = Nothing
        Set xl = Nothing

End Sub 

The only problem I am facing now is that I am whatever formatting my macro does, they don't persist after saving.

share|improve this answer
    
Please open as a new question - ideally on StackOverflow! –  Peter Albert Feb 24 '13 at 19:02
    
.CSV does not store any formatting, only the data! You need to save it as .xlsm! –  Peter Albert Feb 24 '13 at 19:05
    
@PeterAlbert Questions about Excel macros can also be asked here, they're not off topic. –  slhck Feb 24 '13 at 19:58
1  
@slhck: Ok, good to know. Though I guess just from looking at the audience, SO will most likely have more VBA experts look at it... –  Peter Albert Feb 24 '13 at 20:08
    
@PeterAlbert I tried renaming it to .xlsm while saving but then that .xlsm file throws an error and doesn't open. Any tricks? –  Developer by Blood Feb 26 '13 at 9:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.