Solved

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

Posted on 2013-11-10
14
166 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now