Solved

Macro semi-working

Posted on 2013-12-23
4
144 Views
Last Modified: 2013-12-27
Hi Guys, I have a Macro which cuts and pastes a series of text files from 1 folder to another. Wierdly, only have of them work from this code:

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\12_2013\" & Prevday, _
 "StructNotesBSRec_Daily" & "*.txt"""
   
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "ALMBSRec_Daily" & "*.txt"""
   
   
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "CorpBankingBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "GIBSRec_Daily" & "*.txt"""
   
    MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "GSSBSRec_Daily" & "*.txt"""
    MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "Murex3BSRec_Daily" & "*.txt"""
   
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "WSSBSRec_Daily" & "*.txt"""
     MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "SophisBSRec_Daily" & "*.txt"""
     MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "CreditBSRec_Daily" & "*.txt"""
   
    MoveFiles "v\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "RatesBSRec_Daily" & "*.txt"""
   
   

The ones that don't move I have attached to see if anyone can work it out?
CreditBSRec-Daily-20131220-12153.txt
0
Comment
Question by:Justincut
  • 2
4 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39736035
some typo here!
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39736684
The code is not formatted correctly:

"v\Treasury Finance Controls"

should be

"v:\Treasury Finance Controls"

There are some other errors as well.
0
 

Author Comment

by:Justincut
ID: 39736740
Cheers! What other errors are there?
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39736763
When your code is copy/pasted to VBA, there are line breaks that shouldn't be there.  Try using this version:

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\12_2013\" & Prevday, "StructNotesBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "ALMBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "CorpBankingBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "GIBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "GSSBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "Murex3BSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "WSSBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "SophisBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "CreditBSRec_Daily" & "*.txt"""
    MoveFiles "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS", "v:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\2013\12_2013\" & Prevday, "RatesBSRec_Daily" & "*.txt"""

End Sub

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 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

16 Experts available now in Live!

Get 1:1 Help Now