?
Solved

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

Posted on 2013-11-10
14
Medium Priority
?
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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