Sign in to follow this  
Followers 0
nevodj

Create Excel and Run Macro

14 posts in this topic

Hello,

i have been scouring the forums for a solution on this but cannot find anything that answers my question, althought there is hundreds of threads on the excel UDF

I am creating an excel sheet within autoit (hidden) and then i want to run the following macro on the sheet:

Sub insertlines()
Dim i As Long: i = 1
Do Until Cells(i, 1) = ""
If Cells(i, 2).Value <> Cells(i + 1, 2).Value Then
Range(Cells(i, 1), Cells(i, 16)).Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
i = i + 1
Loop
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
  
End Sub

Can anyone advise how to do this?

thanks

Share this post


Link to post
Share on other sites



You can run a workbook macro like this:

$oExcel.Run("Macro1")

Or like this if Macro1 requires parameters:

$oExcel.Run("Macro1",$date2,$FileName3)

Share this post


Link to post
Share on other sites

You can run all vbs commands from AutoIt directly.

Search the forum for Excel UDFs. They will show you how to proceed.


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

You can run a workbook macro like this:

$oExcel.Run("Macro1")

Or like this if Macro1 requires parameters:

$oExcel.Run("Macro1",$date2,$FileName3)

You can only do this if the maco is stored in the sheet. I am creating a new sheet from AutoIT

Share this post


Link to post
Share on other sites

Why do you want to run a macro? Translate the VBA macro to AutoIt and run it on the Excel sheet you just created.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Why do you want to run a macro? Translate the VBA macro to AutoIt and run it on the Excel sheet you just created.

Because i couldnt work out how to convert the VBA to AutoIT... What I was trying to do there is no functions for in the UDF.

I found a workaround... instead of creating the workbook by _ExcelWorkbookNew, I used fileinstall to place a copy of a blank workbook with my macro in it in the temporary directory, then open it using _ExcelBookOpen(@TempDir & "ExcelMacro.xlsm",0) and populate the sheet from AutoIT as desired.

Then I run the macro using $oExcel.Run("MacroName")

The macro performs the desired actions and self deletes, I save the workbook as and delete the temporary .xlsm file with FileDelete.

It works - thought I'd post incase it helped someone else...

Share this post


Link to post
Share on other sites

Why do you want to run a macro? Translate the VBA macro to AutoIt and run it on the Excel sheet you just created.

@Water

How do you translate a VBA macro into autoIT? My macro above for example.

Share this post


Link to post
Share on other sites

Or if you could help me to this it would be even better ;)

Sub Query()

Dim qt As QueryTable

sqlstring = "select * from stockitems"

connstring = "ODBC;Driver={SQL Server};Server=sqlserver;Database=testdb;Uid=sa; Pwd=sa;"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

.Refresh

End With

End Sub

Share this post


Link to post
Share on other sites

Can't test at the moment but I think this should work:

Func Query()
    Local $sqlstring = "select * from stockitems"
    Local $connstring = "ODBC;Driver={SQL Server};Server=sqlserver;Database=testdb;Uid=sa; Pwd=sa;"
    Local $oQuery = $oExcel.ActiveSheet.QueryTables.Add($connstring, $oExcel.ActiveSheet.Range("A1"), $sqlstring)
    $oQuery.Refresh()
End Func

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

It doesnt work, because you can't use ActiveSheet without the 'With' / 'End With' clause.

or i can't seem to anyway.

E.G if i do: (within excel)

ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A4"), Sql:=sqlstring)

ActiveSheet.Refresh BackgroundQuery:=False

It tells me compile error expecting '='

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

It doesnt work, because you can't use ActiveSheet without the 'With' / 'End With' clause.

Sure you can run it without "With / EndWith". That's just used to keep the commands you need to type short.

For my above example to work you need to set $oExcel using _ExcelBookNew or _ExcelBookOpen.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thank you @Water - I have it working now using your example. I had some errors in my code. ;)

Share this post


Link to post
Share on other sites

Oh the other thing - i had

BackgroundQuery:=False

after my refresh statement in VBA - how would i include that?

It doesnt seem like you can have any spaces in the command.

E.G i tried

$oQuery.Refresh BackgroundQuery:=False

Share this post


Link to post
Share on other sites

Use

$oQuery.Refresh(False)
Details can be found here.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0