Desperate help needed on looping through folder

[Siyanna]

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

 

[Siyanna]

Not sure you need to loop through the folder.

Try this.

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

Wow Thank You

A couple of things.
If my file is already open then it creates another workbook which i dont want.
The sheet copied is currently copied on its own sheet but i need it pasted on sheets named "data"
RngDateList is Input like this 01/01/2011 however it is save like 01.01.2011 because excel wont let me save like 01/01/2011. Would that be a problem?
Is TYPENAME a VBA function?

P.s can you guide me through how this is working so i know how to construct this myself in future

Is there a dif between DIR and CHDIR?

 

[Siyanna]

Here's my 2 cents:

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

Again this a shrewd way of doing it but have no clue how it works, any chance you can explain the code above. Many thanks

 

[Norie]

Do you mean if the workbook you want to copy from is open?

If it is a check can be added to go through the open workbooks and see if that's the case.

Forgot about the 'data' bit but that's easily fixed.

Where should the data go in the 'data' worksheet?

Is it appended to existing data or does it replace the data already on the worksheet.

What exactly is on the date list?

I assumed it was the filename.

TypeName is a VBA function and it returns the data type of a variable.

ChDir and Dir are quite different - ChDir is used to change the current directory and Dir is used to look for folders and files.

 

[Siyanna]

Do you mean if the workbook you want to copy from is open?

Yes, Sometimes the file that i am trying to copy from might be left open therefore i need copy regardless of it is open or closed (Hope that makes sense

If it is a check can be added to go through the open workbooks and see if that's the case.

Forgot about the 'data' bit but that's easily fixed.

Where should the data go in the 'data' worksheet?

Range A1

Is it appended to existing data or does it replace the data already on the worksheet.

It needs to replace everything in Data sheet. Basically each day the data sheet cells should be deleted and the new data should be pasted.

What exactly is on the date list?

The date list (i.e b4 to lastrow) has dates that have been copied. So if i copy 03.11.2011 then the lastrow data will be 03.11.2011. does that make sense?

I assumed it was the filename.

TypeName is a VBA function and it returns the data type of a variable.
Data type Date right?

ChDir and Dir are quite different - ChDir is used to change the current directory and Dir is used to look for folders and files.

.

 

[Norie]

Can you clarify what's in the list?

Is it actual dates like 03/11/2011 formatted as 03.11.2011 or is it text like 03.11.2011?

Not sure what you mean about TypeName.

I'm using it to check that the user hasn't hit cancel in the inputbox, not if they've entered a date.

Don't even know if VBA will recognize something like 02.11.2011 as a date.

Would you prefer the user to actually enter a date in another format, like 02/11/2011?

That can be converted for the filename.

 

[Siyanna]

Can you clarify what's in the list?

Is it actual dates like 03/11/2011 formatted as 03.11.2011 or is it text like 03.11.2011?

Not sure what you mean about TypeName.

I'm using it to check that the user hasn't hit cancel in the inputbox, not if they've entered a date.

Don't even know if VBA will recognize something like 02.11.2011 as a date.

Would you prefer the user to actually enter a date in another format, like 02/11/2011?

That can be converted for the filename.

Hi,

my dates are entered like this 01/01/2011

the only reason why I save my file like this 01.01.2011 is because excel won't let me save like this but if you can convert and prompt the user to enter in format 01/01/2011 that would be sound

do you want me to send you the file?

Thank you ever so much

 

[Siyanna]

Can you clarify what's in the list?

Is it actual dates like 03/11/2011 formatted as 03.11.2011 or is it text like 03.11.2011?

Not sure what you mean about TypeName.

I'm using it to check that the user hasn't hit cancel in the inputbox, not if they've entered a date.

If no date was entered, would it exit sub?

Don't even know if VBA will recognize something like 02.11.2011 as a date.

Would you prefer the user to actually enter a date in another format, like 02/11/2011?

That can be converted for the filename.

Thanks

 

[Norie]

I think this covers everything.

Option Explicit
Sub ImportDailyStats()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsList As Worksheet
Dim wsSrc As Worksheet
Dim wsDst 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\"
    ' strPath = "C:\Test\"

    Do
        varDate = InputBox("Please enter date (dd/mm/yyyy:)")
 
        If Not TypeName(varDate) = "Boolean" Then
            strFileName = Replace("Daily Stats X.xlsm", "X", Replace(varDate, "/", "."))
        End If
    Loop Until Dir(strPath & strFileName) = strFileName
    Set wbDst = ThisWorkbook
    Set wsList = wbDst.Worksheets("List")
    ' get list of dates and see if the date entered is on it
    Set rngDateList = wsList.Range("B4", wsList.Range("B" & Rows.Count).End(xlUp))
    '    varDate = Replace(varDate, ".", "/")
    Set valResult = rngDateList.Find(DateValue(varDate))

    ' if date entered not on list then copy the data
    If valResult Is Nothing Then
        ' add date to list
        wsList.Range("B" & Rows.Count).End(xlUp).Offset(1) = DateValue(varDate)
        ' check if workbook is actually open, if it isn't open it
        If IsWBOpen(strFileName) Then
            Set wbSrc = Workbooks(strFileName)
        Else
            Set wbSrc = Workbooks.Open(strPath & strFileName)
        End If
        ' clear worksheet for new data
        Set wsDst = wbDst.Worksheets("Data")
        wsDst.Cells.ClearContents
        Set wsSrc = wbSrc.Worksheets(1)
        ' copy data from from Daily Stats workbook to
        wsSrc.Cells.Copy wsDst.Range("A1")
        wbSrc.Close
    Else
        MsgBox strFileName & " has already been imported"
    End If
End Sub
Function IsWBOpen(strWBName As String) As Boolean
Dim wb As Workbook
    For Each wb In Application.Workbooks
        If wb.Name = strWBName Then
            IsWBOpen = True
        End If
    Next wb
End Function