Jump to content

Recommended Posts

Hello guys, 

First of all, this is my first post. Always been a lurker over here. So sorry if there are any mistakes.

Right now Im stuck, trying to format the cells in my .xlsx. All i want to do is, check how many columns the file has and create a table with the first entries as headlines.

My Script creates a .csv file and converts it into a .xlsx right afterwards. The formatting is all that is missing to make me happy. 

Would be glad if anyone could help me!

Spoiler

#include <Date.au3>
#include <GuiConstantsEx.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>


$GUIexcel = GUICreate("AD-User to Excel", 400, 260)
$SaveLabel = GUICtrlCreateLabel( "Speicherpfad festlegen:", 20, 5)
$Browse = GUICtrlCreateButton( "Browse", 20, 22)
$domain = RegRead("HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters","Domain")
$SavePath = "C:\users\"&@UserName&"\Desktop\"&$domain&"_User.csv"
$SavePathLabel = GUICtrlCreateInput($SavePath, 70, 24, 310, 20)
$ProgressBar = GUICtrlCreateProgress(20, 55, 360, 40)
$GoButton = GUICtrlcreateButton( "Datei erstellen", 145, 210, 110, 40)
$xlsxButton = GUICtrlcreateButton( "CSV to XLSX", 20, 210, 110, 40)
$VersionLabel = GUICtrlCreateLabel("Version 1.2", 340, 240)
$UsernameBox = GUICtrlCreateCheckbox("Username", 20, 100)
$VollstaendigerNameBox = GUICtrlCreateCheckbox("Nachname, Vorname", 20, 120)
$LetzteAnmeldungBox = GUICtrlCreateCheckbox("Letzte Anmeldung", 20, 140)
$EmailBox = GUICtrlCreateCheckbox("E-Mail", 200, 140)
$BeschreibungBox = GUICtrlCreateCheckbox("Beschreibung", 200, 100)
$TelefonBox = GUICtrlCreateCheckbox("Telefonnummer", 200, 120)
GUISetState(@SW_SHOW)
WinSetState($GUIexcel, "", @SW_SHOW)
GUICtrlSetState($UsernameBox, $GUI_CHECKED)
GUICtrlSetState($VollstaendigerNameBox, $GUI_CHECKED)
GUICtrlSetState($LetzteAnmeldungBox, $GUI_CHECKED)
GUICtrlSetState($xlsxButton, $GUI_disable)

While 1
   Switch GUIGetMsg()
   Case $GUI_EVENT_CLOSE
         GUIDelete($GUIexcel)
         ExitLoop
      ;Browse Button
      Case $Browse
         $NewSavePath = FileSaveDialog("Speicherort wählen", @DesktopDir, "CSV-Datei (*.csv)", 2, $domain & "_User.csv")
         If $NewSavePath = "" Then
         Sleep(100)
         Else
         GUICtrlSetData($SavePathlabel, $NewSavePath)
         EndIf

   ;Go Button
   Case $GoButton

         If GUICtrlRead($UsernameBox) = $GUI_CHECKED Then
               $usernamep = " SamAccountName,"
            Else
               $usernamep = ""
            EndIf


         If  GUICtrlRead($VollstaendigerNameBox) = $GUI_CHECKED Then
               $displaynamep = " DisplayName, "
            Else
               $displaynamep = ""
            EndIf

         If  GUICtrlRead($LetzteAnmeldungBox) = $GUI_CHECKED Then
               $lastlogondatep = " lastlogondate, "
            Else
               $lastlogondatep = ""
            EndIf

         If GUICtrlRead($EmailBox) = $GUI_CHECKED Then
               $emailp = " mail, "
            Else
               $emailp = ""
            EndIf

         If GUICtrlRead($BeschreibungBox) = $GUI_CHECKED Then
               $descriptionp = " description, "
            Else
               $descriptionp = ""
            EndIf

         If GUICtrlRead($TelefonBox) = $GUI_CHECKED Then
               $officephonep = " OfficePhone, "
            Else
               $officephonep = ""
         EndIf

         $properties = $usernamep & $displaynamep & $lastlogondatep & $descriptionp  & $emailp  & $officephonep
         $finalproperties = StringTrimRight($properties, 2)


         GUICtrlSetState($GoButton,$GUI_disable)
         GUICtrlSetData($GoButton, "Datei wird erstellt...")
         $SavePath = GUICtrlRead($SavePathlabel)
         If $SavePath = "" Then $SavePath = "C:\users\"&@UserName&"\Desktop\"&$domain&"_User.csv"
         GUICtrlSetData($Progressbar, 25)
         ;Powershell Befehl

         $excelbefehl = "get-aduser -filter * -properties " & $finalproperties & "| Select-Object " & $finalproperties & " | Export-Csv -Path " & $SavePath & " -Delimiter ';' -Encoding Default -NoTypeInformation"
         RunWait("powershell.exe " & $excelbefehl, "", @SW_HIDE)
