21wiki
 Learn, Trade, Earn
Menu

Windows and PowerShell scripts

Ako automatizovať kopírovanie cez skripty (Windows)

Môžem použiť VBScript (Starší) alebo PowerShell (novší), prípadne Shell (Command line a najstarší)

Power Shell

Power shell run Excel VBA guide

https://www.excell-en.com/blog/2018/8/20/powershell-run-macros-copy-files-do-cool-stuff-with-power?fbclid=IwAR3zOi0GQrzXbyRK0XmyNOC6T_vaLZ5eYrV_dHJlOG_0GInkwdI-oKCE_is

Poznámka:

nefunguje spustiť macro, ide to ale cez excel sub  autoopen

Task scheduler

Ako zapnúť plánovač úloh:

Makrá si viem spúšťať cez plánovača úloh

Win + R, príkaz taskschd.msc

Start PowerShell macro

To schedule the PowerShell script, specify the following parameters:

  • Action: Start a program
  • Program\script: powershell
  • Add arguments (optional): -File [Specify the file path to the script here]

Click “OK” to save your changes.

Start AHK scripts

  • Action: Start a program
  • Program\script: link to excel application
    • Example: "C:\Program Files\AutoHotkey\AutoHotkey.exe"
    • Add "" if in address is space
  • Add arguments (optional): -File [Specify the file path to the script here]
    • " D:\(CDS)\test\MyScript.ahk"
  • Click “OK” to save your changes.

To open Excel file with excel

  • Action: Start a program
  • Program\script: link to excel application
    • Example: "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"
    • Add "" if in address is space
  • Add arguments (optional): -File [Specify the file path to the script here]
    • "D:\(CDS)\.CDS tools\test2.xlsm"
  • Click “OK” to save your changes.

Power Shell

Power shell ISE

  • Daj vyhľadať do hľadania programov (lupy) meno
    • Windows Power Shell ISE
  • Daj spustiť ho ako administrátor

!!! Windows Power Shell (Bez ISE) je iný program

  • Windows Power Shell je len príkazový riadok (konzola)
  • Vieš do konzoly dať príkazy CTRL + C a CTRL + V, ale v konzole sa ti neukladajú

Power shell copy folder command and overwrite files

Copy-Item "D:\(CDS)\.CDS local server\CDS data" -Destination "D:\(CDS)\Backup" -Force -recurse

Význam -Destination -> použije aktuálny priečinok

Význam -Force -> zabezpeči prepísanie súborov

Význam -recurse -> skopíruje všetko v priečinkoch a podpriečinkoch

Power Shell Code - Otvor Excel (a zatvor)

Code:

# start Excel

$excel = New-Object -comobject Excel.Application 

#open file

$FilePath = 'D:\(CDS)\.CDS tools\test.xlsm' #<------- Change this!!!

$workbook = $excel.Workbooks.Open($FilePath) 

#make it visible (just to check what is happening)

$excel.Visible = $true 

#access the Application object and run a macro $app = $excel.Application

#$app.Run("RUNALL") #<------- Change this!!! Nefunguje!!!

# makrá workbooku spúšťam cez auto open - Private Sub Workbook_Open()

$excel.Quit()    

#Popup box to show completion - you would remove this if using task scheduler

#$wshell = New-Object -ComObject Wscript.Shell $wshell.Popup("Operation Completed",0,"Done",0x1) 

Exit

Upload folder to FTP via PowerShell

# FTP Server Variables

$FTPHost = 'ftp://192.168.1.1/html/'

$FTPUser = 'user'

$FTPPass = 'password'

#Directory where to find pictures to upload

$UploadFolder = "D:\temp"

#Directory where to upload on FTP

$DestinationFolder = "/test"

$webclient = New-Object System.Net.WebClient

$webclient.Credentials = New-Object System.Net.NetworkCredential($FTPUser,$FTPPass) 

$SrcEntries = Get-ChildItem $UploadFolder -Recurse

