Looking for ways how to get some tasks automated w/o users intervention.

Hi Experts,

I have some tasks that currently users must do it manually in daily bases, however since this is prone to errors, the manager would like it should get automated done via code.

1- Look for specific file in default download folder that name starts with Tables_TodaysDate_LatestNumber.Zip (See attached).
2- Extract it to specific folder (C:\Application)
3- Delete old File C:\Application\Tables.MDB
4- Rename extracted file (something like Tables_2017-Oct-30_2230.mdb) to Tables.MDB

Would prefer it done either by calling some DOS commands from VBA or just using native VB functions if possible.

Thanks in advance.
Capture.PNG
LVL 5
bfuchsAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
Use Dir or FileSystemObject to locate the latest file.

Then pass the full path and file name of this to the function below, like:

Path = "d:\yourdownloadfolder\Tables_2017-mmm-dd_xxxx.zip"
Destination = "C:\Application\Tables.MDB"
Result = UnZip(Path, Destination, True)

Open in new window

The unzip function:

' Select Early Binding (True) or Late Binding (False).
#Const EarlyBinding = True

' Unzip files from a zip file to a folder using Windows Explorer.
' Default behaviour is similar to right-clicking a file/folder and selecting:
'   Unzip all ...
'
' Parameters:
'   Path:
'       Valid (UNC) path to a valid zip file. Extension can be another than "zip".
'   Destination:
'       (Optional) Valid (UNC) path to the destination folder.
'   Overwrite:
'       (Optional) Leave (default) or overwrite an existing folder.
'       If False, an existing folder will keep other files than those in the extracted zip file.
'       If True, an existing folder will first be deleted, then recreated.
'
'   Path and Destination can be relative paths. If so, the current path is used.
'
'   If success, 0 is returned, and Destination holds the full path of the created folder.
'   If error, error code is returned, and Destination will be zero length string.
'
' Early binding requires references to:
'
'   Shell:
'       Microsoft Shell Controls And Automation
'
'   Scripting.FileSystemObject:
'       Microsoft Scripting Runtime
'
' 2017-10-22. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function UnZip( _
    ByVal Path As String, _
    Optional ByRef Destination As String, _
    Optional ByVal Overwrite As Boolean) _
    As Long
    
#If EarlyBinding Then
    ' Microsoft Scripting Runtime.
    Dim FileSystemObject    As Scripting.FileSystemObject
    ' Microsoft Shell Controls And Automation.
    Dim ShellApplication    As Shell
    
    Set FileSystemObject = New Scripting.FileSystemObject
    Set ShellApplication = New Shell
#Else
    Dim FileSystemObject    As Object
    Dim ShellApplication    As Object

    Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set ShellApplication = CreateObject("Shell.Application")
