Excel 2010 macro to find files, perform tasks then move files to a folder.

Fair warning: I am a total Excel macro noob but I am hacking my way through.

I need to write a macro that will find 2 CSV files that match a different filename pattern each, in a specified folder. I only know part of the filename as the file names will be timestamped.

I then need to import the contents of each CSV to a different worksheet in my Excel workbook and then move the file to an archive folder.

Where i need help is how to find the csv files in the specified folder and them move the files that were just used to an archive subfolder.

Here is the macro that I have so far that uses static filenames. It does not move files once they have been processed.

Sheets("orders_ETA").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;\\uncpath\folder\filename1.csv" _
        , Destination:=Range("$A$1"))
        .Name = "orders_ETA"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Due Dates").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT\\uncpath\folder\filename2.csv", Destination:=Range("$A$1"))
        .Name = "Due Dates"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet1").Select
End Sub

Open in new window



Can I get some pointers please? I have googled for other people trying to do the same thing but I haven't been able to make sense of what I have found.

Cheers!
defectaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
If your recorded macro does what you need for one file, try this. Note: your macro has been tweaked slightly to change the CSV file name part.
Option Explicit

Sub FindCSVs()
Dim strFolder As String
Dim strArchiveFolder As String

Dim strFilePattern As String
Dim strFileName As String
strFolder = "C:\MyFolder\"
strArchiveFolder = "C:\MyArchiveFolder\"

strFilePattern = "abc2015-??-??.csv"
strFileName = Dir(strFolder & strFilePattern)
Do Until strFileName = ""
    ProcessCSV (strFolder & strFileName)
    Name strFolder & strFileName As strArchiveFolder & strFileName
    strFileName = Dir()
Loop
End Sub

Sub ProcessCSV(strFileFullName As String)

Sheets("orders_ETA").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFileFullName _
        , Destination:=Range("$A$1"))
        .Name = "orders_ETA"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Due Dates").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFileFullName, Destination:=Range("$A$1"))
        .Name = "Due Dates"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

defectaAuthor Commented:
That looks good, thanks Graham.

Would i be correct in thinking that you code additions only allow for 1 file pattern to be the source for each worksheet in my code example?

i need to match two different file name patterns if you will. what would that code look like?

Cheers!
GrahamSkanRetiredCommented:
If the patterns are similar enough, you might be able to combine them into one. If that isn't possible, you can simply call the procedure with each pattern as an argument. This modified code should cope with several diferent file patterns.

Option Explicit

Sub CallFindCSVs()
    Dim strPatterns() As String
    Dim i As Integer
    
    strPatterns() = Split("abc2015-??-??.csv,def??_??_2015.csv", ",")
    For i = 0 To UBound(strPatterns)
        FindCSVs strPatterns(i)
    Next i
End Sub

Sub FindCSVs(strFilePattern As String)
Dim strFolder As String
Dim strArchiveFolder As String

'Dim strFilePattern As String
Dim strFileName As String
strFolder = "C:\MyFolder\"
strArchiveFolder = "C:\MyArchiveFolder\"

strFileName = Dir(strFolder & strFilePattern)
Do Until strFileName = ""
    ProcessCSV (strFolder & strFileName)
    Name strFolder & strFileName As strArchiveFolder & strFileName
    strFileName = Dir()
Loop
End Sub

Sub ProcessCSV(strFileFullName As String)
...

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

defectaAuthor Commented:
sorry, i have been swamped and haven't been able to implement your code as yet. will report back ASAP.
defectaAuthor Commented:
So I'm starting from the first code example you gave to see if i can get it to work and I am not having any luck. I am not at familiar with proper code conventions and simply copy pasting what you have posted in place of my code example doesn't work.

firstly it doesn't like the Option Explicit command at the start. it gives "compile error. invalid inside procedure." I tried removing the command to see what would happen and it didn't help. (you obviously put it in there for reason.)

A little help please?
GrahamSkanRetiredCommented:
Option Explicit is a statement that will causes the compiler to raise an error message if an undeclared variable is used.
With it, this macro will give a Compile error of 'Variable not defined'. This will call attention to the possibility of misspelling. Obvious in this case.
Sub Test()
      Dim strA as string
      strA = "Some text"
      MsgBox strB
End Sub

Open in new window

Without it, the code will run without error, but the message box will be blank, i.e. will give the wrong result.

The statement must be at the top of the code in the module. You have probably pasted the code inside an existing procedure. Note that if you were to select the words and to press F1, you would be able to read the relevant help information.

If there is code that you might want to keep in the module, you can insert a new module and start again. Otherwise, just clear the code and re-copy from here.
defectaAuthor Commented:
So with a little help from a colleague, showing me how to debug and step through the code to see where it was breaking, we got it working.

Here is what the code ended up looking like. IIRC it  was missing the 'Call' at the beginning of line 28 in the code example below. That and not putting a backslash at the end of the folder path were what was preventing it from working for me.

Thanks again for your help and patience Graham. =)

Option Explicit

Sub CallFindCSVs()
'
' CallFindCSVs macro
'

    Dim strPatterns() As String
    Dim i As Integer
    
    strPatterns() = Split("pattern1.??????????????.csv,pattern2?????????????????.csv", ",")
    For i = 0 To UBound(strPatterns)
        FindCSVs strPatterns(i)
    Next i
End Sub

Sub FindCSVs(strFilePattern As String)
Dim strFolder As String
Dim strArchiveFolder As String

'Dim strFilePattern As String
Dim strFileName As String
strFolder = "\\server\folder\"
strArchiveFolder = "\\server\folder\Archive\"

strFileName = Dir(strFolder & strFilePattern)
Do Until strFileName = ""
    Call ProcessCSV(strFolder & strFileName, strFileName)
    Name strFolder & strFileName As strArchiveFolder & strFileName
    strFileName = Dir()
Loop
End Sub

Sub ProcessCSV(strFileFullName As String, strFileName As String)
' MsgBox strFileFullName & ":" & strFileName & ":"
If Left(strFileName, 8) = "pattern1" Then

Sheets("orders_ETA").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFileFullName, Destination:=Range("$A$1"))
        .Name = "orders_ETA"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
Else

    Sheets("Due Dates").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFileFullName, Destination:=Range("$A$1"))
        .Name = "Due Dates"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End If

End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.