1

The code below works great when I do a "Step into" the code, and pastes the data from each file into the "Cisco Database" file. However, when I just run it, it always leaves off the last file. I added a specific line to save the DB file each time, to no avail. Any suggestions?

Sub AppendDataFinal()

Dim myWorkbook As String
myDB = "CiscoDatabase.xlsx"
myWorkbook = "Cisco.xlsm"

Application.Run "'Cisco.xlsm'!importfile" //grabs all .csv files from a folder

For Each wb In Workbooks
If (wb.Name <> myWorkbook) Then wb.Activate

Columns("A:O").Select
Selection.Delete Shift:=xlUp
Columns("O:AS").Select
Selection.Delete Shift:=xlUp


Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open ("file path")
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste , False
Range("A1").Select
If (ActiveWorkbook.Name = myDB) Then ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.Close savechanges:=False

Next wb


Workbooks.Open ("file path and name")

Application.Run "Cisco.xlsm!DateFormat" //trims time off date row, pretties up data


End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
caro
  • 863
  • 3
  • 15
  • 36
  • 2
    It's not a good idea to modify a collection such as `WorkBooks` while you're looping though it. You should maybe create an array or collection of the names of the files you want to operate on, and then loop through that instead. – Tim Williams Nov 15 '12 at 18:53
  • What file is this code running in? If it's Cisco.xlsm then you don't need to use `Application.Run` to call another macro in the same file. – Tim Williams Nov 15 '12 at 19:25
  • Thank you so much, I truly appreciate your looking at this hotmess. No matter how many tutorials I read I can't seem to find really clear explanations on working on these. – caro Nov 15 '12 at 20:40
  • Are you initially trying to append CSV files? you can do it like this in a DOS prompt: COPY File1.CSV + File2.CSV + File3.CSV Output.CSV. This assumes file names are consistent etc. But it's more reliable than custom VBA code – Nick.Mc Nov 16 '12 at 06:14

1 Answers1

0

A different approach using a collection (untested)

Sub AppendDataFinal()

Const MY_DB_PATH As String = "C:\pathToYourDBHere\"
Const MY_DB As String = "CiscoDatabase.xlsx"

Dim colNames As New Collection, wbName
Dim wb As Workbook
Dim wbDB As Workbook
Dim myDB As String

    importfile  'grabs all .csv files from a folder

    For Each wb In Workbooks
        'ThisWorkbook refers to the workbook containing this code
        If wb.Name <> ThisWorkbook.Name Then colNames.Add wb.Name
    Next wb

    'open the destination workbook
    Set wbDB = Workbooks.Open(MY_DB_PATH & MY_DB)

    For Each wbName In colNames

        Set wb = Workbooks(wbName)
        With wb.Sheets(1)
            .Columns("A:O").Delete Shift:=xlUp
            .Columns("O:AS").Delete Shift:=xlUp
            .Range("A1").CurrentRegion.Copy _
                  wbDB.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        End With
        wb.Close False
        wbDB.Save

    Next wbName

    wbDB.Activate
    DateFormat 'trims time off date row, pretties up data

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125