Jump to content

Recommended Posts

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

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

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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

Link to post
Share on other sites
  • 2 weeks later...

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

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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 '='

Link to post
Share on other sites

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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
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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...