Skip to content

README.en.md

Latest commit

 

History

History
152 lines (117 loc) · 6.59 KB

README.en.md

File metadata and controls

152 lines (117 loc) · 6.59 KB
Raw

vbscriptSampleCode

한국어 | English

Automation scripts written while building SAP integrations on an AAE (Advanced Adapter Engine) setup: driving SAP GUI through its scripting interface, editing the Excel exports that come out of it, and the small string and file helpers those two need.

Originally published in 2019 as a set of loose snippets to get feedback on. This revision keeps the same scenarios but fixes the defects that stopped several of them from running, and adds a PowerShell port alongside — see Why PowerShell as well.

Layout

src/vbs/   VBScript, runs on cscript.exe (Windows Script Host)
src/ps/    PowerShell 5.1+ port of the same scenarios
docs/      Review notes and the VBScript -> PowerShell mapping

Scripts

SAP GUI

Script What it does
src/vbs/SapConnection.vbs Attaches to a running SAP GUI, opens a connection, logs on
src/vbs/SapTableInput.vbs Fills consecutive rows of a GuiTableControl from a delimited string
src/ps/Connect-SapGui.ps1 Same as SapConnection.vbs, takes a PSCredential, emits the session
src/ps/Set-SapTableColumn.ps1 Same as SapTableInput.vbs, accepts pipeline input

Both require SAP GUI for Windows with scripting enabled on the client (Options > Accessibility & Scripting) and on the server (profile parameter sapgui/user_scripting = TRUE).

set SAP_PASSWORD=********
cscript //nologo src\vbs\SapConnection.vbs "/H/sap.example.com/S/3200" KO USER01
$session = .\src\ps\Connect-SapGui.ps1 -ConnectionString '/H/sap.example.com/S/3200' -Language KO
'1000','1010','1020' | .\src\ps\Set-SapTableColumn.ps1 -Session $session -TableId 'wnd[1]/usr/tblX' -ConfirmPopup

Excel

Script What it does
src/vbs/ExcelOpen.vbs Opens a workbook in a visible Excel instance and leaves it open
src/vbs/ReservationExcelEdit.vbs Reservation export cleanup: drop column C, stamp the delivery code, rename 생산버전 to STATUS
src/ps/Edit-ReservationWorkbook.ps1 Same cleanup, with the header names as parameters and -WhatIf support 
cscript //nologo src\vbs\ExcelOpen.vbs "C:\data\reservation.xlsx"
cscript //nologo src\vbs\ReservationExcelEdit.vbs reservation.xlsx 100023
.\src\ps\Edit-ReservationWorkbook.ps1 -WorkbookName reservation.xlsx -SupplierCode 100023 -WhatIf

Both attach to an Excel instance that is already running and never call Quit, because the scripts were designed to be chained.

Helpers

Script What it does
src/vbs/DateFormatCustom.vbs m/d/y [h:m:s] → yyyyMMdd, zero padded
src/vbs/Deduplication.vbs Removes duplicate tokens from a delimited string, first occurrence wins
src/vbs/RegexCheck.vbs StripPattern / MatchesPattern
src/vbs/FileLog.vbs AppendLine / WriteAllText / ReadAllText / ReadLines
src/vbs/BackupFolder.vbs + .cmd Copies a folder into <root>\yyyy_MM_dd
src/ps/ConvertTo-SapDate.ps1 Date normalisation, pipeline friendly
src/ps/Backup-Folder.ps1 Folder snapshot with a -Retain sliding window

Dedup, regex and file logging have no PowerShell counterpart on purpose — they are one-liners there. See docs/vbscript-to-powershell.en.md.

The VBScript helper files declare functions only. Windows Script Host has no include, so either paste them into the calling script or wrap both in a .wsf job:

<job>
  <script language="VBScript" src="DateFormatCustom.vbs"/>
  <script language="VBScript">
    WScript.Echo FormatCustomDate("6/21/2019 13:05:00", "")
  </script>
</job>

Encoding and line endings

Handled by .gitattributes; worth knowing if you edit these files outside git.

  • cscript.exe reads .vbs using the system code page, not UTF-8. A script containing Korean literals must reach disk as UTF-16LE with a BOM; BOM-less UTF-8 turns the literals into mojibake and column lookups silently fail. src/vbs/ReservationExcelEdit.vbs is stored as UTF-8 in the repository and re-encoded on checkout via working-tree-encoding (git ≥ 2.21).
  • Windows PowerShell 5.1 assumes the ANSI code page for BOM-less files, so .ps1 files with non-ASCII content are saved as UTF-8 with a BOM.
  • .vbs, .cmd, .bat and .ps1 check out with CRLF.

Security notes

  • Never pass a password as a command line argument. argv is readable by any process on the machine (Task Manager's Command line column, WMI Win32_Process, Sysmon event ID 1) and is captured by most EDR agents. SapConnection.vbs reads SAP_PASSWORD from the environment; Connect-SapGui.ps1 takes a PSCredential.
  • SAP GUI scripting drives the session as the logged-on user, with that user's full authorisation. Treat these scripts as privileged.
  • The scripts here have no credentials or hostnames baked in. Keep it that way.

Why PowerShell as well

Microsoft deprecated VBScript in 2023. In Windows 11 24H2 it became a Feature on Demand rather than a built-in component; in a later phase the feature is planned to ship disabled by default, and to be removed after that.

SAP GUI's scripting engine itself is language agnostic and still works from PowerShell, VBA or anything else that can talk COM — the piece with an expiry date is the cscript.exe host, not SAP. The src/ps/ scripts are the migration target; src/vbs/ is kept so existing schedules keep working.

Requirements
OS Windows (Windows Script Host for src/vbs)
PowerShell 5.1, or PowerShell 7+ with -UseWindowsPowerShell for the COM pieces
SAP SAP GUI for Windows, scripting enabled client and server side
Excel Microsoft Excel (the Excel scripts drive it over COM)

Documentation

License

MIT