Executing VBAscript from Python

Question

I am in need of some help in regards to win32com.client. I have the code working as far as creating the macro from Python and using Excel but I would like this code to also run the vbascript.

Thank you guys for all of your wonderful feedback!

import pyodbc

import win32com.client as win32

xl = win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
ss = xl.Workbooks.Add()
sh = ss.ActiveSheet

xlmodule = ss.VBProject.VBComponents.Add(1)  # vbext_ct_StdModule

sCode = '''Sub Download_Standard_BOM()
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

    ConnectionString = "Provider=SQLOLEDB; Network Library=dbmssocn;Password=********;User ID=*******;Initial Catalog=**;Data Source=*************;"

    cnn.Open ConnectionString

    cnn.CommandTimeout = 900

    StrQuery = "SELECT * FROM car_search WHERE shop_id = *******"

    rst.Open StrQuery, cnn

    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub'''

xlmodule.CodeModule.AddFromString(sCode)

xlwings may help.

Logan Reed
– Logan Reed

2016-07-13 21:52:32 +00:00
Commented Jul 13, 2016 at 21:52 — Logan Reed
– Logan Reed, Commented Jul 13, 2016 at 21:52

ThunderFrame · Accepted Answer · 2016-07-13 23:07:45Z

1

You should be able to use Excel's Application.Run method:

xl.Run "Download_Standard_BOM"

EDIT If you need to refer to ADO, then you can either use late-binding, like this:

Dim cnn As Object 'ADODB.Connection
Dim rst As Object 'ADODB.Recordset

Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

Or, use early binding and add a reference to the VBA Project:

ss.VBProject.References.AddFromGuid "{2A75196C-D9EB-4129-B803-931327F72D5C}", 2, 8

edited Jul 13, 2016 at 23:07

answered Jul 13, 2016 at 21:53

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

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

3 Comments

phtnm28 Over a year ago

thank you very much that does seem to try and execute it. However, now I receive the "User-defined type not defined" which I believe is because there is an object that isn't being referenced. If you don't mind my follow up question would be how do I add references when executing from Python?

phtnm28 Over a year ago

Is there a way to call out references for my macro within Python?

phtnm28 Over a year ago

Thank you ThunderFrame for the awesome help, I'm sooo close to wrapping this up, I've called out all of my references that I need but I'm still receiving a user-defined error and debug is pointing to this piece of code. Set cn = CurrentProject.Connection Set rst = New ADODB.Recordset Dim cn As Object: Set cn = CreateObject("ADODB.Connection") Dim rst As New ADODB.Recordset Dim ConnectionString As String More specifically the ADODB.Recordset but I have 'Microsoft ActiveX Data Objects Recordset referenced....any ideas?

Collectives™ on Stack Overflow

Executing VBAscript from Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related