Desperate help needed on looping through folder

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Hi Guys,

I defo cant do this on my own and need your guys help. I have never looped through files and folders and therefore need your help please.

I have several of workbooks that i copy on a daily basis.
I rename each workbook Daily stats followed by the date

e.g
Daily Stats 01.11.2011
Daily Stats 02.11.2011
Daily Stats 03.11.2011

The complete File path is C:\Users\h\Documents\Daily Stats and then i have my workbooks in there (e.g - Daily Stats 01.11.2011)
My files are saves as .XLSM files

Now what i need is a macro to do this

Have a input box to say select date for file to search for (Say i input 03.11.2011).
Loop through my file path and folder and find the workbook which has the date 03.11.2011 and open that workbook. In that workbook there will always be 1 worksheet. I need to copy that worksheet and paste it in the worksheet named Data in the initial Workbook that i am working on.

Now if there is no match with that date then msgbox File dated ??? can not be found ENTER another date
If the date selected matches any of the dates in the workbook i am initially working on ....Sheets("List").Range("B4:B" & LastWsRow)
Then
MSGBOX the file has already been copied, ENTER another date to search for.

Please Please Please Help me Guys. I can never do this without you guys
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello

In fact, you do not need to loop (that would be inefficient).

You know the folder name, you know the file name (piece it together), so the Dir function can check whether the file exists or not. And then you're started.
 

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Hello

In fact, you do not need to loop (that would be inefficient).

You know the folder name, you know the file name (piece it together), so the Dir function can check whether the file exists or not. And then you're started.

Hi Wigi,

I am completely new to this. Please guide me through this. The date to search for should be enter in an input box.

This is something new that i will trying be trying to grasp and take in. Please be patient and help me through this.

Much appreciated. x
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

In fact, you should work in a different way.

Loop through the files in the given folder, and add them to a listbox (or a sheet if you want). Then, clicking on the listbox is enough to do the input: you do not need an inputbox and the possibility for typos.
 

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146

ADVERTISEMENT

In fact, you should work in a different way.

Loop through the files in the given folder, and add them to a listbox (or a sheet if you want). Then, clicking on the listbox is enough to do the input: you do not need an inputbox and the possibility for typos.


I am lost :( Can you start us off so i know exactly which route to take
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Thanks, I thought it was something in excel.. a function I did not have!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Not sure you need to loop through the folder.

Try this.
Code:
Option Explicit
 
Sub ImportDailyStats()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsList As Worksheet
Dim wsSrc As Worksheet
Dim rngDateList As Range
Dim strPath As String
Dim strFileName As String
Dim varDate As Variant
Dim valResult As Variant
    
       strPath = "C:\Users\h\Documents\Daily Stats\"      ' "C:\Test\"
 
       Do
              varDate = InputBox("Please enter date (dd.mm.yyyy:)")
 
              If Not TypeName(varDate) = "Boolean" Then
                  strFileName = Replace("Daily Stats X.xlsm", "X", varDate)
            End If
            
       Loop Until Dir(strPath & strFileName) = strFileName
        Set wbDst = ThisWorkbook
        Set wsList = wbDst.Worksheets("List")
        
        Set rngDateList = wsList.Range("B4", wsList.Range("B" & Rows.Count).End(xlUp))
        
        valResult = Application.Match(strFileName, rngDateList, 0)
        If IsError(valResult) Then
        
            wsList.Range("B" & Rows.Count).End(xlUp).Offset(1) = strFileName
            Set wbSrc = Workbooks.Open(strPath & strFileName)
        
            Set wsSrc = wbSrc.Worksheets(1)
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            wbSrc.Close
        Else
            MsgBox strFileName & " has already been imported"
            
        End If
        
End Sub
 

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
Here's my 2 cents:
Code:
Sub tgr()
 
    ChDir "C:\Users\H\Documents\Daily Stats\"
 
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Data")
    ws.UsedRange.Clear
 
    On Error Resume Next
    With Workbooks.Open(Application.GetOpenFilename("Excel Files, *.xls*"))
        .Sheets(1).UsedRange.Copy ws.Range("A1")
        .Close False
    End With
 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,668
Messages
5,597,463
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top