#End If
               
    ' Extension of a cabinet file holding one or more files.
    Const CabExtensionName  As String = "cab"
    ' Mandatory extension of zip file.
    Const ZipExtensionName  As String = "zip"
    Const ZipExtension      As String = "." & ZipExtensionName
    ' Constants for Shell.Application.
    Const OverWriteAll      As Long = &H10&
    ' Custom error values.
    Const ErrorNone         As Long = 0
    Const ErrorOther        As Long = -1
    
    Dim ZipName             As String
    Dim ZipPath             As String
    Dim ZipTemp             As String
    Dim Result              As Long
    
    If FileSystemObject.FileExists(Path) Then
        ' The source is an existing file.
        ZipName = FileSystemObject.GetBaseName(Path)
        ZipPath = FileSystemObject.GetFile(Path).ParentFolder
    End If
    
    If ZipName = "" Then
        ' Nothing to unzip. Exit.
        Destination = ""
    Else
        ' Select or create destination folder.
        If Destination <> "" Then
            ' Unzip to a custom folder.
            If _
                FileSystemObject.GetExtensionName(Destination) = CabExtensionName Or _
                FileSystemObject.GetExtensionName(Destination) = ZipExtensionName Then
                ' Do not unzip to a folder named *.cab or *.zip.
                ' Strip extension.
                Destination = FileSystemObject.BuildPath( _
                    FileSystemObject.GetParentFolderName(Destination), _
                    FileSystemObject.GetBaseName(Destination))
            End If
        Else
            ' Unzip to a subfolder of the folder of the zipfile.
            Destination = FileSystemObject.BuildPath(ZipPath, ZipName)
        End If
            
        If FileSystemObject.FolderExists(Destination) And Overwrite = True Then
            ' Delete the existing folder.
            FileSystemObject.DeleteFolder Destination, True
        End If
        If Not FileSystemObject.FolderExists(Destination) Then
            ' Create the destination folder.
            FileSystemObject.CreateFolder Destination
        End If
        
        If Not FileSystemObject.FolderExists(Destination) Then
            ' For some reason the destination folder does not exist and cannot be created.
            ' Exit.
            Destination = ""
        Else
            ' Destination folder existed or has been created successfully.
            ' Resolve relative paths.
            Destination = FileSystemObject.GetAbsolutePathName(Destination)
            Path = FileSystemObject.GetAbsolutePathName(Path)
            ' Check file extension.
            If FileSystemObject.GetExtensionName(Path) = ZipExtensionName Then
                ' File extension is OK.
                ZipTemp = Path
            Else
                ' Rename the zip file by adding a zip extension.
                ZipTemp = Path & ZipExtension
                FileSystemObject.MoveFile Path, ZipTemp
            End If
            ' Unzip files and folders from the zip file to the destination folder.
            ShellApplication.Namespace(CVar(Destination)).CopyHere ShellApplication.Namespace(CVar(ZipTemp)).Items, OverWriteAll
            If ZipTemp <> Path Then
                ' Remove the zip extension to restore the original file name.
                FileSystemObject.MoveFile ZipTemp, Path
            End If
        End If
    End If
    
    Set ShellApplication = Nothing
    Set FileSystemObject = Nothing
    
    If Err.Number <> ErrorNone Then
        Destination = ""
        Result = Err.Number
    ElseIf Destination = "" Then
        Result = ErrorOther
    End If
    
    UnZip = Result
     
End Function

Open in new window

/gustav
0
 
als315Commented:
Is it possible to use file creation date (Date modified in screenshot)?
What operationg system is used? What can we use for unzip?
Is it possible to use powershell?
0
 
bfuchsAuthor Commented:
Hi,
@Als,
Is it possible to use file creation date (Date modified in screenshot)?
Yes
What operationg system is used? What can we use for unzip?
Win7 Pro & up, guess the built in version.
Is it possible to use powershell?
Need instruction how to call it in from my VBA code.

@Gustav,
Will test yours.
How do I get to know the default download folder?
This should be stored somewhere as all browsers get to know it..

Thanks,
Ben
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
@Gustav,

Use Dir or FileSystemObject to locate the latest file.
Lets say I find a way to discover the default download folder, then how do I get the latest downloaded file?

Tested your code with following
Public Sub testunzip()
Dim path As String, destination As String, result As Variant
path = "C:\Users\bfuchs\Downloads\Tables_2017-Oct-31_2134.zip"
destination = "C:\Application\Tables.MDB"
result = UnZip(path, destination, True)
End Sub
' Select Early Binding (True) or Late Binding (False).
#Const EarlyBinding = False

Open in new window

1- when first tested I got the attached error.
2- Then tried renaming existing c:\application\Tables.mdb and running again, no error but no file created.

'       If True, an existing folder will first be deleted, then recreated.
Just to make sure, I dont want the entire folder deleted, only the particular file specified..!

Thanks,
Ben
Untitled.png
0
 
Gustav BrockCIOCommented:
A similar setup works right away here.
But be careful writing to the C drive outside your user folders; probably you don't haven't been granted the rights to write to that folder.

If a file is specified as the Destination, only that file will be overwritten.

/gustav
0
 
bfuchsAuthor Commented:
Hi,

Actually you're right, I tried using the FileCopy "C:\Application\tables1.mdb", destination and also receiving an error?
But I do need this copied under C:\Application.
What are my options?
How can I be granted rights to this folder (in my own pc..)?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Let me explain the purpose of this.

