By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Having a real funny problem.

100+
P: 443
Here's my problem, I have in my application a backup procedure that creates .txt files for each table. I use the docmd.transfertext to export the data. This creates a number of .txt files. I decided to zip those files up so that I only have a .zip file instead of many .txt files. I'm using Ken Jensen zip module, there are four modules basZip, khZip, khZipfile and khZipfiles this is working fine on 95% of my users however there a a couple that have a problem. Everything goes fine until the very last and then they get an error message that the zip file is empty. Now here comes the funny part. I've run the program on there computer and have stopped the code so I could step it through, when I do the step through it works fine but when I run it straight through I get the error. When I get the error the zip file isn't created, when I step through the code it works and I get the zip file.
I'm wondering if anyone has seen this type of problem, or maybe there is a better way of doing the zipping up instead of using Ken Jensens zip modules.
Thanks for any advise and help
Feb 1 '18 #1

✓ answered by NeoPa

Hi Tom.

I don't know your Zipping program so all I can say at this point is that Shell(), and ShellWait(), are both calls that allow you to run programs in the same way as you might from a Command Prompt. That means a BAT or CMD file can be executed from there. Do you have, or can you create, such a file to execute the task you need to execute?

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,411
It sounds like you may be suffering from some sort of race condition. Something that you rely on to be ready at such and such a point isn't.

Usually VBA is single-threaded so it's hard to guess what that might be, but adding DoEvents() function calls between files may well help.

Good luck Tom.
Feb 2 '18 #2

100+
P: 443
Is that all there is to the command is DoEvents().
Feb 2 '18 #3

NeoPa
Expert Mod 15k+
P: 31,411
CD Tom:
Is that all there is to the command is DoEvents().
I've no idea what you're trying to ask.
Feb 2 '18 #4

PhilOfWalton
Expert 100+
P: 1,430
Butting in, Tom is asking if there are any parameters with DoEvents() and the answer is no (and you don't even need the brackets).

Expand|Select|Wrap|Line Numbers
  1. DoEvents
  2.  
Phil
Feb 2 '18 #5

100+
P: 443
I guess I figured that out, but I've tried everything to get this to work, by putting the DoEvents in many different places, But nothing seems to work. I can't seem to find where the error message is coming from. The following code is where I think the error is happening but like I said when I step through the process it works just fine.
Expand|Select|Wrap|Line Numbers
  1. Me.ZipFilePath_Temp = "C:\Temp\" & Right$(Me.ZipFilePath, Len(Me.ZipFilePath) - InStrRev(Me.ZipFilePath, "\"))
  2.  
  3.         NewZip Me.ZipFilePath_Temp
  4.  
  5.         If Right$(Me.ZipFolderPath, 1) <> "\" Then
  6.             Me.ZipFolderPath = Me.ZipFolderPath & "\"
  7.         End If
  8.  
  9.         Set objShell = CreateObject("Shell.Application")
  10.         Set objFolder = objShell.Namespace(Me.ZipFilePath_Temp)
  11.         Set objFolder2 = objShell.Namespace(Me.ZipFolderPath)
  12.  
  13.         objFolder.CopyHere objFolder2.Items
  14.         'Keep script waiting until Compressing is done
  15.         On Error Resume Next
  16.         Do Until objFolder.Items.Count = objFolder2.Items.Count
  17.             DoEvents
  18.         Loop
  19.  
  20.         On Error GoTo Err_ZipAll
  21.  
  22.         If Len(Dir(Me.ZipFilePath)) > 0 Then
  23.             Kill Me.ZipFilePath
  24.         End If
  25.         Name Me.ZipFilePath_Temp As Me.ZipFilePath
  26.     End If
  27.  
Any ideas where to slow this down.
Feb 2 '18 #6

NeoPa
Expert Mod 15k+
P: 31,411
Hi Tom.

This is the first time we've seen your code, which is strange considering this is what you're asking for help with.

My first comment would be that using scripting to handle this is unnecessary. There is a Shell() function available in VBA that allows you to run Command Line commands. I have also written, for situations just such as this where it's important to ensure the process is completed before allowing the VBA to continue, a version called ShellWait().

My second comment would be that you have inserted the DoEvents() calls into your code intelligently so if that isn't working then that doesn't seem to be the solution.

We all have our own preferred ways of doing things and I prefer to use standard VBA calls where they're available rather than scripting objects.
Feb 2 '18 #7

100+
P: 443
Thanks for your response. Seeing this is the first time I've tried to do anything like zipping up files I didn't have any idea what to do. I looked and found what I thought was the best answer, but I guess I'm finding out it wasn't. I'm not sure VBA calls to use, Shell() and shellWait() sound like a good start. Where can I find more information on how to use these with my zipping problem. I appreciate all your help and information. I keep learning things from you guys. Thanks lots.
Feb 2 '18 #8

NeoPa
Expert Mod 15k+
P: 31,411
Hi Tom.

I don't know your Zipping program so all I can say at this point is that Shell(), and ShellWait(), are both calls that allow you to run programs in the same way as you might from a Command Prompt. That means a BAT or CMD file can be executed from there. Do you have, or can you create, such a file to execute the task you need to execute?
Feb 3 '18 #9

100+
P: 443
Ok, I've been trying different ways to do this zipping up and here's the code I've found to work.
Expand|Select|Wrap|Line Numbers
  1. Dim objFSO As Object, objZip As Object, objShell As Object
  2. Dim objFolder As Object, objFile As Object
  3. Dim sngStart As Single
  4. Dim strPath As String, strZip As String
  5. strPath = FolderName
  6.  
  7. strZip = strbkfoldername  'Output zip file
  8.  
  9. Set objFSO = CreateObject("Scripting.FileSystemObject")
  10. Set objZip = objFSO.CreateTextFile(strZip)
  11. objZip.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
  12. objZip.Close
  13.  
  14. Set objShell = CreateObject("Shell.Application")
  15. Set objFolder = objFSO.GetFolder(strPath)
  16. DoCmd.Hourglass True
  17.     'loop through files - adding them to the
  18. For Each objFile In objFolder.Files
  19.     If objFile <> strZip Then
  20.     objShell.Namespace("" & strZip).CopyHere objFile.Path
  21.     'objShell.Namespace("" & strZip).MoveHere objFile.Path
  22.     sngStart = Timer
  23.     Do While Timer < sngStart + 1
  24.         DoEvents
  25.     Loop
  26.     End If
  27. Next
  28. DoCmd.Hourglass False
  29.  
This works great when I select a disk drive. But if I select a jump drive I keep getting this message everytime it gets a file to zip. "Please insert the last disk of the Multi-volume set and click ok to continue"
There are 54 files that are being .copyhere so you end up clicking the OK 54 times. Can you see any way to eliminate this message?
Thanks again for any help

I've tried the .movehere also and get the same message.
Feb 4 '18 #10

Post your reply

Sign in to post your reply or Sign up for a free account.