[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

Solved

Macro semi-working

Posted on 2013-12-23

Microsoft Excel

1 solution

Medium Priority

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

Convert a grid inside a picture into Microsoft Excel

Article by: Wilder1626

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.

Forecasting in Excel 2016 for Windows and Mac

Article by: Bob

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.

Creating a Scrolling Chart in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

Microsoft Excel

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…