×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hello All, hope somebody could h
2

Hello All, hope somebody could h

Hello All, hope somebody could h

(OP)
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

RE: Hello All, hope somebody could h

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:

CODE --> PowerShell

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:

CODE --> PowerShell

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

RE: Hello All, hope somebody could h

(OP)
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

RE: Hello All, hope somebody could h

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. :)

RE: Hello All, hope somebody could h

(OP)
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.

RE: Hello All, hope somebody could h

(OP)
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?

RE: Hello All, hope somebody could h

Hi sglab,

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

CODE

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.

RE: Hello All, hope somebody could h

(OP)
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

RE: Hello All, hope somebody could h

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.

RE: Hello All, hope somebody could h

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

RE: Hello All, hope somebody could h

>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

RE: Hello All, hope somebody could h

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.

CODE

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.

RE: Hello All, hope somebody could h

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.

RE: Hello All, hope somebody could h

Hi sglab,

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

Download file:

download_binary_file.vbs

CODE

'see:
'https://stackoverflow.com/questions/2973136/download-a-file-with-vbs
'https://serverfault.com/questions/29707/download-file-from-vbscript
'run:
'cscript /NoLogo download_binary_file.vbs

set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1") 
winHttp.Open "GET", "http://www.flexus.com/ftp/cobfd.zip", 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

CODE

'see:
'https://stackoverflow.com/questions/911053/how-to-unzip-a-file-in-vbscript-using-internal-windows-xp-options-in
'https://asmand.wordpress.com/2015/06/15/unzip-with-vbscript/
'https://stackoverflow.com/questions/31222255/vbs-unzipping-object-required-objshell-namespace
'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 

RE: Hello All, hope somebody could h

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

CODE

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

CODE

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:

CODE

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.

RE: Hello All, hope somebody could h

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.

CODE

Sub download_binary_file()
    'see:
    'https://stackoverflow.com/questions/2973136/download-a-file-with-vbs
    'https://serverfault.com/questions/29707/download-file-from-vbscript
    'run:
    'cscript /NoLogo download_binary_file.vbs

    Set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    winHttp.Open "GET", "http://www.flexus.com/ftp/cobfd.zip", 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:
    'https://stackoverflow.com/questions/911053/how-to-unzip-a-file-in-vbscript-using-internal-windows-xp-options-in
    'https://asmand.wordpress.com/2015/06/15/unzip-with-vbscript/
    'https://stackoverflow.com/questions/31222255/vbs-unzipping-object-required-objshell-namespace
    '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 

RE: Hello All, hope somebody could h

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close