$Srcfolders = $SrcEntries | Where-Object{$_.PSIsContainer}

$SrcFiles = $SrcEntries | Where-Object{!$_.PSIsContainer}

$HOSTpreferedDir = -join($FTPHost, $DestinationFolder)

# Create FTP Directory/SubDirectory If Needed - Start

foreach($folder in $Srcfolders)

{   

    $SrcFolderPath = $UploadFolder  -replace "\\","\\" -replace "\:","\:" 

    $DesFolder = $folder.Fullname -replace $SrcFolderPath,$HOSTpreferedDir

    $DesFolder = $DesFolder -replace "\\", "/"

    #$DesFolder = Join-Path $DesFolder "my_folder"

    # Write-Output $DesFolder

    try

        {

            $makeDirectory = [System.Net.WebRequest]::Create($DesFolder);

            $makeDirectory.Credentials = New-Object System.Net.NetworkCredential($FTPUser,$FTPPass);

            $makeDirectory.Method = [System.Net.WebRequestMethods+FTP]::MakeDirectory;

            $makeDirectory.GetResponse();

            #folder created successfully

        }

        catch [Net.WebException]

        {

            try {

                #if there was an error returned, check if folder already existed on server

                $checkDirectory = [System.Net.WebRequest]::Create($DesFolder);

                $checkDirectory.Credentials = New-Object System.Net.NetworkCredential($FTPUser,$FTPPass);

                $checkDirectory.Method = [System.Net.WebRequestMethods+FTP]::PrintWorkingDirectory;

                $response = $checkDirectory.GetResponse();

                #folder already exists!

            }

            catch [Net.WebException] {

                #if the folder didn't exist

            }

        }

}

# Create FTP Directory/SubDirectory If Needed - Stop

# Upload Files - Start

foreach($entry in $SrcFiles)

{

    $SrcFullname = $entry.fullname

    $SrcName = $entry.Name

    $SrcFilePath = $UploadFolder -replace "\\","\\" -replace "\:","\:"

    $DesFile = $SrcFullname -replace $SrcFilePath,$HOSTpreferedDir

    $DesFile = $DesFile -replace "\\", "/"

    # Write-Output $DesFile

    $uri = New-Object System.Uri($DesFile)

    $webclient.UploadFile($uri, $SrcFullname)

}

# Upload Files - Stop

Run AHK - AutoHotkey scripts

cd "C:\Program Files\AutoHotkey"

.\AutoHotkey.exe "D:\(CDS)\test\MyScript.ahk"

Cd -> change directory

.\program.exe -> run program in chosen directory (by cd)

"D:\(CDS)\script.ahk -> run AHK script as parameter

Powershell sendkeys commands

  • Powershell utilizes VBScript sendKeys module
  • Activating the module

#Start Visual Basic Entry Buttons

$wshell = New-Object -ComObject Wscript.Shell

[void] [System.Reflection.Assembly]::LoadWithPartialName("'Microsoft.VisualBasic");

#Add window to activate (optional) – find more about it

#[Microsoft.VisualBasic.Interaction]::AppActivate("Master8 - Power BI Desktop");

Send keys part

#Wait for 5 seconds

Start-Sleep -s 5

#press / write letters

$wshell.SendKeys("abc keys pressed");

#press CTRL + SHIFT + P

$wshell.SendKeys("^+P");

#press Alt + H (Home)

$wshell.SendKeys("%H") 

#press ALT + H and then 1 (Bold text in word)

$wshell.SendKeys("%H1") 

#press Alt + F4

$wshell.SendKeys("%{F4}");

Other characters of WshShell.SendKeys

Send one or more keystrokes to the active window as if they were typed at the keyboard. This method is similar to the VB SendKeys method.

      WshShell.SendKeys "Character_string_and/or_SendKeys"

Most ASCII characters can be represented by the character itself.
E.g, the key sequence FRED can be represented by "FRED".
Some special keys, such as the control keys, function keys etc are encoded in a string enclosed by {braces}
See the table below

Key/CharacterSendKeyDescription
~{~}Send a tilde (~)
!{!}Send an exclamation point (!)
^{^}Send a caret (^)
+{+}Send a plus sign (+)
Backspace{BACKSPACE} or {BKSP} or {BS}Send a Backspace keystroke
Break{BREAK}Send a Break keystroke
Caps Lock{CAPSLOCK}Press the Caps Lock Key (toggle on or off)
Clear{CLEAR}Clear the field
Delete{DELETE} or {DEL}Send a Delete keystroke
Insert{INSERT} or {INS}Send an Insert keystroke
Cursor control arrows{LEFT} / {RIGHT} / {UP} / {DOWN}Send a Left/Right/Up/Down Arrow
End{END}Send an End keystroke
Enter{ENTER} or ~Send an Enter keystroke
Escape{ESCAPE}Send an Esc keystroke
F1 through F16{F1} through {F16}Send a Function keystroke
Help{HELP}Send a Help keystroke
Home{HOME}Send a Home keystroke
Numlock{NUMLOCK}Send a Num Lock keystroke
Page Down
Page Up
{PGDN}
{PGUP}
Send a Page Down or Page Up keystroke
Print Screen{PRTSC}Send a Print Screen keystroke
Scroll lock{SCROLLLOCK}Press the Scroll lock Key (toggle on or off)
TAB{TAB}Send a TAB keystroke

To specify keys combined with any combination of SHIFT, CTRL, and ALT keys, precede the key code with one or more of the following:

   For SHIFT prefix with +
   For CTRL  prefix with ^
   For ALT   prefix with %

RUN Power BI, make refresh and quit

#D:\test\WorldmetersWebExtractor.pbix

D:\_Shortcuts\WorldmetersWebExtractor.lnk

Start-Sleep -s 20

$wshell = New-Object -ComObject Wscript.Shell

[void] [System.Reflection.Assembly]::LoadWithPartialName("'Microsoft.VisualBasic");

#[Microsoft.VisualBasic.Interaction]::AppActivate("Master8 - Power BI Desktop");

Start-Sleep -s 5

#Acts as alt+H

$wshell.SendKeys("%H R");

Start-Sleep -s 2

#Sends R for refresh

$wshell.SendKeys("r")

Start-Sleep -s 60

#Sends 1 - shortcut(S) as Save

#$wshell.SendKeys("%");

#Start-Sleep -s 2

#$wshell.SendKeys("1");

#Start-Sleep -s 10

#Send F as File and Exit

$wshell.SendKeys("%");

Start-Sleep -s 2

$wshell.SendKeys("F");

Start-Sleep -s 2

#Send X as Exit Power BI

$wshell.SendKeys("x")

Start-Sleep -s 2

#Send Enter as Confirm Save of PBIX

$wshell.SendKeys("~")

Run Github Desktop, pull original and quit

#Start GitHub

# every computer may have different path of Github Desktop

C:\Users\Administrator\AppData\Local\GitHubDesktop\GitHubDesktop.exe

Start-Sleep -s 15

#Start Visual Basic Entry Buttons

$wshell = New-Object -ComObject Wscript.Shell

[void] [System.Reflection.Assembly]::LoadWithPartialName("'Microsoft.VisualBasic");

#[Microsoft.VisualBasic.Interaction]::AppActivate("Master8 - Power BI Desktop");

Start-Sleep -s 5

#Refresh - Pull origin (CTRL + SHIFT + P)

$wshell.SendKeys("^+P");

#Set appropriate timer for pulling

Start-Sleep -s 10

#Exit Github Desktop (Alt + F4)

$wshell.SendKeys("%{F4}");

Powershell CD Command

cd – change directory

cd ~\Documents

  • Shortcut do Documents priečinka
  • Napr: C:\Users\Administrator\Documents\WindowsPowerShell>
About author
robert
Contact
About me
Privacy Policy
Cookies
Copyright © 2021 21wiki.com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram