Create Tables in Excel

[HerrAltunpek]

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.

[Nine]

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 October 28, 2020 by Nine

[water]

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.

[HerrAltunpek]

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.

[water]

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

[GokAy]

@water beat me to it.

(HerrAltunpek, is that a Turkish name?)

And those recorded macros usually contain a lot of selects and with statements. Stripping them will help too. Maybe get something more dynamic in the end.

[water]

German + Turkish I think

[HerrAltunpek]

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?

[GokAy]

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 October 29, 2020 by GokAy
Changed the WB/WS/Header names

[GokAy]

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

[HerrAltunpek]

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

[water]

First try:

expandcollapse popup

#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

 

[GokAy]

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 October 29, 2020 by GokAy
Saw @water's response

[HerrAltunpek]

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 October 30, 2020 by HerrAltunpek

[water]

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

[HerrAltunpek]

1 hour ago, water said:

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

Awesome, thanks for the quick replies!

I'll try that solution as fast is I can!

[GokAy]

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 October 30, 2020 by GokAy