Link to home
Start Free TrialLog in
Avatar of Justincut
Justincut

asked on

How to open via Macro a Daily Automated file with a random number in the name

Hi, I am trying to import into an Excel file on a daily basis a text dump called "Daily OBS REC_YYYYMMDD_(Random Number)" via Macro but it ends with a random number so I have to manually rename the file every day by stripping the name of the random number. Is there a way I can tweak my Macro so I can import the file every day without having manually rename it? Using the Instr function, for example?

Justin
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
Hi

your "Daily OBS REC_YYYYMMDD_(Random Number)"  or random number would come only once per day for that YYYYMMDD day ? or it may come twice or 3 times like you would have 1 instance of YYYYMMDD or several ?

if only once then I would do something like this Sub

Sub test()
Dim FileNameMask As String, FileName As String
FileNameMask = ActiveWorkbook.Path & "\Daily OBS REC_" & Format(Now, "YYYYMMDD") & "*.xls"
FileName = ActiveWorkbook.Path & "\" & Dir(FileNameMask)
Workbooks.Open FileName

End Sub

Open in new window


This would open the file called Daily OBS REC_YYYYMMDD regardless of the random number provided it is in the same directory as the file where the macro is run if not then you need to change
ActiveWorkbook.Path to the location of the Daliy OBS file

If you have problems with that give me the location of the file and I will re-write the macro.

gowflow
Avatar of Justincut
Justincut

ASKER

Hi, it worked using the * wildcard symbol to open up the file, but when I copy the data from the file and paste to another spreadsheet and try to go back using "Windows ("DailyOBS Rec YYYYMMDD" & ".*.xlsx").Activate", I get Run Time error 9: Subscript out of range". any ideas why?
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
for sure please post the code in here as you are loosing focus and need to affect to a variable.
gowflow
Avatar of Justincut
Justincut

ASKER

Sub Import()
'
' Import Macro
'
Dim target, target1, target2, target3, target4, target5, target6, target7, target8, target9, target10, target11 As Range
Dim PrevDay, Prevday2 As String

PrevDay = Worksheets("Rec").Range("AM1").Value
PrevDay = Format(PrevDay, "DDMMYY")

Prevday2 = Worksheets("Rec").Range("AM1").Value

