views:

235

answers:

1

I'm going to be generating some graphs from a lot of data located in multiple workbooks. The data is formatted exactly the same in all workbooks and reside in folders all at the same level. I'm going to be bringing parts (ranges) of the data into one final workbook where I'll generate my graphs from.

This made me think that this sort of thing is ripe for VBA automation. Only problem, I'm a novice. I've tried writing pseudo code and then replacing it with what I think is correct VBA. I've looked around for examples, and tried Excel help files, but I'm missing some important steps somewhere...and some basic steps as well.

Lots of things seem to be wrong (... at least you'll have something to smile about before the weekend). If anyone can point out where my brain has abandoned me, I'd be very grateful.

Also, how do you add the name of the file that the ranges came from in Column B on the same rows? This is something that would really help me but I can't find an example of how to do it.

Sub CopySourceValuesToDestination()

Dim DestPath As String
Dim SourcePath As String
Dim Folder As Variant
Dim Folders As Variant
Dim FileInFolder As Variant
Dim Range1 As Range
Dim Range2 As Range
Dim DesitnationPaste1 As Variant
Dim DesitnationPaste2 As Variant


Folder = Array("ABC", "DEF", "GHI", "JKL")
FileInFolder = Array("ABCFile", "DEFFile", "GHIFile", "JKLFile")

''My final Excel file sits in the parent folder of the source files folders
DestPath = "S:\Common\XYZ\Michael S\Macrotest\"

''Each file has it's own folder, and there are many specific files in each
SourcePath = "S:\Common\XYZ\Michael S\Macrotest\ + Folder"

''Always the same in each of my source files
Range1 = Cells("C4:C8") 
Range2 = Cells("C17:D21") 

''Below I 'm trying to paste Range1 into Column C directly under the last used cell
DestinationPaste1 = Range("C5000").End(xlUp).Offset(1, 0)

 ''Below I 'm trying to paste Range2 into Column D directly under the last used cell
DestinationPaste2 = Range("D5000").End(xlUp).Offset(1, 0)

''Trying to make it loop through the folder and the_
''files...but this is just a guess
For Each Folder In Folders 
''Again a guess
F = 0 

''The rest of the process would open a source file copy_
''Range1 and then opening the Destination file and pasting_
''it in the Row 1 of Column C. Hopefully it then goes back_
''to the open source file copies Range2 and pastes it the_
''next Row down in Column C

    Workbooks.Open FileName:=SourcePath + FileName + "Source.xls"

        Workbook.Sheet(Sheet2).Range1.Copy

    Workbook.Open FileName:=DestPath + "Destination.xls"

        Workbook.Sheet(Sheet1).DestinationPaste.Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
            Operation:= xlNone, SkipBlanks:=False, Transpose:=True

    Windows(SourcePath + FileName + "Source.xls").Activate

        Workbook.Sheet(Sheet2).Range2.Copy

    Workbook.Open FileName:=DestPath + "Destination.xls"

        Workbook.Sheet(Sheet1).DestinationPaste.Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=True

  Windows(SourcePath + FileName + "Source.xls").Activate
    ActiveWorkbook.Close
F = F + 1
Next

End Sub

The outcome of the process would look like the image below - but without the colours or the additonal "_b":

Final Data Output

Many thanks again for any help you can give me.

Michael.

+1  A: 

I don't know if this is exactly what you want, but I think it will get you closer and give you some clues on how to proceed. We can edit it to make it right.

Sub CopySourceValuesToDestination()

    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim sDestPath As String
    Dim sSourcePath As String
    Dim shDest As Worksheet
    Dim rDest As Range
    Dim vaFolder As Variant
    Dim vaFiles As Variant
    Dim i As Long

    'array of folder names under sDestPath
    vaFolder = Array("ABC", "DEF", "GHI", "JKL")

    'array of file names under the respective folders in vaFolder
    vaFiles = Array("ABCFile.xls", "DEFFile.xls", "GHIFile.xls", "JKLFile.xls")

    sDestPath = "S:\Common\XYZ\Michael S\Macrotest\"
    sSourcePath = "S:\Common\XYZ\Michael S\Macrotest\"

    'Open the destination workbook at put the destination sheet in a variable
    Set wbDest = Workbooks.Open(sDestPath & "Destination.xls")
    Set shDest = wbDest.Sheets(1)

    'loop through the folders
    For i = LBound(vaFolder) To UBound(vaFolder)
        'open the source
        Set wbSource = Workbooks.Open(sSourcePath & vaFolder(i) & "\" & vaFiles(i))

        'find the next cell in col C
        Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
        'write the values from source into destination
        rDest.Resize(5, 1).Value = wbSource.Sheets(1).Range("C4:C8").Value

        'repeat for next source range
        Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
        rDest.Resize(5, 2).Value = wbSource.Sheets(1).Range("C17:D21").Value

        wbSource.Close False
    Next i

End Sub
Dick Kusleika
Mike
Worked like a charm - and fast! I just amended the second rdest = shDest to (...Rows.Count.4), so it adds the values to column D. I'm just looking in to how to add the source file name to column B when writing the first values (the second values are from the same source file).
Mike
Amended the code a little bit (my requirements slightly changed, so i'm only using a one cell range rather than a 5 cell range). I've added an additional step to paste in the file name: rDest.Resize(1, 1).Value = wbSource.FileName in column B but I think my interpritation of how it should work is slightly off.
Mike
Found it: rDest.Value = wbSource.Name. Worked a charm. Thanks for the great help again.
Mike
Mike
I'm not sure I understand. Is there one file per folder or multiple? Are you copying a list of file names into the Array() function? Where are those file names? I think we should figure out how to go get those file names automatically. Is it every file in that folder or only certain ones? If it's every file in ABC, or even every xls file, there's a better way to do it.
Dick Kusleika
Mike
One file in each folder. I've copied in the names of the FOLDERS in one ARRAY and the names of the FILES in another ARRAY. The FOLDER name is ABC. The FILE NAME in that folder is ABC_text_text.xls. My thought was that I could loop through the files by using a new vsFileName "_test_text.xls" and adding vaFOLDER at the start. It's probbaly quite comlicated - so what I have now works great, so I'll leave it at that. Again many thanks for this great piece of code.
Mike