Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

List Contents of zip file including folders / subfolders using VBA?

Hi,

I'm trying to create some code that allows the user to select *.zip file(s) and then have the contents of this file listed in a worksheet in Excel.

So far I have some code, but it fails for this reason - it will only display the 1st layer of files / folders - i.e if anything is within a folder inside the zip file, only the folder name is listed, not the individual files within that folder and all subsequent sub-folders.

The code allows the user to select a folder, and then lists contents of all zip files within that folder. The ListZipDetails Subroutine is where I have the issue, the other items are needed to run this.


	VB:
	
    hOwner As Long 
    pidlRoot As Long 
    pszDisplayName As String 
    lpszTitle As String 
    ulFlags As Long 
    lpfn As Long 
    lParam As Long 
    iImage As Long 
End Type 
 
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ 
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long 
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ 
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long 
Function GetFolderName(Msg As String) As String 
     ' returns the name of the folder selected by the user
    Dim bInfo As BROWSEINFO, path As String, r As Long 
    Dim X As Long, pos As Integer 
    bInfo.pidlRoot = 0& ' Root folder = Desktop
    If IsMissing(Msg) Then 
        bInfo.lpszTitle = "Select a folder." 
         ' the dialog title
    Else 
        bInfo.lpszTitle = Msg ' the dialog title
    End If 
    bInfo.ulFlags = &H1 ' Type of ucase(Dir)ectory to return
    X = SHBrowseForFolder(bInfo) ' display the dialog
     ' Parse the result
    path = Space$(512) 
    r = SHGetPathFromIDList(ByVal X, ByVal path) 
    If r Then 
        pos = InStr(path, Chr$(0)) 
        GetFolderName = Left(path, pos - 1) 
    Else 
        GetFolderName = "" 
    End If 
End Function 
 
 
Sub ListZipDetails() 
    Dim FSO As Object 
    Dim oApp As Object 
    Dim Fname As Variant 
     ' Dim FileNameFolder As Variant
     ' Dim DefPath As String
     ' Dim strDate As String
    Dim fileNameInZip As Variant 
     
    i = 2 
     
    mypath = GetFolderName("Select Folder where Data Files are stored") 
     
    If mypath = "" Then 
        Exit Sub 
    End If 
     'Fcount = CountFiles(mypath, "txt")
    mypath = mypath & "" 
     
     
    Fname = Dir(mypath) 
     
    Do While Fname  "" 
         
        If UCase(Fname) Like "*.ZIP" Then 
             
             
            Set oApp = CreateObject("Shell.Application") 
             
             
            For Each fileNameInZip In oApp.Namespace(mypath & Fname).Items 
                Range("A" & i).Value = Fname 
                Range("B" & i).Value = fileNameInZip 
                 
                i = i + 1 
            Next 
            Set oApp = Nothing 
        End If 
         
        Fname = Dir 
    Loop 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks to Ron de Bruin for the inspiration for this thus far.

If anyone has any help to offer on this, I'd really appreciate it - or any other ways of listing contents of zip files into Excel.

Many thanks

Chris


Post your answer or comment

comments powered by Disqus
hi guys,

is it possible to use the .BuiltinDocumentProperties for zip files?
or maybe i used the wrong structure for the code?

any help or suggestion is much appreciated
thanks

	VB:
	
 Available() 
    Dim p As String, x As Variant 
     
    p = "D:*.zip" 
    x = GetFileList(p) 'function to lists all zip files in a folder
    Select Case IsArray(x) 
    Case True 'files found
         'MsgBox UBound(x)
        Sheets("Available").Range("A:B").Clear 
        For i = LBound(x) To UBound(x) 
            Sheets("Available").Cells(i, 1).Value = x(i) 
             
            Sheets("Available").Cells(i, 2).Value = x(i).BuiltinDocumentProperties("Creation date") 'here's my problem
        Next i 
    Case False 'no files found
         'MsgBox "No matching files"
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello All,

I tried to write some VBA code to loop through all the files in a folder and return the name of the file. (In my current example, all the files are excel workbooks)

Here is the code I have used:


	VB:
	
 Load_List() 
     
     
     
     
    Dim lCount As Long 
    Dim wbResults As Workbook 
    Dim wbCodeBook As Workbook 
     
     
    Set wbCodeBook = ThisWorkbook 
     
    With Application.FileSearch 
        .NewSearch 
         'Change path to suit
        .LookIn = "G:CFOMiddle OfficeDannyFine Tunning for JP" 
        .FileType = msoFileTypeExcelWorkbooks 
         
         
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 
                 
                Set wbResults = Workbooks.Open(.FoundFiles(i)) 
                 
                MsgBox wbResults.Name 
                 
                 
            Next lCount 
             
        End If 
         
    End With 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The problem I had with this code is, it needs to open the workbook for every file, which make the code slow.

Is there a way that I can return the name of the file without openning the workbook?

I have googled for quite a while and got the following code:


	VB:
	
 Get_Names() 
     
     
     
    Dim FSO As FileSystemObject 
    Dim FilesDir As Folder 
    Dim CurFile As File 
     
     'Create a variable to store the folder holding the files
    Set FSO = New FileSystemObject 
     
     'Change the string here to look in a different folder (highly recommended :P)
    Set FilesDir = FSO.GetFolder("G:CFOMiddle OfficeDannyFine Tunning for JP") 
     
     
     
     
     'Loop through all of the files in the folder
     
    For Each CurFile In FilesDir.files 
         
         'If the file begins with the word "budget" then rename it
        If CurFile.Name  "Financial Analysis Master File.xls" Then 
             
            Cty_list.AddItem CurFile.Name 
             
        Else 
             
             
        End If 
         
    Next 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, when I tried to run the code, it give me the error message saying: the defined type of the variable are not been defined. Seems to me, that VBA dont have the variable type as Folder, or File.

How could I do this??

Any suggestions will be really appreciated.

Thanks

How can I use VBA to copy data from other excel files and place them into sheet1 of my main excel file?

I have a main file (maintest.xls) in the directory C:Excel and have three excel files (test1.xls, test2.xls, and test3.xls) in the folder C:ExcelData

Each of the three test files only have 1 sheet of data, "sheet1". Each sheet has a heading row where A1 contains "Data" and B1 contains "Value". Underneath these header rows, there's an unknown number of rows of data with text in Column A and numbers in Column B. Ideally, I'd like to just copy the data and not the heading rows.

I need to come up with code that will copy the contents of *.xls in the folder C:ExcelData and paste them into sheet1 of maintest.xls in the C:Excel folder. I can run the script from the maintest.xls sheet, but don't have a clue where to begin coding this.

This is my first VBA project and I'm pretty unfamiliar with the code. I plan to learn this but am unfortunately in a time crunch. Any and all help is appreciated, thanks!

Is it possible to Compress a Folder/File or create a Zip File Dynamically?

I have realized that VBA has a great potential and I have managed to create Files and Folders Dynamically using VBA coding..ofcourse with a lot of great help from the forum..

Going forward in saving time is it possible to Compress the Created Folders or any any Folders in a Given Path..

Like a WinZip file which can be an email attachment or on Pen-Drive..

Warm Regards
e4excel

I have a directory of zip files which I want to calculate the (gross) file
size of. Does anyone here know of a way to expose this information using VBA?

--
Chris Jones

I have 46 files with identical column headings and rows and I have to summarise the contents of each file into one file. Is there a way to merge these files automatically in one file? What I want is the data from the same cell addresses in each file to be added and put into the same cell addresses of a single new file. I have tried linking the files, but the number of rows and columns run into hundreds.

Can this be done? I will appreciate any help.

Hi everyone,
How do I view a zip file in folder view? When I double click on it, I see all the files in the zip and they aren’t organized by folders. I zipped a few folders with sub folders inside. When I open the zip, they all appear unorganized. How can I view the zip in folder view?Thanks

Hi-

I have a folder with multiple excel files in it and currently have a
macro that goes through each file and pulls certain data into Access.
The problem I have is that this folder is dynamic. The names and the
number of files varies from month to month. The way my macro works now
is it opens a specific file....runs the macro....and then closes the
file. I have about 60 individual instructions for each of the files.
Open specific file.....call Macro....close file.....open next
file....call Macro.....close file.....etc.

My question is this: Is there a way...using VBA...to point to a
specific folder and run a macro for EVERY file in that folder?? I
don't want to tell Excel what file to open....I just want it to open
every file in the folder....run this macro against each file....and
then close the files.

Anyway this can be done or do I have to be specific in what I tell
Excel to open??

Thanks in advance!!

-Chris

Hi All,

I'm trying to change the contents of a cell in another worksheet using VBA. In sheet1, I have tried

However, this isn't working 

Can someone point me in the right direction.

Thanks in advance

I am new to VBA and trying to delete particular file format using VBA in folder and subfolders.
My folder has all type of file format including csv and also has subfolders; each subfolder has csv files and subfolders.
How do I delete all .csv files in a folder and its subfolders using VBA? Thanks in advance.

I'm using a piece of code I found here on Ozgrid to create a list of files in a specific folder. And even though this worked fine for me a week ago, now when I run this same macro, the .zip files are left out of the list, even though I'm specifying msoFileTypeAllFiles. Archive files with other extensions, such as .tar.gz, do make the list.

I get the feeling that my zip files are being considered folders instead of files. Do you have any idea why this is happening, or what to do to get that list of zip files?


	VB:
	
 HyperlinkXLSFiles() 
    Dim lCount As Long 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
     
    On Error Resume Next 
     
    With Application.FileSearch 
        .NewSearch 
         'Change path to suit
        .LookIn = "o:DataFeeds" 
        .FileType = msoFileTypeAllFiles 
         
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(lCount, 1), Address:= _ 
                .FoundFiles(lCount), TextToDisplay:= _ 
                Replace(.FoundFiles(lCount), "o:DataFeeds", "") 
            Next lCount 
        End If 
    End With 
     
    On Error Goto 0 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

I need to find a way to rename a excel .csv file in a specific folder based on the content of a specific cell.

i.e.:

I have a folder with 300 CSV files named with non-consistent string (letters and numbers)

I want each of these files to be renamed with the content of the cell in D2 of the file.

Would anyone knows a VBA code I could use to make this easier than opening every file and saving it under a new name?

Many thanks!

Hi,

I have a list of excel (2010 version) files in a folder (eg: c:tempwork). The files in this list are all in the same format and contain 1 sheet in each file.

I need a VBA to import the data in each file, found in the folder above, into one worksheet, listing the contents of each file, and then save the new file with the combined data. However, the code needs to be dynamic in the same that the number of files and the filenames change, therefore, the code has to automatically pick up the list of files in the folder.

Can anyone help, please.

regards,
makku

I have managed to find code to create a zip file and add a text file to the zip file, however when its complete, I get this message pop up:

"WinZip encountered problems during this operation.
Would you like to view the detailed results of the last operation showing the specific error(s) encountered?"

When I click "Yes", it says:

"Action: Add (and replace) files Include subfolders: yes Save full path: no
Warning: unexpected characters at end of Zip file ignored
Adding Test.txt
copying Zip file"

Questions:
1) Is this something I should be worried about?
2) If not, how can I get this message NOT to pop up?

I am brand new to programming. I have only read two text books on it and am having difficulty figuring out how to do this.

I have files saved with only the mm-dd-yy date format, and I want to rename the files with the yyyy-mm-dd date format. I have created a spreadsheet with the first column with all the dates from 9-1-10 - 10-31-11. The second column has the same dates in the new date format. My files don't use every date in that range, but I included all dates so I didn't have to manually enter specific dates. I'm not even sure if this is the best approach, or if there is a way to loop through the folders and change the date format. All of the files are in subfolders under a large folder located at c:test

Any suggestions?

Hi,

Is there any way I can put the whole content of different multiple .txt files (practically the content of all .txt file in one folder) into Excel 2010? I need one cell (A1) to be the name of the file, and the other cell (A2) to be the whole content of that .txt file. The same goes for the other .txt files, i.e. B1-B2, C1-C2, etc.

Searching the forums, I already found this code:


	VB:
	
 test() 
    Dim myDir As String, fn As String, ff As Integer, txt As String 
    Dim delim As String, n As Long, b(), flg As Boolean, x 
    myDir = "c:test" '

Q1. Using VBA, I need to know the name of a database file which will be unzipped from DB.zip file.

Does anyone know if there anyway I can see the contents of a zip file without unzipping it with VBA ?

I will eventually unzip it with the excellent code provided by jonmo1 at http://www.mrexcel.com/forum/showthread.php?t=292760

Q2. Alternatively, if I unzip a file to a specific folder, where other files already exist, how would I find the name of the new file with VBA?

I am open to all suggestions and ideas, which are gratefully received

PS The file in the zip file may have a much older date of last modification, so testing for latest date modified will not work

Hi Guys,

I need a method of accessing all files within a folder to get the number of rows/records and contents of "A2".
The file could ideally look at the current folder it's in and collect the requested data.

Hopefully I can work and develop the code to suit.

Regards

Resul

Hi all,

I searched for this but was not able to find the required command...

Aim:
In the worksheet, I will specify a directory
Then, want to write command...
1-Specifieddirectory_GET LIST OF ALL FILES
2-I'll store the list of all files in memory
3-I'll open the first file in that directorry
4-I'll perform an operation to get the data i need from that file
5-i'll close that file
6-i'll open the next file and, etc.

To everyone:
What is the command for step1 above...
1-Specifieddirectory_GET LIST OF ALL FILES

Psanyone wanting to do the same thing and cant?)

Your help or insight is much appreciated!!
Thank you!

Greetings,

