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

Extracting an Excel file from a zip folder using Access VBA

P: 2
I had the following line of code working to extract an Excel lfile from a temp.zip folder, but it stopped working about 2 months ago.

Set oApp = CreateObject("Shell.Application")
oApp.Namespace(ZipFilePath).CopyHere (ZipFilePath & "temp.zip\" & ExcelFileName)

The error message is "The file exists". Can anyone suggest why this would suddenly stop working? I am not sure what settings might have changed, but updates get rolled out in my company with users knowing.
Feb 12 '10 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
Looks like the overwrite stopped working, or your filename was different each time in the past.
When the file does exist, you can delete it before unzipping it by using the KILL command.
With the Dir() function you could check or the file name is already in present.

Nic;o)
Feb 14 '10 #2

ADezii
Expert 5K+
P: 8,597
To Unzip Test.xls, contained within Test.zip, the C:\Zips\ Folder to the C:\UnZips\ Folder as Test.xls:
Expand|Select|Wrap|Line Numbers
  1. Const conPATH_TO_ZIP_FILE As String = "C:\Zips\Test.zip"
  2. Const conUNZIPPED_PATH As String = "C:\UnZips\"
  3. Const conFILE_NAME As String = "Test.xls"
  4. Dim oApp As Object
  5.  
  6. If Dir$(conUNZIPPED_PATH & conFILE_NAME) <> "" Then
  7.   Kill conUNZIPPED_PATH & conFILE_NAME
  8.  
  9.   Set oApp = CreateObject("Shell.Application")
  10.  
  11.   oApp.Namespace(conUNZIPPED_PATH).CopyHere _
  12.   oApp.Namespace(conPATH_TO_ZIP_FILE).Items.Item(conFILE_NAME)
  13. End If
Feb 14 '10 #3

P: 2
@ADezii
I tried copying your code exactly and I am still having problems. I can extract the excel file from a regular folder, but it gives me the error message when I try to extract from a zip folder.
The frustrating part is this worked fine for me a few months ago and then suddenly stopped around Christmas. I am wondering if a setting has changed on my computer or Access references.
Mar 2 '10 #4

Post your reply

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