Solved

Macro semi-working

Posted on 2013-12-23

Microsoft Excel

1 solution

184 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

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

Join The Community

4 Comments

LVL 4

Expert Comment

by:andrew_man

ID: 397360352013-12-23

some typo here!

LVL 18

Expert Comment

by:Steven Harris

ID: 397366842013-12-23

The code is not formatted correctly:

"v\Treasury Finance Controls"

should be

"v:\Treasury Finance Controls"

There are some other errors as well.

Author Comment

by:Justincut

ID: 397367402013-12-23

Cheers! What other errors are there?

LVL 18

Accepted Solution

by:Steven Harris

Steven Harris earned 500 total points

ID: 397367632013-12-23

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

Featured Post

Question has a verified solution.

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

Automating Microsoft Excel from Access (Part I)

Article by: Dale Fye

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

A Game of Draw Poker

Article by: Martin

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.

Using, Creating and Modifying Styles in Microsoft Excel

Video by: Bob

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

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…