I have a text file being used as a log file. Sometime I need to clear this file when I start-up the UserForm.

I load this text file with this code.
First is this in Module1:

	VB:
	
 = "Log.txt" 
Public Const LOGFILE As Integer = 1 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This defines the text file to be added to.

The code that actully apends the text to the first open row of the text file is:

	VB:
	
) 
    Dim szOutput As String 
     
    If Not blnPL Then Exit Sub 
    If sPL = 1 Then 
        szOutput = "==================================================" 
    ElseIf sPL = 2 Then 
        szOutput = "--------------------------------------------------" 
    ElseIf sPL = 3 Then 
        szOutput = "++++++++++++++++++++++++++++++++++++++++++++++++++" 
    ElseIf sPL = 4 Then 
        szOutput = "**************************************************" 
    ElseIf sPL = 5 Then 
        szOutput = ".................................................." 
    ElseIf sPL = 6 Then 
        szOutput = "__________________________________________________" 
    ElseIf sPL = 0 Then 
         '   Construct the output string
        iPL = iPL + 1 
        szOutput = iPL & ") " & szName 
    End If 
     
     '   Open the file for appended output
    Open FILENAME For Append As #LOGFILE 
     '   Write the output to the file
    Write #LOGFILE, szOutput 
     '   Close the output file
    Close #LOGFILE 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And this is an example of how this sub is used to track the logic flow in a format that I can make large enough to see (visually chalenged):

	VB:
	
 Routine_Change() 
    Call PrintLog(" [" & CustName.ListIndex & _ 
    "][" & Routine.Value & _ 
    "] Start - Routine Value = " & _ 
    Routine.Value, 0) 
     
     'Some Code
     
    Call PrintLog(" End", 0) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to be able to choose to clear this text file or not, when I start the UserForm.

I can use a MsgBox with vbYesNo to ask the question. But what do I do to actually clear the contents of "Log.txt"?

-Minitman

Hi everyone,

I am new to this forum so I would like to thank you for considering my threat.

I have a problem with creating a VBA code for a macro fitting my requirements. I would like the macro to create an .htm file based on cell values in an opened Excell spreadsheet. For the content of the file I would like the macro to use the data in cells J2 and J3 and I would like to name the file according to cell J1. Actually I would like to create multiple htm files, but I guess (I hope) the loop will be the minor problem. Now, I have a very nice code, which does all these things – however since my htm files will include Slavic characters they are displayed badly in the resulting file. Here the code (I am not a VBA expert, so I took this code from a nice page and transformed it, probably i left some unnecessary data):

Sub MakeHTM_Basic()
' Defining a list of variables used in this program
Dim PageName As String

codepart1 = Range("j2").Value
codepart2 = Range("j3").Value
fName = Range("j1").Value
PageName = "C:" & fName & ".htm" 'location and name of saved file
MyPageTitle = Range("A1").Value

Open PageName For Output As #1

Print #1, codepart1
Print #1, codepart2
Close #1

End Sub

You can see the problem and check the result when you place the following text into cell J2:

Ä…

and the following text to cell J3:

Ĺš

(The value in J1 can be: test)

So what I would like is to make the macro save the resulting htm file in unicode and not in ansii as it obviousy does. I fought fiercly with it, replaced my notpad for other text editors but nothing helped yet. I am not sure if notepad is the source of the problem or if it is the VBA editor. Has anyone an idea how to make this work? This would help me a lot.

Thank you very much and have a good day!

I'm having trouble changing part of the file name for all the files in a particular folder. What I want to do is have an Input Box appear that I can input the current week. The macro will then find last week's number in the file name and replace it with the current number. The part I'm having trouble with is all the file names are slightly different. Here are file names: Week 21 Monday.xls, Week 21 Tuesday.xls, etc. for each day of the week.

I need a macro to change the numerical part of the file only with the number I input into the Input Box. Any help is greatly appreciated. I'm really close, but I cannot get this last part to work.

Hi there,

I am doing something similar to the autoformat function in Excel. But I
want to save each set of format to a file that can be exchanged between
users.

Is this possible to save a sample range of cells (about 6 cols and 3
rows) to a binary file including the cell format information.

After that, the content of the file will be loaded into a range
variable. Formation information will be obtained from that variable.

Please give some ideas or sample code.

Best regards,

coolersport

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello,

Got a tricky one: I have a list of account numbers for which I want to
retrieve associated data. The account number and its data I seek is found in
one of several files. To re-cap the commands: For each account found in the
list, go through the files in folder A, find the account number and return
the entire row of data for it.... then on to the next account number.

Any hope? TIA!


No luck finding an answer? You could always try Google.