What's wrong with VB ?

[Sreedhar]

This problem is bugging me for quite some time. This is a sort of re-post, as
I had posted it ealier.

I have to import zipped text files into Access, which are sent to the server
via ftp.

The file extension is '*.txt.gz'

gzip.exe is the utility used for extracting the files, which is proving to
be very VBA-unfriendly. Good old DOS based foxpro is able to handle this
situation very effectively, but not my VBA Code in Access.

Extracting one file is working fine, but I have to extract several files in
the same folder/different folders. VBA is very hasty and not stopping until
the exe finishes its job and jumping to execute the next statement. I've
tried 'ShellWait' function found at access.mvps.org but to no avail.

My earlier post ended in an unfinished search for an alternative solution.

Any help is really appreciated. Thanks in advance.

 

[Douglas J. Steele]

What does your VBA look like?

 

[Albert D. Kallal]

I have little, or no idea as to why your shell wait is not working
correctly....

Also, can you get the files in zip, or are they gz files beyond your
control?

You could use WinZip example here:

I have a zipping routine here:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm

The above runs without you having to install winzip.
(and, you don't have to use shell coomands to
pkzip or installing pkzip).

Howver, the above only works for .zip...and not gz.....

I think you back to getting the shell command to "wait" for you....

 

[Klatuu]

Albert,
I downloaded and used that a few months ago. Works great.
We have about 100 zip files come in to an FTP site every day. Someone had
to move them to a working directory, unzip them to another directory. They
are .dat files then use Pervasive SQL to export a text file to another folder.

I used your Unzip as part of the solution. It now runs as a scheduled task
every day.

 

[Sreedhar]

What does your VBA look like?

Hi,

I'm using two functions and a sub-routine to get the job done.

The function "GetFileFromServer" returns the path to the gz file on the
server and it is working fine.

The function "UnZippedFile" is where the shell is used and it is working for
a single file.

The Sub "ImportBalanceFiles" is the one that is throwing error on the line
that is highlighted, because "UnZippedFile" is failing if asked to extract
more than one file.

Here is my code:


Public Const G_ZIP As String = "C:\WINDOWS\system32\gzip.exe"
Public Const REPORT_PATH As String = "\\hsvsr1\cpp\reports\"
Public Const SHADOW_PATH As String = "\\hsvsr1\cpp\shadowfiles\"
Public Const BAL_FILE_NAME As String = "CC_OD_Balance_File_depd0580.txt.gz"
Public Const TXN_FILE_NAME As String = "LoansBalanceFile-lond2390.txt.gz"
Public Const TRD_SHADOW_NAME As String = "DEP_Shadow_file.txt.gz"
Public Const LED_SHADOW_NAME As String = "LON_Shadow_file.txt.gz"

Public Enum FILE_TO_EXTRACT
BAL_FILE
TXN_FILE
TRD_SHADOW
LED_SHADOW
End Enum

Public Function UnZippedFile(strZippedFile As String) As String
On Error GoTo Err_UnZippedFile

'Purpose: To copy the file from server to local directory and,
' to extract Text File from gz file.

'Argument: Path to the file on the server as a string

'Returns: Path to the local copy of the file that is unzipped.

Dim fs As Object
Dim strFile() As String
Dim strLocalCopy As String
Dim strUnZippedFile As String
Dim i As Integer

