Cutting and pasting a text file in Excel from one folder to another

Hi Guys, in Excel everyday I have to copy and cut about 6 text files to a dated folder.In the macro, there will a wildcard "*" in the code as there's always some random number generated at the end of the name of the text file. Is there a way I can do this as when I try to record a Macro, nothing comes up in the code?
JustincutAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GrahamSkanConnect With a Mentor RetiredCommented:
If you have a need to count the number of files, you could use such a variable. Otherwise it isn't needed.

There was an extra backslash at the end of the output folder string.

Again I've reformatted the code for easier reading
Sub CallMoveFiles()
    Dim Prevday As String

    Prevday = WorksheetFunction.WorkDay(Date, -1)
    Prevday = Format(Prevday, "DDMMYY")
    MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", _
             "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\11_2013\" & Prevday, _
             "StructNotesBSRec_Daily" & "*.txt"""

 End Sub

Open in new window

0
 
GrahamSkanRetiredCommented:
Don't really understand the difficulty, but this pair of macro procedures should do that sort of thing
Sub CallMoveFiles()
    MoveFiles "C:\MyInFolder", "C:\MyOutFolder", "pattern*.txt"
End Sub

Sub MoveFiles(strInFolder As String, strOutFolder As String, strFilePattern As String)
    Dim strFileName As String
   
    strFileName = Dir$(strInFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        Name strInFolder & "\" & strFileName As strOutFolder & "\" & strFileName
        strFileName = Dir$()
    Loop
   
End Sub

Open in new window

0
 
JustincutAuthor Commented:
so  my file is called "RatesBalancesheetRec_13112013_67777.txt" and I have to move it from "v\:EOD" to ":v:\FullLedger\10112013". How would that code look?

The Wildcard would be "RatesBalancesheetRec " & "*.txt"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GrahamSkanRetiredCommented:
Just plug the values into the calling Sub:

Sub CallMoveFiles()
    MoveFiles "v\:EOD", "v:\FullLedger\10112013", "RatesBalancesheetRec*.txt"
End Sub

Open in new window

0
 
JustincutAuthor Commented:
If the Folder that the files are going to be pasted to is named the Previous Business Day, eg, Friday's date "081113" . Would this code work?

Dim PrevDay as string
PrevDay = WorksheetFunction.WorkDay(Date, -1)

Sub CallMoveFiles()
    MoveFiles "v\:EOD", "v:\FullLedger\" & PrevDay & ", "RatesBalancesheetRec*.txt"
End Sub
0
 
JustincutAuthor Commented:
So the final code I paste into my Module would be:

Sub CallMoveFiles()
Dim Prevday as string

    MoveFiles "v\:EOD", "v:\FullLedger\" & PrevDay & ", "RatesBalancesheetRec*.txt"
End Sub

Sub MoveFiles(strInFolder As String, strOutFolder As String, strFilePattern As String)
    Dim strFileName As String
   
    strFileName = Dir$(strInFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        Name strInFolder & "\" & strFileName As strOutFolder & "\" & strFileName
        strFileName = Dir$()
    Loop
   
End Sub
0
 
GrahamSkanRetiredCommented:
I don't see why not. Try it.
0
 
GrahamSkanRetiredCommented:
Missed your latest comment.

You don't set Prevday to any value in it. If you are setting it up somewhere else, don't declare it locally as well or it will be a zero length string.

I have to take her indoors out to lunch now , so I won't be able to respond again for a while.
0
 
JustincutAuthor Commented:
I am getting Compile Error: Argument Not Optional on the "MoveFiles" bit of code. Any ideas?

Sub CallMoveFiles()
Dim Prevday As String

      Prevday = WorksheetFunction.WorkDay(Date, -1)
   
    MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\StructNotesBSRec_Daily" & "*.txt", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\11_2013" & Prevday & ""
   
End Sub

Sub MoveFiles(strInFolder As String, strOutFolder As String, strFilePattern As String)
    Dim strFileName As String
   
    strFileName = Dir$(strInFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        Name strInFolder & "\" & strFileName As strOutFolder & "\" & strFileName
        strFileName = Dir$()
    Loop
   
End Sub
0
 
GrahamSkanRetiredCommented:
You only have two arguments in the call to MoveFiles. It expects three. I've used the continuation (underscore) character to spread it across two lines here:
Sub CallMoveFiles()
    MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\StructNotesBSRec_Daily" & "*.txt", _
          "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\11_2013" & Prevday & ""
End Sub

Open in new window

0
 
JustincutAuthor Commented:
I am now getting a Run-time error 52- Bad file name or number.Does it definitely accept the Wildcard "*" sign?

I am hovering over strFilePattern and its saying "StructNotesBSRec_Daily*.txt"

Its breaking on this line:

 
    strFileName = Dir$(strInFolder & "\" & strFilePattern)
0
 
GrahamSkanRetiredCommented:
Is the strInFolder value correct?
0
 
JustincutAuthor Commented:
Hi, can you double check my code is right? I don't want to change the name of the file. Just shift it from "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS" Folder to
v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\11_2013\" & Prevday & "\" Folder. The name of my file is "StructNotesBSRec_Daily" & "*.txt"""

Sub CallMoveFiles()
Dim Prevday As String

      Prevday = WorksheetFunction.WorkDay(Date, -1)
   Prevday = Format(Prevday, "DDMMYY")

 MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\11_2013\" & Prevday & "\", "StructNotesBSRec_Daily" & "*.txt"""
0
 
JustincutAuthor Commented:
Your Loop should have an incrementor, no? eg. i = i +1 and dim i as integer?


Sub MoveFiles(strInFolder As String, strOutFolder As String, strFilePattern As String)
    Dim strFileName As String, i As Integer
   
   
    strFileName = Dir$(strInFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        Name strInFolder & "\" & strFileName As strOutFolder & "\" & strFileName
        strFileName = Dir$()
    Loop
   i = i + 1
   
   
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.