-
5 Attachment(s)
In support of this Thread post
https://eileenslounge.com/viewtopic....290499#p290499
Method a)
This is the way you do it, just in case it is more successful in the future for anything else .
I use here the example of what you are looking for. As you can see, we almost get there, but just crap out at the very last step :(
_1 Go to the Site of the search/ archive engine, web archive org - http://web.archive.org/
That should bring up their “WayBackMachine”
https://i.postimg.cc/XYB2tJgs/Site-web-archive-org.jpg
_ 2 Put that link given from the book in the search bar of their “WayBackMachine”
https://i.postimg.cc/RVFpQGJ5/Put-li...search-bar.jpg
Attachment 3785
_3 Hit Enter
You will then see something like this if it found anything.
https://i.postimg.cc/DZxTzgMv/Lots-o...rcaive-Bot.jpg
In this case it seems to have found a lot, - so at this stage it looks very hopeful
_ 4 Pick one of the years where there are hits shown . That is just guess work – try to think of a year that what you are looking for may have been available. In this next screen shot I chose randomly the year 2003
https://i.postimg.cc/ZYjDdXRx/Chose-...hits-in-it.jpg
Now you will see the dates ringed.
https://i.postimg.cc/q781M5Sn/Ringed...e-was-done.jpg
Those are the dates on which the search/ archive engine, web org, made a random capture of the web site
_ 5 Hover over any of them there ringed dates, and click on a time shown
https://i.postimg.cc/hvCyRQw5/Pick-a-date-and-time.jpg
Attachment 3786
_6 After this you have to take it as it comes and see how far you get.
In this example, I got this, and noticed a link to Examples :
https://i.postimg.cc/3wMLhTSY/Examples-1.jpg
On another attempt after picking a different time and date I got something different but once again found a link to the examples.
https://i.postimg.cc/ncPRd7qs/Examples-2.jpg
Attachment 3787
When I clicked on either of those links I see this, which is still looking hopeful: We are almost there.
https://i.postimg.cc/mkZXnyd9/Almost-there.jpg
Attachment 3788
Unfortunately, every attempt so far by me craps out when I hit that zip link
https://i.postimg.cc/PxQVKRMh/Craps-...st-attempt.jpg
Attachment 3789
I tried a couple of dozen times, and so far it always craps out at the last step. Shame we almost got there- I would take a guess that you won’t find the file this way. When I tried the same with the examples for the Excel book, I got the file on about every second attempt.
As I mentioned it’s a bits hit and miss.
I will look again randomly today when I have a few minutes and let you know if I strike lucky. But I don’t hold out much hope in this case.
Always worth a try. More times than not the web archive works for me. I often use it when passing a link to a current web site and pass the link to the archived stored capture in preference to the actual link because then I know they get what I want them to get. Its invaluable for example, for Microsoft links, since they seem to have nothing better to do then change what actually appears at their help links, some times for the better, sometimes for the worse.
Edit
Method b) Frantic net search
Examples.zip - https://app.box.com/s/w75bf0yb8dv875u0myyyg6xxmr9csiw3
RomanWortdBook.dot - https://app.box.com/s/t09e5w0hrr71slos3tlnb5ds9byw5s3p
BobsdBook.doc - https://app.box.com/s/2wwnfuxupruw8jzfmlhlown9trv8yx3t
-
In support of this Thread
http://www.eileenslounge.com/viewtopic.php?f=41&t=37540
These were the simple steps:
_ Start at free personal account page,( I did give you before ( https://www.box.com/pricing/individual
https://i.postimg.cc/dtqHX8z3/Choose-free-personal.jpg
https://account.box.com/signup/personal?tc=annual ) )
_ https://i.postimg.cc/vTg6c2KX/box-jo...x-password.jpg
You may have some errors if you do not check all boxes
https://i.postimg.cc/W34ZP16B/error-...x-password.jpg
https://i.postimg.cc/0yxCrmYm/error2...x-password.jpg
_ With all boxes checked , it should be successful
https://i.postimg.cc/nLp70ksc/box-jo...x-password.jpg
_ by a successful registration, a confirmation Email will be sent to your given Email address
https://i.postimg.cc/RhFLGTDn/They-s...nfirmation.jpg
The confirmation should arrive at your given Email address, but note it might arrive in spam folder sometimes
https://i.postimg.cc/mZXwk0P3/box-co...ve-in-spam.jpg
_ You must click to confirm your registration
https://i.postimg.cc/x1KMv714/box-co...rify-EMail.jpg
( _ sometime you may receive a warning from your Email provider, but not always
https://i.postimg.cc/Wzh001dG/box-co...il-Warning.jpg )
_ After you confirmation you should arrive at box login
https://i.postimg.cc/FFdp6hFf/Arrive...og-in-page.jpg
_ To login .. first Email address
https://i.postimg.cc/m2n8X0RZ/box-Log-in-EMail.jpg
, then password
https://i.postimg.cc/wjS4vnb4/box-Log-in-password.jpg
_ If all has gone well, you now are logged in to your new account, and can begin using
https://i.postimg.cc/76qVJ419/box-Log-Sucess.jpg
That’s it! You have an account!
_._______________
_ Because I already know all about using, I did skip introduction and set up. But you may chose not to do that
https://i.postimg.cc/Nfs3vhW1/Skipped-Set-up.jpg
_._____________________-
Example Upload and get share link
_New File(s) upload
https://i.postimg.cc/VNqgNvt5/Test-upload-New-File.jpg
( _ you can upload many files at once
https://i.postimg.cc/PrJSMdgF/Upload...es-at-once.jpg )
The files will be uploaded
https://i.postimg.cc/ydKfhcKF/Files-...e-uploaded.jpg
https://i.postimg.cc/HLXZ73qY/All-Fi...e-uploaded.jpg
_ I can get a share link quickly
https://i.postimg.cc/YqDscvNH/I-can-...share-link.jpg
_ I click to copy share link into my clipboard
https://i.postimg.cc/hGQFZ9N6/Copy-s...-Clipboard.jpg
Now that share link is in my clipboard
So finally you have the share link for that File, and can paste it any where
Here is that link:
https://app.box.com/s/gac9uzmfeudlo37hsj4hfim5wb7e3l3z
The peson with that share link can click on it.
Then they will see it and/ or can download it
https://i.postimg.cc/VspVYmYt/I-can-...ownload-it.jpg
Alan
Alan
_._____________________-
P.S. For Image posting share links I also use https://postimages.org/
This does not need any registering. I do use this have for example, for all the image links I did use here.
_ start here: https://postimages.org/
_ https://i.postimg.cc/GtNWPKjX/postimages.jpg
_ Select one or more files to upload https://i.postimg.cc/vZwjQc2x/postim...lect-files.jpg
Files will be uploaded
https://i.postimg.cc/MK1wbkhR/post-images.jpg
https://i.postimg.cc/nhKWMpNR/post-images.jpg
_ you can select what type of link you want
https://i.postimg.cc/SsbjdLgN/post-i...-available.jpg
Finally all links are available for you to copy
https://i.postimg.cc/9zHh51qr/post-i...-available.jpg
-
In support of this Thread https://excelfox.com/forum/showthrea...ed-on-Criteria
https://excelfox.com/forum/showthread.php/2774-Summarize-Data-from-Dates-to-Months-based-on-Criteria
https://i.postimg.cc/14gZ3Xtb/Case-Tracker.jpg
_____ Workbook: Project Tracker.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
B |
C |
D |
|---|
| 2 |
DATE |
CONTRACT NO. |
STATUS |
| 3 |
01-Jul-21 |
|
NOT STARTED |
| 4 |
01-Aug-21 |
|
IN PROGRESS |
| 5 |
02-Aug-21 |
|
COMPLETE |
| 6 |
09-Sep-21 |
|
ON HOLD |
| 7 |
21-Oct-21 |
|
NOT STARTED |
| 8 |
22-Oct-21 |
|
IN PROGRESS |
| 9 |
03-Nov-21 |
|
COMPLETE |
| 10 |
05-Nov-21 |
|
ON HOLD |
| 11 |
12-Dec-21 |
|
COMPLETE |
| 12 |
22-Dec-21 |
|
ON HOLD |
https://i.postimg.cc/H87GZZRT/Summary.jpg
_____ Workbook: Project Tracker.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
B |
C |
D |
E |
F |
|---|
| 2 |
DATE |
NOT STARTED |
IN PROGRESS |
ON HOLD |
COMPLETE |
| 3 |
Aug-21 |
|
|
|
|
| 4 |
Sep-21 |
|
|
|
|
| 5 |
Oct-21 |
|
|
|
|
| 6 |
Nov-21 |
|
|
|
|
| 7 |
Dec-21 |
|
|
|
|
| 8 |
Jan-22 |
|
|
|
|
| 9 |
Feb-22 |
|
|
|
|
| 10 |
Mar-22 |
|
|
|
|
| 11 |
Apr-22 |
|
|
|
|
| 12 |
May-22 |
|
|
|
|
| 13 |
Jun-22 |
|
|
|
|
| 14 |
Jul-22 |
|
|
|
|
| 15 |
Aug-22 |
|
|
|
|
| 16 |
Sep-22 |
|
|
|
|
| 17 |
Oct-22 |
|
|
|
|
| 18 |
Nov-22 |
|
|
|
|
| 19 |
Dec-22 |
|
|
|
|
results after running macro here https://excelfox.com/forum/showthread.php/2774-Summarize-Data-from-Dates-to-Months-based-on-Criteria?p=16306&viewfull=1#post16306 https://excelfox.com/forum/showthrea...ll=1#post16306
_____ Workbook: Project Tracker.xls ( Using Excel 2007 32 bit )
| Row\Col |
B |
C |
D |
E |
F |
|---|
| 2 |
DATE |
NOT STARTED |
IN PROGRESS |
ON HOLD |
COMPLETE |
| 3 |
Aug-21 |
|
1 |
|
1 |
| 4 |
Sep-21 |
|
|
1 |
|
| 5 |
Oct-21 |
1 |
1 |
|
|
| 6 |
Nov-21 |
|
|
1 |
1 |
| 7 |
Dec-21 |
|
|
1 |
1 |
| 8 |
Jan-22 |
|
|
|
|
| 9 |
Feb-22 |
|
|
|
|
| 10 |
Mar-22 |
|
|
|
|
| 11 |
Apr-22 |
|
|
|
|
| 12 |
May-22 |
|
|
|
|
| 13 |
Jun-22 |
|
|
|
|
| 14 |
Jul-22 |
|
|
|
|
| 15 |
Aug-22 |
|
|
|
|
| 16 |
Sep-22 |
|
|
|
|
| 17 |
Oct-22 |
|
|
|
|
| 18 |
Nov-22 |
|
|
|
|
| 19 |
Dec-22 |
|
|
|
|
-
3 Attachment(s)
In support of the forum post:
NOT POSTED YET – DRAFT COPY
Hi
I am new to PowerShell script since a few weeks
I hit my first major coding problem, I have got over some smaller ones.
I have a GUI with lots of buttons on it. Each Button has some various things behind it. Some do some quite major things to the computer, such as registry changes, others download stuff. With one exception all is working as it should**.
Problem Summary
This coding does what it should. I checked it on a few computers with different Windows 10 versions. It checks for installed winget on the computer, and if not there attempts to download it. ( That download might not work for other reasons, but that is a separate issue which I am not concerned with here – as it happens I have it installed on all my computers )
On all my current computers that have winget, the message comes up saying 'winget already installed', and the coding moves on. All is well
Code:
Write-Host "Checking winget..."
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){ # Check if winget is installed
'Winget Already Installed'
}
else{
# Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."
$ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget" # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle
$nid = (Get-Process AppInstaller).Id
Wait-Process -Id $nid
Write-Host Winget Installed
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
} # })
I put that same coding behind a button on a GUI. It seems to work initially, the GUI comes up,
https://i.postimg.cc/xJ3g9CzX/GUI-comes-up.jpg
, and on clicking the button it appears initially to start OK, , but on the same computers, the coding always hangs at
"checking winget…"
Code:
#
Add-Type -AssemblyName System.Windows.Forms
# Create a new form
$Form = New-Object system.Windows.Forms.Form
# Define the size
$Form.ClientSize = '800, 600'
# Range to put button in
$Panel10 = New-Object system.Windows.Forms.Panel ; $Panel10.height = 50 ; $Panel10.width = 250 ; $Panel10.location = New-Object System.Drawing.Point(1, 25)
# function to create sinple botton
function Create-Button {param([string]$Text, [int]$FntSz, [int]$Width, [int]$Height, [int]$ClmX, [int]$RwY)#As Object ' This function allows us to make a buttons in one line. (Those later single lines do not make the button appear)
$Btn = New-Object System.Windows.Forms.Button #
$Btn.Text = $Text #
$Btn.Width = $Width ; $Btn.Height = $Height #
#
$Btn.Location = New-Object System.Drawing.Point($ClmX, $RwY) #
$Btn.Font = New-Object System.Drawing.Font('Arial', $FntSz) # ('Microsoft Sans Serif', 9)
#
return $Btn } #
# Make button
$GetWinGet = Create-Button -Text "winget" -FntSz 9 -Width 117 -Height 21 -ClmX 3 -RwY 1
$GetWinGet.Add_Click({
Write-Host "Checking winget..." # PROBLEM!!!! This wont work in a button - it freezes here?
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){ # Check if winget is installed
'Winget Already Installed'
}
else{
# Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."
$ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget" # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle
$nid = (Get-Process AppInstaller).Id
Wait-Process -Id $nid
Write-Host Winget Installed
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
} })
# Add Button to range
$Panel10.controls.AddRange(@($GetWinGet))
# Add ranbge to Form
$Form.controls.AddRange(@($Panel10))
# Display the form
[void]$Form.ShowDialog()
On this same GUI I can put lots of other buttons, all doing different things, and they always do what they should**
What am I missing?
Alan
-
Test
HTML Code:
#
Add-Type -AssemblyName System.Windows.Forms
# Create a new form
$Form = New-Object system.Windows.Forms.Form
# Define the size, title and background color
$Form.ClientSize = '800, 600'
# Range to put button in
$Panel10 = New-Object system.Windows.Forms.Panel ; $Panel10.height = 50 ; $Panel10.width = 250 ; $Panel10.location = New-Object System.Drawing.Point(1, 25)
# function to create sinple botton
function Create-Button {param([string]$Text, [int]$FntSz, [int]$Width, [int]$Height, [int]$ClmX, [int]$RwY)#As Object ' This function allows us to make a buttons in one line. (Those later single lines do not make the button appear)
$Btn = New-Object System.Windows.Forms.Button #
$Btn.Text = $Text #
$Btn.Width = $Width ; $Btn.Height = $Height #
#
$Btn.Location = New-Object System.Drawing.Point($ClmX, $RwY) #
$Btn.Font = New-Object System.Drawing.Font('Arial', $FntSz) # ('Microsoft Sans Serif', 9)
#
return $Btn } #
# Make button
$GetWinGet = Create-Button -Text "winget" -FntSz 9 -Width 117 -Height 21 -ClmX 3 -RwY 1 # $firefox.width = 212
$GetWinGet.Add_Click({
Write-Host "Checking winget..." # PROBLEM!!!! This wont work in a button - it freezes here?
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){ # Check if winget is installed
'Winget Already Installed'
}
else{
# Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."
$ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget" # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle #$vscode.width = 211
$nid = (Get-Process AppInstaller).Id
Wait-Process -Id $nid
Write-Host Winget Installed
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
} })
# Add Button to range
$Panel10.controls.AddRange(@($GetWinGet))
# Add ranbge to Form
$Form.controls.AddRange(@($Panel10))
# Display the form
[void]$Form.ShowDialog()
PHP Code:
#
Add-Type -AssemblyName System.Windows.Forms
# Create a new form
$Form = New-Object system.Windows.Forms.Form
# Define the size, title and background color
$Form.ClientSize = '800, 600'
# Range to put button in
$Panel10 = New-Object system.Windows.Forms.Panel ; $Panel10.height = 50 ; $Panel10.width = 250 ; $Panel10.location = New-Object System.Drawing.Point(1, 25)
# function to create sinple botton
function Create-Button {param([string]$Text, [int]$FntSz, [int]$Width, [int]$Height, [int]$ClmX, [int]$RwY)#As Object ' This function allows us to make a buttons in one line. (Those later single lines do not make the button appear)
$Btn = New-Object System.Windows.Forms.Button #
$Btn.Text = $Text #
$Btn.Width = $Width ; $Btn.Height = $Height #
#
$Btn.Location = New-Object System.Drawing.Point($ClmX, $RwY) #
$Btn.Font = New-Object System.Drawing.Font('Arial', $FntSz) # ('Microsoft Sans Serif', 9)
#
return $Btn } #
# Make button
$GetWinGet = Create-Button -Text "winget" -FntSz 9 -Width 117 -Height 21 -ClmX 3 -RwY 1 # $firefox.width = 212
$GetWinGet.Add_Click({
Write-Host "Checking winget..." # PROBLEM!!!! This wont work in a button - it freezes here?
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){ # Check if winget is installed
'Winget Already Installed'
}
else{
# Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."
$ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget" # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle #$vscode.width = 211
$nid = (Get-Process AppInstaller).Id
Wait-Process -Id $nid
Write-Host Winget Installed
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
} })
# Add Button to range
$Panel10.controls.AddRange(@($GetWinGet))
# Add ranbge to Form
$Form.controls.AddRange(@($Panel10))
# Display the form
[void]$Form.ShowDialog()
-
In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16373
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
| 1 |
S.No. |
Year |
Month |
Name |
Project |
Task |
Amount |
Submitted By |
|
| 2 |
1 |
2022 |
January |
bbb |
Project2 |
Task2 |
100 |
Liviu Popescu |
|
| 3 |
2 |
2022 |
February |
ccc |
Project5 |
Task1 |
200 |
Liviu Popescu |
|
| 4 |
3 |
2022 |
March |
aaa |
Project3 |
Task2 |
500 |
Liviu Popescu |
|
| 5 |
|
|
|
|
|
|
|
|
|
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
|---|
| 1 |
Name |
Project |
Task |
01-22 |
02-22 |
03-22 |
04-22 |
| 2 |
aaa |
Project1 |
Task1 |
|
|
|
|
| 3 |
aaa |
Project1 |
Task2 |
|
|
|
|
| 4 |
aaa |
Project2 |
Task1 |
|
|
|
|
| 5 |
aaa |
Project2 |
Task2 |
|
|
|
|
| 6 |
aaa |
Project3 |
Task1 |
|
|
|
|
| 7 |
aaa |
Project3 |
Task2 |
|
|
|
|
| 8 |
aaa |
Project4 |
Task1 |
|
|
|
|
| 9 |
aaa |
Project4 |
Task2 |
|
|
|
|
| 10 |
aaa |
Project5 |
Task1 |
|
|
|
|
| 11 |
aaa |
Project5 |
Task2 |
|
|
|
|
| 12 |
bbb |
Project1 |
Task1 |
|
|
|
|
| 13 |
bbb |
Project1 |
Task2 |
|
|
|
|
| 14 |
bbb |
Project2 |
Task1 |
|
|
|
|
| 15 |
bbb |
Project2 |
Task2 |
|
|
|
|
| 16 |
bbb |
Project3 |
Task1 |
|
|
|
|
| 17 |
bbb |
Project3 |
Task2 |
|
|
|
|
| 18 |
bbb |
Project4 |
Task1 |
|
|
|
|
| 19 |
bbb |
Project4 |
Task2 |
|
|
|
|
| 20 |
bbb |
Project5 |
Task1 |
|
|
|
|
| 21 |
bbb |
Project5 |
Task2 |
|
|
|
|
| 22 |
ccc |
Project1 |
Task1 |
|
|
|
|
| 23 |
ccc |
Project1 |
Task2 |
|
|
|
|
| 24 |
ccc |
Project2 |
Task1 |
|
|
|
|
| 25 |
ccc |
Project2 |
Task2 |
|
|
|
|
| 26 |
ccc |
Project3 |
Task1 |
|
|
|
|
| 27 |
ccc |
Project3 |
Task2 |
|
|
|
|
| 28 |
ccc |
Project4 |
Task1 |
|
|
|
|
| 29 |
ccc |
Project4 |
Task2 |
|
|
|
|
| 30 |
ccc |
Project5 |
Task1 |
|
|
|
|
| 31 |
ccc |
Project5 |
Task2 |
|
|
|
|
| 32 |
ddd |
Project1 |
Task1 |
|
|
|
|
| 33 |
ddd |
Project1 |
Task2 |
|
|
|
|
-
In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16376
Before, as we had before
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
| 1 |
S.No. |
Year |
Month |
Name |
Project |
Task |
Amount |
Submitted By |
|
| 2 |
1 |
2022 |
January |
bbb |
Project2 |
Task2 |
100 |
Liviu Popescu |
|
| 3 |
2 |
2022 |
February |
ccc |
Project5 |
Task1 |
200 |
Liviu Popescu |
|
| 4 |
3 |
2022 |
March |
aaa |
Project3 |
Task2 |
500 |
Liviu Popescu |
|
| 5 |
|
|
|
|
|
|
|
|
|
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
|---|
| 1 |
Name |
Project |
Task |
01-22 |
02-22 |
03-22 |
04-22 |
| 2 |
aaa |
Project1 |
Task1 |
|
|
|
|
| 3 |
aaa |
Project1 |
Task2 |
|
|
|
|
| 4 |
aaa |
Project2 |
Task1 |
|
|
|
|
| 5 |
aaa |
Project2 |
Task2 |
|
|
|
|
| 6 |
aaa |
Project3 |
Task1 |
|
|
|
|
| 7 |
aaa |
Project3 |
Task2 |
|
|
|
|
| 8 |
aaa |
Project4 |
Task1 |
|
|
|
|
| 9 |
aaa |
Project4 |
Task2 |
|
|
|
|
| 10 |
aaa |
Project5 |
Task1 |
|
|
|
|
| 11 |
aaa |
Project5 |
Task2 |
|
|
|
|
| 12 |
bbb |
Project1 |
Task1 |
|
|
|
|
| 13 |
bbb |
Project1 |
Task2 |
|
|
|
|
| 14 |
bbb |
Project2 |
Task1 |
|
|
|
|
| 15 |
bbb |
Project2 |
Task2 |
|
|
|
|
| 16 |
bbb |
Project3 |
Task1 |
|
|
|
|
| 17 |
bbb |
Project3 |
Task2 |
|
|
|
|
| 18 |
bbb |
Project4 |
Task1 |
|
|
|
|
| 19 |
bbb |
Project4 |
Task2 |
|
|
|
|
| 20 |
bbb |
Project5 |
Task1 |
|
|
|
|
| 21 |
bbb |
Project5 |
Task2 |
|
|
|
|
| 22 |
ccc |
Project1 |
Task1 |
|
|
|
|
| 23 |
ccc |
Project1 |
Task2 |
|
|
|
|
| 24 |
ccc |
Project2 |
Task1 |
|
|
|
|
| 25 |
ccc |
Project2 |
Task2 |
|
|
|
|
| 26 |
ccc |
Project3 |
Task1 |
|
|
|
|
| 27 |
ccc |
Project3 |
Task2 |
|
|
|
|
| 28 |
ccc |
Project4 |
Task1 |
|
|
|
|
| 29 |
ccc |
Project4 |
Task2 |
|
|
|
|
| 30 |
ccc |
Project5 |
Task1 |
|
|
|
|
| 31 |
ccc |
Project5 |
Task2 |
|
|
|
|
| 32 |
ddd |
Project1 |
Task1 |
|
|
|
|
| 33 |
ddd |
Project1 |
Task2 |
|
|
|
|
-
In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16376
After
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
|---|
1 |
Name |
Project |
Task |
01-22 |
02-22 |
03-22 |
04-22 |
2 |
aaa |
Project1 |
Task1 |
|
|
|
|
3 |
aaa |
Project1 |
Task2 |
|
|
|
|
4 |
aaa |
Project2 |
Task1 |
|
|
|
|
5 |
aaa |
Project2 |
Task2 |
|
|
|
|
6 |
aaa |
Project3 |
Task1 |
|
|
|
|
7 |
aaa |
Project3 |
Task2 |
|
|
500 |
|
8 |
aaa |
Project4 |
Task1 |
|
|
|
|
9 |
aaa |
Project4 |
Task2 |
|
|
|
|
10 |
aaa |
Project5 |
Task1 |
|
|
|
|
11 |
aaa |
Project5 |
Task2 |
|
|
|
|
12 |
bbb |
Project1 |
Task1 |
|
|
|
|
13 |
bbb |
Project1 |
Task2 |
|
|
|
|
14 |
bbb |
Project2 |
Task1 |
|
|
|
|
15 |
bbb |
Project2 |
Task2 |
100 |
|
|
|
16 |
bbb |
Project3 |
Task1 |
|
|
|
|
17 |
bbb |
Project3 |
Task2 |
|
|
|
|
18 |
bbb |
Project4 |
Task1 |
|
|
|
|
19 |
bbb |
Project4 |
Task2 |
|
|
|
|
20 |
bbb |
Project5 |
Task1 |
|
|
|
|
21 |
bbb |
Project5 |
Task2 |
|
|
|
|
22 |
ccc |
Project1 |
Task1 |
|
|
|
|
23 |
ccc |
Project1 |
Task2 |
|
|
|
|
24 |
ccc |
Project2 |
Task1 |
|
|
|
|
25 |
ccc |
Project2 |
Task2 |
|
|
|
|
26 |
ccc |
Project3 |
Task1 |
|
|
|
|
27 |
ccc |
Project3 |
Task2 |
|
|
|
|
28 |
ccc |
Project4 |
Task1 |
|
|
|
|
29 |
ccc |
Project4 |
Task2 |
|
|
|
|
30 |
ccc |
Project5 |
Task1 |
|
200 |
|
|
31 |
ccc |
Project5 |
Task2 |
|
|
|
|
32 |
ddd |
Project1 |
Task1 |
|
|
|
|
-
In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16376
After
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
|---|
1 |
Name |
Project |
Task |
01-22 |
02-22 |
03-22 |
04-22 |
2 |
aaa |
Project1 |
Task1 |
|
|
|
|
3 |
aaa |
Project1 |
Task2 |
|
|
|
|
4 |
aaa |
Project2 |
Task1 |
|
|
|
|
5 |
aaa |
Project2 |
Task2 |
|
|
|
|
6 |
aaa |
Project3 |
Task1 |
|
|
|
|
7 |
aaa |
Project3 |
Task2 |
|
|
500 |
|
8 |
aaa |
Project4 |
Task1 |
|
|
|
|
9 |
aaa |
Project4 |
Task2 |
|
|
|
|
10 |
aaa |
Project5 |
Task1 |
|
|
|
|
11 |
aaa |
Project5 |
Task2 |
|
|
|
|
12 |
bbb |
Project1 |
Task1 |
|
|
|
|
13 |
bbb |
Project1 |
Task2 |
|
|
|
|
14 |
bbb |
Project2 |
Task1 |
|
|
|
|
15 |
bbb |
Project2 |
Task2 |
100 |
|
|
|
16 |
bbb |
Project3 |
Task1 |
|
|
|
|
17 |
bbb |
Project3 |
Task2 |
|
|
|
|
18 |
bbb |
Project4 |
Task1 |
|
|
|
|
19 |
bbb |
Project4 |
Task2 |
|
|
|
|
20 |
bbb |
Project5 |
Task1 |
|
|
|
|
21 |
bbb |
Project5 |
Task2 |
|
|
|
|
22 |
ccc |
Project1 |
Task1 |
|
|
|
|
23 |
ccc |
Project1 |
Task2 |
|
|
|
|
24 |
ccc |
Project2 |
Task1 |
|
|
|
|
25 |
ccc |
Project2 |
Task2 |
|
|
|
|
26 |
ccc |
Project3 |
Task1 |
|
|
|
|
27 |
ccc |
Project3 |
Task2 |
|
|
|
|
28 |
ccc |
Project4 |
Task1 |
|
|
|
|
29 |
ccc |
Project4 |
Task2 |
|
|
|
|
30 |
ccc |
Project5 |
Task1 |
|
200 |
|
|
31 |
ccc |
Project5 |
Task2 |
|
|
|
|
32 |
ddd |
Project1 |
Task1 |
|
|
|
|
-
In support of these forum posts
https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776
https://excelfox.com/forum/showthrea...5356#post15356
https://eileenslounge.com/viewtopic.php?f=18&t=37740
https://eileenslounge.com/viewtopic.php?f=18&t=37712
https://eileenslounge.com/viewtopic.php?f=18&t=37707
Code:
'Version : 5.1.19041.1320
'InstanceId : e165cf30-9ddd-49ed-96c7-59cca98516ee
'UI: System.Management.Automation.Internal.Host.InternalHostUserInterface
'CurrentCulture: DE -DE
'CurrentUICulture: DE -DE
'PrivateData: Microsoft.PowerShell.Host.ISE.ISEOptions
'DebuggerEnabled : True
'IsRunspacePushed : False
'Runspace: System.Management.Automation.Runspaces.LocalRunspace
'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776
'https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15356#post15356
'https://eileenslounge.com/viewtopic.php?f=18&t=37740
'https://eileenslounge.com/viewtopic.php?f=18&t=37712
'https://eileenslounge.com/viewtopic.php?f=18&t=37707
Sub Services() ' https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15356#post15356
' PowerShell
Dim PScmdLet As String, cmdLet As String
'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000"
Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 1000"
Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/
CreateObject("WScript.Shell").Exec (PScmdLet)
' Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
Get #FileNum, , TotalFile 'Debug.Print TotalFile
Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather-a-lot-of-Chr-0-s.jpg
'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by
Close #FileNum
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
' make a 1 D array of the text file lines
Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
' make array for output
Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0
Dim Cnt As Long
For Cnt = 1 To UBound(arrRws()) - 2
If arrRws(Cnt + 2) = "" Then
' This should occur at the last empty rows, so we could consider jumping out of the loop here
Else
' time to split the line string
Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare)
Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1)
Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare)
Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3)
Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, -1, vbBinaryCompare)
Let DispNme = Replace(DispNme, StrtTyp, "", 1, -1, vbBinaryCompare)
Let DispNme = Trim(DispNme)
' fill the array for output
Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp
End If
Next Cnt
' Chuck array into a spreadsheet
Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut()
ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit
End Sub
Edit Some issues…
I messed up with a few things.
_ the display name could be long and go up to the startuptype in the text file, which messed up the manipulation of a line of data from the text file a bit. For now I fiddled that by adding some spaces before the words used for the startuptype. A better solution will probably wait until I fully understand the PowerShell code line
_ There seems to be some strange effects with something somewhere working too slow, too fast or not giving accurate information about if a text file is present. For now that is fiddled with some Waits , Dirs and a Kill. That will do for now, but that need to be looked at again when I understand better wots going on
The next code version is in the next post
https://excelfox.com/forum/showthrea...ll=1#post16369
