Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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
    
@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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .