Solved

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

Posted on 2013-11-10
14
205 Views
Last Modified: 2013-11-16
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
Comment
Question by:Justincut
  • 7
  • 7
14 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39636787
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
 

Author Comment

by:Justincut
ID: 39636821
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39636830
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Justincut
ID: 39636853
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
 

Author Comment

by:Justincut
ID: 39636856
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39636857
I don't see why not. Try it.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39636863
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
 

Author Comment

by:Justincut
ID: 39639250
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39639295
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
 

Author Comment

by:Justincut
ID: 39639346
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39639364
Is the strInFolder value correct?
0
 

Author Comment

by:Justincut
ID: 39639399
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
 

Author Comment

by:Justincut
ID: 39639433
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39639835
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question