Hello All, hope somebody could h 2

[sglab]

Hello All,

hope somebody could help with this.
We regularly receive text files, which contain list of hyperlinks to ZIP files, which we need to download to a local drive. Doing it one by one could be annoying so, is there a way to automate this task, using Excel VBA?

Thank you

 

[Rick998]

I can't help thinking that Excel VBA is not the best tool for the job when you probably already have a far more capable (and powerful) tool already built-in... PowerShell.

At its simplest, the following one-liner run from a PowerShell console may do it:

gc urls.txt | % {iwr $_ -outf $(split-path $_ -leaf)}

No, I didn't write it... have a look at PowerShell: Download a list of files for an article about a basic way to feed in a text file of URLs and download them, including an expanded PowerShell explanation of what that one-liner actually means.

Better still, have a look at this Download a File with an Alternative PowerShell wget Command article which explores 4 different methods of doing the same and walks you through each of them. (NB: Make sure you read the comment about asynchronomous mode at the end of the article.)

In particular, have a look at the method using BITS with a list of files. BITS (Background Intelligent Transfer Service) is built-in to Windows 10 and, unlike Excel VBA, is a service specifically designed to download files efficiently. All you have to do is use PowerShell to tell it what to do and, again, it could be as simple as:

Import-Csv .\filelist.csv | Start-BitsTransfer

Having said that, once you realise the built-in capability of PowerShell it shouldn't take all that much to adapt other people's code to your own situation (including error-checking and perhaps adding logging).

Just Google powershell download list of files to find lots more articles, examples and explanations.

The point I'm trying to make is that, IMO, PowerShell is MUCH better suited to carrying out this type of task. Use Excel to create the CSV file (and perhaps VBA to merely run any PowerShell script from within Excel).

Hope this helps...

 

[sglab]

Hello Rick998,

thank you for your reply. It's really helpful.
PowerShell is something I haven't touched before - maybe it's time I did - and Excel is right there and it speaks human language.
Anyways, I will look into utilizing PowerShell

Have a great day

 

[Rick998]

I only touch on the periphery of PowerShell and only learn the bare minimum I need to accomplish what I want to do.

To paraphrase... "I stand on the shoulders of others...", meaning I copy other people's efforts and adapt them to my own needs. I couldn't write a PowerShell script from scratch if you paid me. However, I don't believe in re-inventing the wheel when much smarter folks than me have already figured out most of what I want to do... and give generously of their knowledge.

 

[sglab]

Well, this is pretty much what I am doing here.
My task a bit more complex than I originally stated, and I am trying to piece together snippets from different scripts I find.
We actually receive a ZIP file and a txt file with a password. ZIP extracts a bunch of folders and one of them has a csv file with the names of GPG files and links from where they can be downloaded. Once GPGs have been downloaded, they need to be decrypted, using the password in the text file I mentioned before. The result of the decryption are ZIP files which need to be extracted to get actual data. This entire process is what I am trying to automate.

 

[mikrom]

Hi sglab,
You are here in VBscript Forum und asks questions about Excel VBA.
VBA and VBscript are not the same, the VBA forum is here:

https://www.tek-tips.com/threadminder.cfm?pid=707

 

[sglab]

Hello microm,

thank you for pointing this out and sorry, if my question upset anyone.
I know that VBA and VBScript are not the same. I wouldn't mind if you or anyone else could come up with something in VBScript to address my question. Isn't the task at least a bit interesting?

 

[mikrom]

Hi sglab,

the task you mentioned is interesting, it seems for a script like this

get on the script input 2 files:
  - TXT file
  - ZIP file

Then do following:
- read password from TXT file
- extract ZIP file, getting bunch of folders
- read CSV file from specific folder (extracted from ZIP file) 
- for every line in CSV file do:
   -- parse download link for GPG file 
   -- download GPG file from given link
   -- decrypt GPG file using the password from TXT-file, getting ZIP file
   -- extract ZIP file

You can use VBscript for it, but you will need to install on Windows some command line utilities, like:
- zip for extracting archives
- curl or wget for downloading files
- GnuPG
Then you will have to call these utilies from your VB script.

You can try to use PowerShell as suggested above, but IMO its initial learning curve is steep.

IMO, bash is best suited for tasks likes this. Hovewer to use it on Windows, you would need to install unix command line utilities like MingW/MSYS or Cygwin or use WSL.

Other option is python. It's multi platform, easy to learn and has modules available for all kinds of tasks.

 

[sglab]

Hello microm,

your breakdown of what I described previously looks much more organized.
The problem with installing any of the utilities you mentioned is not allowed in our environment.
We do have the bash script that comes with the data and which does all of it, but to run it we have to use off network computers for the same reason - not allowed. Hence VBA, mostly. Or VBScript occasionally.
Anyway, thank you and have a great day

 

[mikrom]

Hi sglab,
If you have such strict rules, the Powershell execution may also be banned. Almost every time I had to use Powershell, I struggled with Powershell Execution Policy.

 

[sglab]

Hello mikrom,
in your experience, will Python have all necessary for this task modules/libraries?

 

[strongm]

>installing any of the utilities you mentioned is not allowed in our environment.

Then you have a problem, as you will definitely need GnuPG installed to allow the GPG decrypt

 

[mikrom]

Hi sglab,
Python has modules for all of these tasks available:
For unzipping you need the module named zipfile, for downloading there exists more alternative modules e.g. urllib or requests, ..etc. For decrypting there is gnupg module.
I have them all in my python installation ... but I'm on Linux.

Python 3.6.9 (default, Dec  8 2021, 21:08:43) 
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license()" for more information.
>>> import zipfile
>>> import urllib
>>> import requests
>>> import gnupg

What modules you have, depends on your python installation on windows. I run on windows an older ActiveState Python version, which is battery included and contain lot of modules.
But if you don't have one of modules listed above you can install it via python package manager pip.

 

[mikrom]

Hi sglab,

But anyway, I would still give the Vbscript solution a chance.
I googled and it looks like downloading and unzipping can be done in the VBscript without any other tools. You only need to install GnuPG for decryption.

 

[mikrom]

Hi sglab,

This worked for me in Vbscript, you can try it:

Download file:

download_binary_file.vbs

'see:
'[URL unfurl="true"]https://stackoverflow.com/questions/2973136/download-a-file-with-vbs[/URL]
'[URL unfurl="true"]https://serverfault.com/questions/29707/download-file-from-vbscript[/URL]
'run:
'cscript /NoLogo download_binary_file.vbs

set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1") 
winHttp.Open "GET", "[URL unfurl="true"]http://www.flexus.com/ftp/cobfd.zip",[/URL] False
winHttp.Send

set stream = createobject("Adodb.Stream")
with stream
    .type = 1 '//binary
    .open
    .write winHttp.responseBody
    .savetofile ".\cobfd.zip", 2 '//overwrite
end with

set winHttp = Nothing
set stream = Nothing

Unzip archive:

unzip_archive.vbs

'see:
'[URL unfurl="true"]https://stackoverflow.com/questions/911053/how-to-unzip-a-file-in-vbscript-using-internal-windows-xp-options-in[/URL]
'[URL unfurl="true"]https://asmand.wordpress.com/2015/06/15/unzip-with-vbscript/[/URL]
'[URL unfurl="true"]https://stackoverflow.com/questions/31222255/vbs-unzipping-object-required-objshell-namespace[/URL]
'run:
'cscript /NoLogo unzip_archive.vbs

'The location of the zip file.
ZipFile="cobfd.zip"
'The folder the contents should be extracted to.
ExtractToFolder="COBFD"

set fso = CreateObject("Scripting.FileSystemObject")
ZipFilePath = fso.GetAbsolutePathName(ZipFile)
ExtractToFolderPath = fso.GetAbsolutePathName(ExtractToFolder)

'If the extraction location does not exist create it.
if not fso.FolderExists(ExtractToFolderPath) then
   fso.CreateFolder(ExtractToFolderPath)
end if

'Extract the contants of the zip file.
set objShell = CreateObject("Shell.Application")
set FilesInZip=objShell.NameSpace(ZipFilePath).Items()

objShell.NameSpace(ExtractToFolderPath).CopyHere(FilesInZip)

set fso = Nothing
set objShell = Nothing
set FilesInZip = Nothing

 

[mikrom]

Hi sglab,

For this proof of concept I didn't want to install GnuPG in my Windows10 and just looked for a ZIP archive that I can download and extract into my working directory. Here I found it:

https://gnupg.org/ftp/gcrypt/binary/

I downloaded 2 archives (maybe old versions but they work)
- gnupg-w32cli-1.4.0a.zip
- libiconv-1.9.1.dll.zip
extracted them, copied all files into one subdirectory GNUPG in my working directory and then added to my path the GNUPG folder

set PATH=%PATH%;c:\path_to_my_dir\gnupg

Now I have in my working directory a subdirectory GNUPG and the file cobfd.zip I downloaded before - see above.
To encrypt the file I enter on command line

gpg -c cobfd.zip

and then gpg asks me to enter password twice and creates encrypted file cobfd.zip.gpg

To decrypt the file cobfd.zip.gpg from a script we need to run gnupg in batch mode and pass to it the password on command line. After a certain amount of torture, I found that this works for me:

echo mypass| gpg --batch --passphrase-fd 0 cobfd.zip.gpg

As I mentioned above I used old version gpg (GnuPG) 1.4.0. Maybe in newer version other command line arguments for decrypting in batch mode are available.

Now when the decrypting command works on command line, it could be executed from Vbscript.

 

[mikrom]

Hi sglab,

You originally asked for Excel VBA solution.
VBScript is a subset of the VBA, so the VBscript code for downloading and unzipping I posted above, works in VBA too. I created an Excel workbook (.xlsm), inserted in it a module with the following code, assigned the subroutines to the buttons - and that's it - it works.

Sub download_binary_file()
    'see:
    '[URL unfurl="true"]https://stackoverflow.com/questions/2973136/download-a-file-with-vbs[/URL]
    '[URL unfurl="true"]https://serverfault.com/questions/29707/download-file-from-vbscript[/URL]
    'run:
    'cscript /NoLogo download_binary_file.vbs

    Set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    winHttp.Open "GET", "[URL unfurl="true"]http://www.flexus.com/ftp/cobfd.zip",[/URL] False
    winHttp.Send

    Set fso = CreateObject("Scripting.FileSystemObject")
    'zip file name and path
    ZipFile = "cobfd.zip"
    ZipFilePath = fso.BuildPath(ActiveWorkbook.Path, ZipFile)
    
    Set stream = CreateObject("Adodb.Stream")
    With stream
        .Type = 1 '//binary
        .Open
        .write winHttp.responseBody
        .savetofile ZipFilePath, 2 '//overwrite
    End With

    Set winHttp = Nothing
    Set stream = Nothing
End Sub

Sub unzip_archive()
    'see:
    '[URL unfurl="true"]https://stackoverflow.com/questions/911053/how-to-unzip-a-file-in-vbscript-using-internal-windows-xp-options-in[/URL]
    '[URL unfurl="true"]https://asmand.wordpress.com/2015/06/15/unzip-with-vbscript/[/URL]
    '[URL unfurl="true"]https://stackoverflow.com/questions/31222255/vbs-unzipping-object-required-objshell-namespace[/URL]
    'run:
    'cscript /NoLogo unzip_archive.vbs

    'zip file name
    ZipFile = "cobfd.zip"
    'The folder the contents should be extracted to
    ExtractToFolder = "COBFD"

    Set fso = CreateObject("Scripting.FileSystemObject")
    ZipFilePath = fso.BuildPath(ActiveWorkbook.Path, ZipFile)
    ExtractToFolderPath = fso.BuildPath(ActiveWorkbook.Path, ExtractToFolder)
  
    'If the extraction location does not exist create it
    If Not fso.FolderExists(ExtractToFolderPath) Then
        fso.CreateFolder (ExtractToFolderPath)
    End If

    'Extract the contants of the zip file.
    Set objShell = CreateObject("Shell.Application")
    Set FilesInZip = objShell.Namespace(ZipFilePath).Items()

    objShell.Namespace(ExtractToFolderPath).CopyHere (FilesInZip)

    Set fso = Nothing
    Set objShell = Nothing
    Set FilesInZip = Nothing
End Sub

 

[sglab]

Hello microm,
that is more that I could hope for.
Thank you very much!!!
I will study the code and try to use it, or parts of it, in our environment

Best regards