;~          RunWait("powershell.exe " & $excelbefehl, "")
;~          MsgBox(0, "",  $excelbefehl)
         GUICtrlSetData($ProgressBar, 100)
         MsgBox(0, "", "Datei wurde erstellt!" & @CRLF & $SavePath)
         GUICtrlSetState($xlsxButton, $GUI_enable)
         GUICtrlSetData($ProgressBar, 0)
         GUICtrlSetState($GoButton,$GUI_enable)
         GUICtrlSetData($GoButton, "Datei erstellen")

      Case $xlsxButton
         $filex = GUICtrlRead($SavePathLabel)

      If FileExists($filex) Then
         Local $oExcel = _Excel_Open(False)
         Local $oWorkbook = _Excel_BookOpen($oExcel, $SavePath)
         $xlsxPath = FileSaveDialog("Speicheort festlegen", @DesktopDir, "XLSX-Datei (*.xlsx)", 2,  $domain & "_User.xlsx")
         _Excel_BookSaveAs($oWorkbook, $xlsxPath)
         _Excel_BookClose($oWorkbook)
         MsgBox(0, "", "xlsx-Datei wurde erstellt!" & @CRLF & $xlsxPath)
      Else
         MsgBox(0, "", "Keine Datei ausgewählt")
         EndIf
      EndSwitch
      WEnd

Thanks.

Link to post
Share on other sites

To get the best chance of getting help in this forum, I would suggest a few things :

1- Make your code runable.  The code you provided is obviously unrunable for any of us.

2- Make your code as small as possible.  The code you provided is way too long and contains many steps that are useless to us and to your issue.

3- Provide files example if needed.

4- Use this tool when you post code.

5- Show exactly what is the problem (by using comments inside the code for example)

Good luck.

Edited by Nine
Link to post
Share on other sites

The wiki has a section about Excel automation. Even about formatting.
If you need further assistance, just post here.

Creating tables is not covered by the Excel UDF that comes with AutoIt.
But you could record a macro while you create your table. This can then easily be translated to AutoIt.

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

First of all, thanks for the answers.

@water, I already created a macro in excel to see what it looks like in vba. Sadly, i have no idea how to translate the macro code to autoit. And im almost sure that there's no way to translate those functions.

It's not that big of a deal anyways. Automated formatting would be great, but is not a necessarity.

Link to post
Share on other sites

Translating VBA to AutoIt is no rocket sience :)
Just post the macro and we will have a look at it.

Formatting should be no problem.
What do you want to achieve: Background/font color, row/column size, number format ...?

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

German + Turkish I think

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's indeed a turkish surname. But it's not my acutal name. When i got kicked out of university. The de-regestistration was sent to me with this name on the letter. Since then, I use it as an internet identity😅

To come back to the topic.

Sub Makro1()
'
' Makro1 Makro
'

'
    Range("A1:C4972").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$4972"), , xlYes).Name = _
        "Tabelle1"
    Range("Tabelle1[#All]").Select
    ActiveSheet.ListObjects("Tabelle1").TableStyle = "TableStyleLight11"
    ActiveWorkbook.Worksheets("kvnbw.intra_User").ListObjects("Tabelle1").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("kvnbw.intra_User").ListObjects("Tabelle1").Sort. _
        SortFields.Add Key:=Range("Tabelle1[[#All],[SamAccountName]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("kvnbw.intra_User").ListObjects("Tabelle1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This is what Excel gives me. I marked the area, created a table and sorted A:A alphabetically.

Is there any chancen you can help me translating it?

Link to post
Share on other sites

This works for me in Excel VBA. I will leave converting to AutoIt to the experts here.

Assuming the Header you are looking for is "SamAccountName"

Private Sub Table_Test()

	' Declare variables
	Dim wb As Workbook
    Dim ws As Worksheet
    Dim rRange As Range
    Dim TableName As ListObject
    
    ' Set variables for Workbook, Worksheet and Data Range
    Set wb = Application.Workbooks("YourWorkbookNameHere.xlsm")
    Set ws = wb.Sheets("WorkSheetNameHere")
    Set rRange = ws.Range("A1").CurrentRegion
    
    ' Check if Data Range is already a table
    If rRange.ListObject Is Nothing Then
    	' If not a table, add range as new table
        Set TableName = ws.ListObjects.Add(xlSrcRange, rRange, , xlYes, , "TableStyleLight11")
    Else
    	' If already a table, set variable for it
        Set TableName = rRange.ListObject
    End If
    
    ' Sort all, with column header = "SamAccountName"
    With TableName.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range(TableName.Name & "[[#All],[SamAccountName]]"), _ 
        					SortOn:=xlSortOnValues, _ 
        					Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Clean up, set variables
    Set TableName = Nothing
    Set rRange = Nothing
    Set wb = Nothing
   
End Sub

Edit2: Added VBA style comment lines

Edited by GokAy
Changed the WB/WS/Header names
Link to post
Share on other sites

Thought about that too.

1 hour ago, GokAy said:

Wanted to add, if it's too much bother to convert, maybe you can:

- Change the Excel file to xlsm (Macro Enabled)

- Insert a module

- Copy paste this code

- Change "Private" to "Public" at sub declaration (1st line)

- Change sub name to whatever (1st line)

- and call this sub from AutoIt

Thought about that too. But that's quite laborious.

Especially because i want to be able to use tables in excel in future scripts aswell. Was really hoping for a UDF command or smth.

Sad life

Link to post
Share on other sites

First try:

#include <Excel.au3>

Global $oExcel = _Excel_Open()

Func Table_Test()

    ; Define variables
    Local $oWorkbook, $oWorksheet, $oRange, $oTable
    Local $xlSrcRange = 1 ; https://docs.microsoft.com/en-us/office/vba/api/excel.xllistobjectsourcetype
    Local $xlPinYin = 1 ; Phonetic Chinese sort order for characters (default). https://docs.microsoft.com/en-us/office/vba/api/excel.xlsortmethod

    ; Set variables for Workbook, Worksheet and Data Range
    $oWorkbook = $oExcel.Workbooks("YourWorkbookNameHere.xlsm")
    $oWorksheet = $oWorkbook.Sheets("WorkSheetNameHere")
    $oRange = $oWorksheet.Range("A1").CurrentRegion  ; ??

    ; Check if Data Range is already a table
    If Not IsObj($oRange.ListObject) Then
        ; If not a table, add range as new table
        $oTable = $oWorksheet.ListObjects.Add($xlSrcRange, $oRange, Default, $xlYes, Default, "TableStyleLight11")
    Else
        ; If already a table, set variable for it
        $oTable = $oRange.ListObject
    EndIf

    ; Sort all, with column header = "SamAccountName"
    With $oTable.Sort
        .SortFields.Clear
        .SortFields.Add($oWorksheet.Range($oTable.Name & "[[#All],[SamAccountName]]"), _
                $xlSortOnValues, $xlAscending, Default, $xlSortNormal)
        .Header = $xlYes
        .MatchCase = False
        .Orientation = Default ; $xlTopToBottom. Could not find this value
        .SortMethod = $xlPinYin
        .Apply
    EndWith

    ; Clean up, set variables
    $oWorkbook = 0
    $oWorksheet = 0
    $oRange = 0
    $oTable = 0

EndFunc   ;==>Table_Test

 

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 depends I believe.

If you will be around the file, we can do this.

- Create a template.xlsm file, with the above sub but change it so it will work regardless of filename. Maybe add parameters for selecting options like sort column and whatnot. Then in AutoIt, you would first make a copy of the template.xlsm, and open that, copy stuff, and run the macro with preferred options. And save the copied file as whatever or overwrite an already created one. A lot of options  available.

- Will be more robust too.

I can help with VBA part if you can not come up with a better solution.

 

Edit: I see @water has provided the AutoIt part. Let's see how that goes.

Edited by GokAy
Saw @water's response
Link to post
Share on other sites

I'm not really into vba. Will there we a way for the macro to check how many columns the sheet has? As the user can decide how much information he wants to generate. 

If it's possible. It would a great alternative! And im going to test it out.

Edited by HerrAltunpek
Link to post
Share on other sites

Everything you can do with AutoIt you can do with VBA as well - both use the same API to interact with Excel.

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

The script I posted already calculates all columns (that are tied together at least with a non blank cell).

You can check for yourself, click any cell that you want to see the behavior and press CTRL + * (* can be on a upper case position,, add shift to it if so)

And you can changed the column head to sort with if you assign a variable to it.

Edited by GokAy
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...