• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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?
0
Justincut
Asked:
Justincut
  • 7
  • 7
1 Solution
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
GrahamSkanRetiredCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now