strFile = Split(strZippedFile, "\")

For i = 0 To UBound(strFile)
strLocalCopy = strFile(i)
Next i

strLocalCopy = CurrentProject.Path & "\" & strLocalCopy
strUnZippedFile = Left(strLocalCopy, Len(strLocalCopy) - 3)

If Len(Dir(strLocalCopy)) > 0 Then Kill strLocalCopy
If Len(Dir(strUnZippedFile)) > 0 Then Kill strUnZippedFile

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile strZippedFile, CurrentProject.Path & "\"

Call ShellWait(G_ZIP & " -d " & strLocalCopy, vbNormalFocus)
DoEvents

If Len(Dir(strUnZippedFile)) > 0 Then
UnZippedFile = strUnZippedFile
Else
UnZippedFile = vbNullString
End If

Exit_UnZippedFile:
Exit Function

Err_UnZippedFile:
ErrHandler Err.Number, Err.Description, "UnZippedFile"
Resume Exit_UnZippedFile

End Function









Public Function GetFileFromServer(FileToExtract As FILE_TO_EXTRACT) As String


'Purpose: Locate the file on the server and return the path

'Argument: The type of file that is to be searched for (enum - long)

'Returns: The path to the gz file on the server (string)





Public Sub ImportBalanceFiles()
On Error GoTo Err_ImportBalanceFiles

Dim BALInput As String
Dim BALOutput As String
Dim TXNInput As String
Dim TXNOutput As String

'Other variables declared

BALInput = UnZippedFile(GetFileFromServer(BAL_FILE))
TXNInput = UnZippedFile(GetFileFromServer(TXN_FILE)) <<---------####

'Further code is failing , because TXNInput is empty and not getting its
value
'from the "UnZippedFile" function.

 

[Sreedhar]

Also, can you get the files in zip, or are they gz files beyond your
control?

Yes, I have no control here and the files are gz only.

I have little, or no idea as to why your shell wait is not working
correctly....

I have posted my code here in reply to Mr.Douglas J. Steele's reponse. Can
you please have a look at it and tell me what's wrong ?

 

[Larry Linson]

I have posted my code here in reply to Mr.Douglas J. Steele's
reponse. Can you please have a look at it and tell me what's
wrong ?

I suspect most of the participants here are not familiar with, and don't
know the specifications for, the program you use to extract the uncompressed
file from the .gz files. Nor, sad to say, do most of us have time to delve
into other software to answer a question in the newsgroup.

You did not list ShellWait as one of the Functions, Subs, or Programs you
were using. Were you under the impression that it was a native Access VBA
funtion/sub?

However, I did a quick search on ShellWait, and, lo, I note there are
several mentions of trouble with that code under Windows Vista. Could it be
that you are running Vista?

I know Terry Kreft, who apparently is the author of ShellWait, by
reputation, and his reputation is a good one. But, I don't think Terry has
been participating in the Access newsgroups for several years -- or has not
been participating very much. So it's entirely possible that some of the
changes in the OS may not be compatible with code that he wrote some time
ago.

One alternative would be to determine if there are parameters or arguments
that you can pass to G_ZIP to "extract all the files from this .gz". You
will have to find the detailed documentation and might have to contact the
author(s) of G_ZIP.

Larry Linson
Microsoft Office Access MVP

 

[Douglas J. Steele]

Hi,

I'm using two functions and a sub-routine to get the job done.

The function "GetFileFromServer" returns the path to the gz file on the
server and it is working fine.

The function "UnZippedFile" is where the shell is used and it is working
for
a single file.

The Sub "ImportBalanceFiles" is the one that is throwing error on the line
that is highlighted, because "UnZippedFile" is failing if asked to extract
more than one file.

Presumably you're telling "UnZippedFile" to extract more than one file by
passing it a string of file names separated by backslashes (\).

In your code, you're using the Split function to unpack that list of file
names into an array. However, all your function does is work with the last
file name passed. Once the following loop has finished execution,
strLocalCopy contains the name of the last entry in strFile only.

For i = 0 To UBound(strFile)
strLocalCopy = strFile(i)
Next i

I would think that all the rest of the code following that block needs to be
inside the loop. Presumably you'll have to pass back a backslash-delimited
list of files extracted.

 

[Sreedhar]

Presumably you're telling "UnZippedFile" to extract more than one file by
passing it a string of file names separated by backslashes (\).

That function is passed a string value which is the path to a 'single file'.
I'm splitting the path to catch the 'actual file name only' for other
purposes. Hence, the loop. The variable strLocalCopy is expected to hold only
the last entry in strFile only.

My code is failing because UnZippedFile is called in quick succession :

BALInput = UnZippedFile(GetFileFromServer(BAL_FILE))
TXNInput = UnZippedFile(GetFileFromServer(TXN_FILE)) <<---------####

As you can see, I'm trying to extract two different files by calling
UnZippedFile once for each one.

The first line is working as expected. The second is failing for reasons
unknown to me. If I call the function once, it is working very well. If I
step through the code, both lines are working fine. Hence, my feeling that
the problem lies with the Shell or ShellWait.

Many thanks for patiently going through my code.

 

[Sreedhar]

"Sreedhar" <[email protected]> wrote

I suspect most of the participants here are not familiar with, and don't
know the specifications for, the program you use to extract the uncompressed
file from the .gz files.

My question is not about software for extracting the compressed files. It is
about controlling the execution of VBA while shelling out to other programs.

You did not list ShellWait as one of the Functions, Subs, or Programs you
were using. Were you under the impression that it was a native Access VBA
funtion/sub?

My first post clearly mentions 'ShellWait' as been taken from access.mvps.org
I pasted it in its own module and didn't think necessary to post all that
code along with mine. I know it is a workaround for the native Access VBA
'Shell'.

Nor, sad to say, do most of us have time to delve
into other software to answer a question in the newsgroup.

Thanks anyway.

 

[John W. Vinson]

That function is passed a string value which is the path to a 'single file'.
I'm splitting the path to catch the 'actual file name only' for other
purposes. Hence, the loop. The variable strLocalCopy is expected to hold only
the last entry in strFile only.

My code is failing because UnZippedFile is called in quick succession :

BALInput = UnZippedFile(GetFileFromServer(BAL_FILE))
TXNInput = UnZippedFile(GetFileFromServer(TXN_FILE)) <<---------####

As you can see, I'm trying to extract two different files by calling
UnZippedFile once for each one.

The first line is working as expected. The second is failing for reasons
unknown to me. If I call the function once, it is working very well. If I
step through the code, both lines are working fine. Hence, my feeling that
the problem lies with the Shell or ShellWait.

Many thanks for patiently going through my code.

You might try putting DoEvents in between the lines... not sure it will help
but worth a try.

 

[Douglas J. Steele]

That function is passed a string value which is the path to a 'single
file'.
I'm splitting the path to catch the 'actual file name only' for other
purposes. Hence, the loop. The variable strLocalCopy is expected to hold
only
the last entry in strFile only.

My code is failing because UnZippedFile is called in quick succession :

BALInput = UnZippedFile(GetFileFromServer(BAL_FILE))
TXNInput = UnZippedFile(GetFileFromServer(TXN_FILE)) <<---------####

As you can see, I'm trying to extract two different files by calling
UnZippedFile once for each one.

The first line is working as expected. The second is failing for reasons
unknown to me. If I call the function once, it is working very well. If I
step through the code, both lines are working fine. Hence, my feeling that
the problem lies with the Shell or ShellWait.

Many thanks for patiently going through my code.

Gotcha.

The only thing I can think of is to put a breakpoint in the ShellWait
subroutine, and see whether it looks like it's working properly.

 

[Albert D. Kallal]

I have posted my code here in reply to Mr.Douglas J. Steele's reponse. Can
you please have a look at it and tell me what's wrong ?

The problem is I can't tell what is wrong because I have no idea what is
wrong!!

Larry has suggested that Vista might be a source of problems.

I would also suggest you check with the authors of the gs zip utility, as
see if they are aware of any known issues as to why using the windows shell
(api) does not wait for that program. They might some version of gs that is
compiled without using threading for example. So, do check with them, and
see what comes up. Looking at posts here, it not a general conclusion that
the shellwait fails to wait.

Is shellwait wait flashing by in an instant, and *no* delay occurs, or is it
"close" to when the shellwait finishes? So, in fact is there *some* delay
occurring here?

I ask because some systems with virus software often have a *delay* time
before the os is actually finished writing out the file (I having this
problem with ms-access and my update code that "copies" a file from the
server....the files is being shown as not available..but I know it has been
copied -- it the virus software..and I had to increase a wait/delay time
before I attempt to use the file.

So, shell wait might be finished correctly, but the os has not yet released
the file. I would consider placing a wait command, and perhaps wait 250
milliseconds (that is 1/4th of a second) *after* the shell code is done, and
you code then continues. Try the apiSleep right BEFORE your dovents (which
is a good idea that you have a doevents already right after the sheelwait).

The "sleep" wait command is found here:
http://www.mvps.org/access/api/api0021.htm

Another possible approach would be consider a windows script that does both
the copy, and the unzip for you. It might "better" wait until the whole
script is done. You would then sheelwait from access to the script passing
the file name...

 

[Sreedhar]

I ask because some systems with virus software often have a *delay* time
before the os is actually finished writing out the file (I having this
problem with ms-access and my update code that "copies" a file from the
server....the files is being shown as not available..but I know it has been
copied -- it the virus software..and I had to increase a wait/delay time
before I attempt to use the file.

So, shell wait might be finished correctly, but the os has not yet released
the file. I would consider placing a wait command, and perhaps wait 250
milliseconds (that is 1/4th of a second) *after* the shell code is done, and
you code then continues. Try the apiSleep right BEFORE your dovents.

After lot of debugging and finding nothing really flawed, I've stepped
through the code for the final time and verified the actual existence/status
of the file copy/extraction in the directory. And to my surprise, even though
my function is returning null string, the file is being actually copied and
being extracted. The only thing I can think of is what you suggested.

Instead of using the sleep sub, I've removed the following lines in my
'UnZippedFile' funtion where it returns value:

'If Len(Dir(strUnZippedFile)) > 0 Then <---Removed

UnZippedFile = strUnZippedFile

'Else <---Removed
'UnZippedFile = vbNullString <---Removed
'End If <---Removed

Having commented out the final check for the actual existence of the file
(because I know the file is already copied and extracted), I was able to run
my code without any problems and what a great relief it is !

I'll try the Sleep sub as well.

Many thanks.

 

[Sreedhar]

Gotcha.

The only thing I can think of is to put a breakpoint in the ShellWait
subroutine, and see whether it looks like it's working properly.

After a lot of debugging, I think the final check for the actual existence
of the file copied/extracted in my UnZippedFile is causing the trouble.
Though I would like to have it, commenting out those few lines made my life
easier. Thanks to Mr.Albert Kallal's advice, I was able to trace the origin
of my problem.

Thank you very much.