Prevday2 = Format(Prevday2, "YYYYMMDD")
'
   
   
       
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "ALMBSRec_Daily_" & Prevday2 & "*.xlsx"
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$24425").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
    Worksheets("Journals").Select
   
   
   
     Range("C4").PasteSpecial xlPasteValues
 
     
     

     
   


   
   
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GIBSRec_Daily_" & Prevday2 & "*.xlsx"
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$166").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
 
   
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Set target1 = Range("C5").End(xlDown).Offset(1)
   
    target1.PasteSpecial xlPasteValues
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GSSBSRec_Daily_" & Prevday2 & "*.xlsx"
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Rows("3:3").Select
    Selection.AutoFilter
    Range("M7").Select
    ActiveSheet.Range("$A$3:$R$472").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
 Set target2 = Range("C5").End(xlDown).Offset(1)

    target2.PasteSpecial xlPasteValues
   
       
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "StructNotesBSRec_Daily_" & Prevday2 & "*.xlsx"
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$3223").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target3 = Range("C5").End(xlDown).Offset(1)

    target3.PasteSpecial xlPasteValues
   
   
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "RatesBSRec_Daily_" & Prevday2 & "*.xlsx"
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Range("A3").Select
   

   
    ActiveSheet.Range("$A$3:$R$72511").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
     Set target4 = Range("C5").End(xlDown).Offset(1)

    target4.PasteSpecial xlPasteValues
   
   
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "CreditBSRec_Daily_" & Prevday2 & "*.xlsx"
   
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("G3").Select
     
     
    ActiveCell.FormulaR1C1 = "Structured ID"
     Rows("3:3").Select
    Application.CutCopyMode = False
     Selection.Copy
    ActiveSheet.Range("$A$3:$R$50").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
   
    Range("A1").CurrentRegion.Select
   
    Selection.Copy
 
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Set target5 = Range("C5").End(xlDown).Offset(1)
   
     target5.PasteSpecial xlPasteValues
   
   
    Windows("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
 Range("M3").Select
 
 
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Sheets("Rec").Select
   
     Range("E5").PasteSpecial

    'target6.PasteSpecial xlPasteValues
   
    Windows("GIBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
   
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
 
   
     Set target7 = Range("E5").End(xlDown).Offset(1)
 target7.PasteSpecial xlPasteValues
   
    Windows("GSSBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
 
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target8 = Range("E5").End(xlDown).Offset(1)
 
  target8.PasteSpecial xlPasteValues
    Windows("RatesBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
       Set target9 = Range("E5").End(xlDown).Offset(1)
     target9.PasteSpecial xlPasteValues
    ActiveSheet.Paste
   
   
   
   
    Windows("StructNotesBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target11 = Range("E5").End(xlDown).Offset(1)
     target11.PasteSpecial xlPasteValues
     Windows("CreditBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
   
    Range("M1").Select
     Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
       
   
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target10 = Range("E5").End(xlDown).Offset(1)
     target10.PasteSpecial xlPasteValues
Avatar of Justincut
Justincut

ASKER

The get this Error Message 9 " Subscript Out of Range"  when the Macro hit this code



    Windows("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
reason why you get this error is due to 2 possible reasons. As I am getting part of your code it could be any of the following:

1) the workbook containing called ALMBSRec_Daily_" & Prevday2 & "*.xlsx has not been opened. Actually I only get this instruction when I search for ALMBRec string
 Windows("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
I do not get a workbooks.Open instruction.

2) It could be that the variable Prevday2 contains the wrong data which makes this file either not there or not being able to be opened.

3) If this file is a sheet in a workbook you would get this error if the sheet is missing.

As I am getting part of your code then difficult to know more.

An other comment to your code you use Activesheet and Windows instructions which are usually quite dangerous as if the focuses changes due to a copy paste then automatically your activesheet points to a total different sheet and your data may all be totally corrupted and still the code would work without error.

gowflow
Avatar of Justincut
Justincut

ASKER

Try this:
Dim target, target1, target2, target3, target4, target5, target6, target7, target8, target9, target10, target11 As Range
Dim PrevDay, Prevday2 As String

PrevDay = Worksheets("Rec").Range("AM1").Value
PrevDay = Format(PrevDay, "DDMMYY")

Prevday2 = Worksheets("Rec").Range("AM1").Value

Prevday2 = Format(Prevday2, "YYYYMMDD")
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "ALMBSRec_Daily_" & Prevday2 & "*.xlsx"
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$24425").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
    Worksheets("Journals").Select
   
     
     Range("C4").PasteSpecial xlPasteValues
 Windows("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
 Range("M3").Select
 
 
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Sheets("Rec").Select
   
     Range("E5").PasteSpecial
Avatar of Justincut
Justincut

ASKER

The macro opens the file  "ALMBSRec_Daily_" & Prevday2 & "*.xlsx", filters the data, copies and pastes it to the ("Murex BS rec breaks - " & PrevDay & ".xlsm").

This works fine, but when it goes back to the
 Windows ("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate

It fails.

Prevday2 is fine. Its simply YYYYMMDD. Any ideas?
Avatar of Justincut
Justincut

ASKER

I am experimenting using code with "*" and "Windows" and its not working at all. Can you attempt some code that works with them both in?
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
pls can you post the entire workbook with code ?
I cannot troubleshoot part of code and further your code with activesheet and windows is not recommended you need to use explicit variables not implicit
something like
Dim WS as Worksheet
Set WS = ActiveSheet
WS.RAnge("A2").select etc...

gowflow
Avatar of Justincut
Justincut

ASKER

How do you change this from implicit to explicit?

The macro opens the file  "ALMBSRec_Daily_" & Prevday2 & "*.xlsx", filters the data, copies and pastes it to the ("Murex BS rec breaks - " & PrevDay & ".xlsm").

 Windows ("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate


(Murex BS rec breaks - " & PrevDay & ".xlsm") is the name of the file that I am running the Macro from and is my masterfile.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
pls post the entire code and I will fix it for you
gowflow
Avatar of Justincut
Justincut

ASKER

Sub Import()
'
' Import Macro
'
Dim target, target1, target2, target3, target4, target5, target6, target7, target8, target9, target10, target11 As Range
Dim PrevDay, Prevday2 As String

PrevDay = Worksheets("Rec").Range("AM1").Value
PrevDay = Format(PrevDay, "DDMMYY")

Prevday2 = Worksheets("Rec").Range("AM1").Value

Prevday2 = Format(Prevday2, "YYYYMMDD")
'
   
   
       
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "ALMBSRec_Daily_" & Prevday2 & "*.xlsx"
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$24425").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
    Worksheets("Journals").Select
   
   
   
     Range("C4").PasteSpecial xlPasteValues
 
     
     

     
   


   
   
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GIBSRec_Daily_" & Prevday2 & "*.xlsx"
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$166").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
 
   
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Set target1 = Range("C5").End(xlDown).Offset(1)
   
    target1.PasteSpecial xlPasteValues
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GSSBSRec_Daily_" & Prevday2 & "*.xlsx"
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Rows("3:3").Select
    Selection.AutoFilter
    Range("M7").Select
    ActiveSheet.Range("$A$3:$R$472").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
 Set target2 = Range("C5").End(xlDown).Offset(1)

    target2.PasteSpecial xlPasteValues
   
       
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "StructNotesBSRec_Daily_" & Prevday2 & "*.xlsx"
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$3223").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target3 = Range("C5").End(xlDown).Offset(1)

    target3.PasteSpecial xlPasteValues
   
   
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "RatesBSRec_Daily_" & Prevday2 & "*.xlsx"
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Range("A3").Select
   

   
    ActiveSheet.Range("$A$3:$R$72511").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
     Set target4 = Range("C5").End(xlDown).Offset(1)

    target4.PasteSpecial xlPasteValues
   
   
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Workbooks.Open Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "CreditBSRec_Daily_" & Prevday2 & "*.xlsx"
   
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("G3").Select
     
     
    ActiveCell.FormulaR1C1 = "Structured ID"
     Rows("3:3").Select
    Application.CutCopyMode = False
     Selection.Copy
    ActiveSheet.Range("$A$3:$R$50").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
   
    Range("A1").CurrentRegion.Select
   
    Selection.Copy
 
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Set target5 = Range("C5").End(xlDown).Offset(1)
   
     target5.PasteSpecial xlPasteValues
   
   
    Windows("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
 Range("M3").Select
 
 
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
   
    Sheets("Rec").Select
   
     Range("E5").PasteSpecial

    'target6.PasteSpecial xlPasteValues
   
    Windows("GIBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
   
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
 
   
     Set target7 = Range("E5").End(xlDown).Offset(1)
 target7.PasteSpecial xlPasteValues
   
    Windows("GSSBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
 
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target8 = Range("E5").End(xlDown).Offset(1)
 
  target8.PasteSpecial xlPasteValues
    Windows("RatesBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
   
       Set target9 = Range("E5").End(xlDown).Offset(1)
     target9.PasteSpecial xlPasteValues
    ActiveSheet.Paste
   
   
   
   
    Windows("StructNotesBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target11 = Range("E5").End(xlDown).Offset(1)
     target11.PasteSpecial xlPasteValues
     Windows("CreditBSRec_Daily_" & Prevday2 & "*.xlsx").Activate
   
    Range("M1").Select
     Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
       
   
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Murex BS rec breaks - " & PrevDay & ".xlsm").Activate
    Set target10 = Range("E5").End(xlDown).Offset(1)
     target10.PasteSpecial xlPasteValues
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
Sorry your macro does not end with
End Sub

and to post code press on the button Code in this window and insert your code in between the 2 code you get something like

[.code ]
place your code here from Sub to End Sub
[./code]

gowflow
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
byundt
Didn't I just mentioned exactly that in my last post ?
gowflow
Avatar of byundt
byundt
Flag of United States of America image
gowflow,
Right you are!

I  apologize for not reading your post carefully enough and subsequently duplicating your content.

I've changed my Comment status to Deleted.

Brad
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
byundt
No worries at all, when we get to the level you are at I certainly understand that you simply cannot read each and every word in an answer as too much time consuming.

I always like your input and so much room to learn.
hv a great day
gowflow
Avatar of Justincut
Justincut

ASKER

The code is still falling over at this stage:

   Windows("ALMBSRec_Daily_" & Prevday2 & "*.xlsx").Activate


Have you tested any macros where the "*" & "Windows code work successfully together?
Avatar of byundt
byundt
Flag of United States of America image
In Windows, the following characters may not be used in folder names or file names: / ? < > \ : * | ”

When VBA tries to run the following statement, it treats the * as a part of the file name, rather than a wildcard character. Since the asterisk is not a legal character in a file name, you should not be surprised that there is a runtime error on that statement.

The best way of approaching the problem is to create workbook variables for each of your open workbooks. The best time to do this is when the file is being opened.

Instead of activating a Window, you should refer to the workbook in question using one of the eight workbook variables I created. The code snippet below incorporates these changes.
Sub Import()
'
' Import Macro
'
Dim target As Range, target1 As Range, target2 As Range, target3 As Range, target4 As Range, target5 As Range, target6 As Range, target7 As Range, target8 As Range, target9 As Range, target10 As Range, target11 As Range
Dim PrevDay As String, Prevday2 As String
Dim sFileNameMask As String, sFileName As String
Dim wbALMBSRec As Workbook, wbGIBSRecDaily As Workbook, wbMurexBSRecBreaks As Workbook, wbStructNotesBSRecDaily As Workbook, _
    wbGSSBSRecDaily As Workbook, wbRatesBSRecDaily, wbCreditBSRecDaily As Workbook

PrevDay = Worksheets("Rec").Range("AM1").Value
PrevDay = Format(PrevDay, "DDMMYY")

Prevday2 = Worksheets("Rec").Range("AM1").Value

Prevday2 = Format(Prevday2, "YYYYMMDD")
'
    Set wbMurexBSRecBreaks = Workbooks("Murex BS rec breaks - " & PrevDay & ".xlsm")
    
    sFileNameMask = "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "ALMBSRec_Daily_" & Prevday2 & "*.xlsx"
    sFileName = Dir(sFileNameMask)
    Set wbALMBSRec = Workbooks.Open(sFileName)
    
    'Workbooks.Open FileName:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "ALMBSRec_Daily_" & Prevday2 & "*.xlsx"
    
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$24425").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    wbMurexBSRecBreaks.Activate
   
    Worksheets("Journals").Select
    
     Range("C4").PasteSpecial xlPasteValues

    sFileNameMask = "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GIBSRec_Daily_" & Prevday2 & "*.xlsx"
    sFileName = Dir(sFileNameMask)
    Set wbGIBSRecDaily = Workbooks.Open(sFileName)

    'Workbooks.Open FileName:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GIBSRec_Daily_" & Prevday2 & "*.xlsx"
    
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$166").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
  
    
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    
    
    Set target1 = Range("C5").End(xlDown).Offset(1)
    
    target1.PasteSpecial xlPasteValues
    
    sFileNameMask = "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GSSBSRec_Daily_" & Prevday2 & "*.xlsx"
    sFileName = Dir(sFileNameMask)
    Set wbGSSBSRecDaily = Workbooks.Open(sFileName)
    
    'Workbooks.Open FileName:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "GSSBSRec_Daily_" & Prevday2 & "*.xlsx"
    
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Rows("3:3").Select
    Selection.AutoFilter
    Range("M7").Select
    ActiveSheet.Range("$A$3:$R$472").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    wbMurexBSRecBreaks.Activate
 Set target2 = Range("C5").End(xlDown).Offset(1)

    target2.PasteSpecial xlPasteValues
    
    sFileNameMask = "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "StructNotesBSRec_Daily_" & Prevday2 & "*.xlsx"
    sFileName = Dir(sFileNameMask)
    Set wbStructNotesBSRecDaily = Workbooks.Open(sFileName)
    
    'Workbooks.Open FileName:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "StructNotesBSRec_Daily_" & Prevday2 & "*.xlsx"
    
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$3223").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    Set target3 = Range("C5").End(xlDown).Offset(1)

    target3.PasteSpecial xlPasteValues
    
    sFileNameMask = "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "RatesBSRec_Daily_" & Prevday2 & "*.xlsx"
    sFileName = Dir(sFileNameMask)
    Set wbRatesBSRecDaily = Workbooks.Open(sFileName)
    
    'Workbooks.Open FileName:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "RatesBSRec_Daily_" & Prevday2 & "*.xlsx"
    
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Structured ID"
    Range("A3").Select
   

    
    ActiveSheet.Range("$A$3:$R$72511").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    
     Set target4 = Range("C5").End(xlDown).Offset(1)

    target4.PasteSpecial xlPasteValues
    
    
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    
    sFileNameMask = "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "CreditBSRec_Daily_" & Prevday2 & "*.xlsx"
    sFileName = Dir(sFileNameMask)
    Set wbCreditBSRecDaily = Workbooks.Open(sFileName)
    
    'Workbooks.Open FileName:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\10_2013 Recs\BS\Full Ledger\" & PrevDay & "\" & "CreditBSRec_Daily_" & Prevday2 & "*.xlsx"
   
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("G3").Select
     
     
    ActiveCell.FormulaR1C1 = "Structured ID"
     Rows("3:3").Select
    Application.CutCopyMode = False
     Selection.Copy
    ActiveSheet.Range("$A$3:$R$50").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"
   
    Range("A1").CurrentRegion.Select
    
    Selection.Copy
 
    wbMurexBSRecBreaks.Activate
   
    
    Set target5 = Range("C5").End(xlDown).Offset(1)
    
     target5.PasteSpecial xlPasteValues
    
   
    wbALMBSRec.Activate
 Range("M3").Select
 
  
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    
    
    Sheets("Rec").Select
    
     Range("E5").PasteSpecial

    'target6.PasteSpecial xlPasteValues
    
    wbGIBSRecDaily.Activate
    
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    wbMurexBSRecBreaks.Activate
 
    
     Set target7 = Range("E5").End(xlDown).Offset(1)
 target7.PasteSpecial xlPasteValues
   
    wbGSSBSRecDaily.Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
  
    wbMurexBSRecBreaks.Activate
    Set target8 = Range("E5").End(xlDown).Offset(1)
 
  target8.PasteSpecial xlPasteValues
    wbRatesBSRecDaily.Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    
       Set target9 = Range("E5").End(xlDown).Offset(1)
     target9.PasteSpecial xlPasteValues
    ActiveSheet.Paste
    
    
    
    
    wbStructNotesBSRecDaily.Activate
    Range("M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    Set target11 = Range("E5").End(xlDown).Offset(1)
     target11.PasteSpecial xlPasteValues
     wbCreditBSRecDaily.Activate
    
    Range("M1").Select
     Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
       
    
    Application.CutCopyMode = False
    Selection.Copy
    wbMurexBSRecBreaks.Activate
    Set target10 = Range("E5").End(xlDown).Offset(1)
     target10.PasteSpecial xlPasteValues

End Sub

Open in new window

Avatar of byundt
byundt
Flag of United States of America image
It should also be said that the code in question is very difficult to maintain, even for someone who is good at VBA. Ideally, it would be broken up into 7 smaller subs, each of which imports data from one workbook.

You should also avoid selecting or activating ranges, worksheets and workbooks. Doing so is almost never necessary, slows down code execution, and makes the code harder to debug and maintain.

I'm assuming this is not the only macro in your workbook, and that the others exhibit similar issues. Your employer would be well served if you had someone revise your code into something that is easier to maintain. Such a task would be outside the scope of a question on this forum, however.

Soliciting work for hire or offering to perform work for hire is not permitted in Experts Exchange threads. That said, many of the Experts active in the Excel TA take on side jobs and post their email address in their user profile so you can contact them directly.
Avatar of Justincut
Justincut

ASKER

Hi, that code looks good. Is there a way I can get it to run at 8am automatically without me being in the office?
Avatar of byundt
byundt
Flag of United States of America image
You can use Windows Scheduler to launch Excel and open the workbook. If macros are automatically enabled, then a Workbook_Open sub can call the Import macro:
'This code must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Import    'Run the Import macro at 8 AM
End Sub

Alternatively, you can leave Excel running all the time and use Application.OnTime to schedule the macro to run at a certain time. I put such a statement in a Workbook_Open sub to make the Import macro run at 8 AM.

Open in new window

'This sub must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Application.OnTime TimeSerial(8, 0, 0), "Import"    'Run the Import macro at 8 AM
End Sub

Open in new window

Avatar of byundt
byundt
Flag of United States of America image
I've requested that this question be closed as follows:

Accepted answer: 0 points for byundt's comment #a39583126
Assisted answer: 500 points for gowflow's comment #a39578124
Assisted answer: 0 points for byundt's comment #a39583393

for the following reason:

Question had originally been closed with http:/Q_28268250.html#a39583393 marked as solution (500) points.

byundt reopened the Question with a suggested closing of 500 points to gowflow Comment http:/Q_28268250.html#a39578124 and 0 points to byundt Comments http:/Q_28268250.html#a39583126 and http:/Q_28268250.html#a39583393. The essence of the solution was described in the gowflow Comment: use Dir to get the file name and set a workbook variable to each of the workbooks being opened.

byundt Comment http:/Q_28268250.html#a39583126 followed through with that suggestion and replaced all the Windows(xxx).Activate statements with WorkbookVariable.Activate statements. byundt Comment http:/Q_28268250.html#a39583393 then added a discussion of Windows Scheduler and Application.OnTime to the Import macro would be called at a certain time.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
byundt

I am deeply sorry if my last comment was offensive. If it was by any chance I am deeply sorry and withdraw any comment that respect. I will ask you to delete my last comment anyway.

This being said, I wish to thank you first for the last post on closing this question as you acted highly professionally which confirm to me once more that I belong to the right site and indeed all Experts (at least the top level experts) are  truly professionals and correct.

I wish to ask you to re-open this question as I am not sure that the way it was closed is indeed the will of the asker as if he had seen already my comments and if they were indeed a solution for him he would have closed the question in that respect. The fact that he choose your post for ontime as closing maybe indicate that he is not satisfied with my post.

I have no problem with points attribution and indeed in my mind that way it is now being closed (full 500 for gowflow) is indeed not fair and not correct.

I will not object to this but posting, but will post as a comment and know that you have enough privileges to close this question as you feel appropriate.

Rgds/gowflow
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
byundt,

Thank you for your comment and action. I truly appreciate the link on Excel thread discussion which I put in my monitored Question to follow as very interesting and an opening to get help when things get difficult Excel whys.

Rgds/gowflow