Its a file that being downloaded and imported into Access.

Now I have to link Access to a fixed folder something like C:\NameOfFixedFolder
Cant have same file linked by someone to C:\Users\Bfuchs and by Someone else C:\Users\HisName..

What do you suggest?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi Gustav,
Not sure if this is related (or this what caused it-:), but when you have a chance, please take a look.
https://www.experts-exchange.com/questions/29066015/getting-error-file-exists-while-copying-new-file.html

Thanks,
Ben
0
 
als315Commented:
Open explorer, find your folder (c:\Application), select it's properties with right button click on it. Select security tab, and in add "modify" permission to yourself (or to Users group).
Details you can find here:
https://www.wikihow.com/Change-File-Permissions-on-Windows-7
0
 
Gustav BrockCIOCommented:
A safe place, that by default is accessible for all users, is:

    C:\Users\Public

So I would create a subfolder here and use that for your files.

/gustav
0
 
bfuchsAuthor Commented:
Hi  Experts,

I finally figured out whats going on..
Gustav's code creates a folder with the name specified..see attached.
So I guess the unzip is working for C:\Application as well.

Will do some testing & keep you posted.

Btw, If you do have a way to accomplish the following I would appreciate.
1-Find out the default download location
2-Find out last saved file in specific folder

Thanks,
Ben
Capture.PNG
0
 
Gustav BrockCIOCommented:
The download location should be:

    C:\Users\UserName\Downloads

or:

    Environ("userprofile") & "\Downloads"

/gustav
0
 
bfuchsAuthor Commented:
Hi,

Any objection for using the following for default download folder?
https://stackoverflow.com/questions/23070299/get-the-windows-download-folders-path
And the below for latest file in folder?
https://stackoverflow.com/questions/25490868/finding-latest-file-in-a-folder-and-opening-it-vba-access

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Thank you.
0
 
Gustav BrockCIOCommented:
For those interested, an article describing the UnZip (and Zip) functions is now up:

Zip and unzip files and folders with VBA the Windows Explorer way

/gustav
0
 
bfuchsAuthor Commented:
@Gustav,

Here is my final code
Its supposed to take from the download folder and unzipped to C:\Application folder, and then replace the old Tables.MDB with this unzipped one.
Do you see any need for improvements?
(Just trying to prevent another catastrophically accident..)
    Dim stDocName As String
    Dim dLastBak As Date
    Dim sLastFile As String
    Dim sDownloadFolder As String
    Dim sBackupFolder As String
    Dim sDestinationFolder As String
    
'    sBackupFolder = "E:\CaspioBackups\"
'    sLastFile = NewestFile(sBackupFolder, "*.zip")
    sDestinationFolder = "C:\Application\"
'    If FileDateTime(sBackupFolder & sLastFile) < Date Then
        sDownloadFolder = DownloadsFolder & "\"
        sLastFile = NewestFile(sDownloadFolder, "*.zip")
'        FileCopy sDownloadFolder & sLastFile, sBackupFolder
        UnZip sDownloadFolder & sLastFile, sDestinationFolder, False
    sDestinationFolder = "C:\Application\"
    If Dir(sDestinationFolder & NewestFile(sDestinationFolder, "*.mdb")) <> "" Then
        Kill sDestinationFolder & "Tables.MDB"
        Name sDestinationFolder & NewestFile(sDestinationFolder, "*.mdb") As sDestinationFolder & "Tables.mdb"
    End If

Open in new window


Thanks,
Ben
0
 
Gustav BrockCIOCommented:
If it works, then OK. But you have this twice:

sDestinationFolder = "C:\Application\"

/gustav
0
 
bfuchsAuthor Commented:
Hi,

If it works, then OK
Yeah, just would like to take extra precautions in case something doesnt goes as planned..
But you have this twice:
Yes, The reason because your function being By Ref modified the contents of that variable sDestinationFolder..It took me a while to get it..

Thanks,
Ben
0
 
Gustav BrockCIOCommented:
That's for you to capture where the destination actually is.

If you don't want that, call:

UnZip sDownloadFolder & sLastFile, (sDestinationFolder), False

Open in new window

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.