VBScript to open Excel then add a VBA macro

Question

I need a VBScript to open a certain Excel Document, then when open it must add a Macro and save.

I can open the Excel document but I do not know how to open the Macro screen(Alt+F11) and then add the code and save...

Is there anyway to do this?

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = True`

'Macro Script

Sub HideRows()
    Dim cell As Range   
    For Each cell In Range("H1:W200")
        If Not isEmpty(cell) Then
            If cell.Value <> "" And cell.Value = 0 Then 
                cell.EntireRow.Hidden = True
                Columns("H").EntireColumn.Hidden = True
                Columns("I").EntireColumn.Hidden = True
                Columns("J").EntireColumn.Hidden = True
                Columns("M").EntireColumn.Hidden = True
                Columns("N").EntireColumn.Hidden = True
                Columns("O").EntireColumn.Hidden = True
                Columns("P").EntireColumn.Hidden = True
                Columns("Q").EntireColumn.Hidden = True
                Columns("S").EntireColumn.Hidden = True
                Columns("T").EntireColumn.Hidden = True
                Columns("V").EntireColumn.Hidden = True
            End If
        End If
    Next
End Sub

I strongly advise you to solve this without SendKeys, since this approach can be (unwillingly) sabotaged by the user, and also renders the screen useless for the duration. Microsoft Support or @Zam's link should help you. — Verzweifler
– Verzweifler, Commented Jul 2, 2015 at 10:50

Bond · Accepted Answer · 2015-07-02 14:07:47Z

2

Follow these steps:

Open the VBA Editor in Excel and add a new Module.
Paste your macro code into it.
Right-click the Module and choose Export....
Give it a filename and save it somewhere.
In your VBScript, add the following lines of code:
```
objWorkbook.VBProject.VBComponents.Import "/path/to/your/module.bas"
objWorkbook.Save
```
Note that, in Excel 2007+, you can't save macros in xlsx files. You'll need to use SaveAs instead and give the file an xslm extension. Or, you can use the old xls format (which is what you appear to be using from your example).

edited Jul 2, 2015 at 14:07

answered Jul 2, 2015 at 14:01

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco · Accepted Answer · 2015-07-02 13:28:46Z

You can add code programmatically by using the VBComponents object of the VBProject object. So add this behind the last line of your code:

Set objModule = objworkbook.VBProject.VBComponents.Add(1)       ' 1 = vbext_ct_StdModule

objExcel.Visible = True    ' not necessary if you close Excel anyway

theSource = ""
theSource = theSource & "Sub HideRows()" & vbCrLf
theSource = theSource & "    Dim cell As Range   " & vbCrLf
theSource = theSource & "    For Each cell In Range(""H1:W200"")" & vbCrLf
theSource = theSource & "        If Not isEmpty(cell) Then" & vbCrLf
theSource = theSource & "            If cell.Value <> """" And cell.Value = 0 Then " & vbCrLf
theSource = theSource & "                cell.EntireRow.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""H"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""I"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""J"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""M"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""N"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""O"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""P"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""Q"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""S"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""T"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "                Columns(""V"").EntireColumn.Hidden = True" & vbCrLf
theSource = theSource & "            End If" & vbCrLf
theSource = theSource & "        End If" & vbCrLf
theSource = theSource & "    Next" & vbCrLf
theSource = theSource & "End Sub" & vbCrLf

objModule.CodeModule.AddFromString theSource

'objExcel.Quit

'Set objExcel = Nothing

Ahmad · Accepted Answer · 2015-07-02 10:39:19Z

-1

This is not straight forward, but what I would do is use SendKeys function to simulate Alt+F11.

Application.SendKeys "%{F11}", True

Then using the same logic, use keystrokes to navigate to the proper window, adding a module, then paste the macro code in the right place using:

Application.SendKeys ""^V"
Application.SendKeys ""^V", True   'Incase that one above does not work

Then you could save using:

Application.SendKeys ""^S", True

You can read more about that here and here

But another way is to use a mouse and keyboard macro recorder (stand alone application that can be programmed to mimic actions). I personally have used KeyText for more than 10 years to do that sort of thing.

answered Jul 2, 2015 at 10:39

Ahmad

12.8k7 gold badges53 silver badges98 bronze badges

1 Comment

luke_t Over a year ago

Just to mention: many people regard SendKeys as too risky, and generally do not recommend using it for a business solution.

Collectives™ on Stack Overflow

VBScript to open Excel then add a VBA macro

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related