0

I have written some VBA code to handle copying data from multiple workbooks in a single folder to another Master workbook and then graph the results. When running the macro it doesn't copy the data in the correct order. Ie. Curve1 gets copied where Curve8 should go. Below is the code that handles the entire folder selection and copy paste procedure.

 Sub CopyDataBetweenWorkbooks()

    Dim wbSource As Workbook
    Dim shTarget As Worksheet
    Dim shSource As Worksheet
    Dim strFilePath As String
    Dim strPath As String

    ' Initialize some variables and
    ' get the folder path that has the files
    Set shTarget = ThisWorkbook.Sheets("5")
    strPath = GetPath

    ' Make sure a folder was picked.
    If Not strPath = vbNullString Then

        ' Get all the files from the folder
        strfile = Dir$(strPath & "*.xlsx", vbNormal)

        Do While Not strfile = vbNullString

            ' Open the file and get the source sheet
            Set wbSource = Workbooks.Open(strPath & strfile)
            Set shSource = wbSource.Sheets("Points")


            'Copy the data
            Call CopyData(shSource, shTarget)

            'Close the workbook and move to the next file.
            wbSource.Close False
            strfile = Dir$()
        Loop
    End If

End Sub

' Procedure to copy the data.
Sub CopyData(ByRef shSource As Worksheet, shTarget As Worksheet)

    Const strRANGE_ADDRESS As String = "B1:C26000"

    Dim lCol As Long

    'Determine the last column.
    lCol = shTarget.Cells(21, shTarget.Columns.Count).End(xlToLeft).Column + 2

    'Copy the data.
    shSource.Range(strRANGE_ADDRESS).Copy
    shTarget.Cells(21, lCol).PasteSpecial xlPasteValuesAndNumberFormats

    ' Reset the clipboard.
    Application.CutCopyMode = xlCopy

End Sub


' Fucntion to get the folder path
Function GetPath() As String

    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select a folder"
        .Title = "Folder Picker"
        .AllowMultiSelect = False

        'Get the folder if the user does not hot cancel
        If .Show Then GetPath = .SelectedItems(1) & "\"

    End With

End Function

If there is a way for me to implement a change without using an array that would be the best option.

halfer
  • 19,824
  • 17
  • 99
  • 186
hburazin
  • 1
  • 1
    Do you mean that `Dir$` doesn't loop through the files in the order you want? – BigBen Jan 14 '20 at 19:42
  • Yes, exactly. The workbooks are titled as follows "curve1" "curve2" "curve3".... "curve10" But when i execute the macro to pull the data from each workbook it opens and copies them randomly. I made certain that "curve1" was "edited" first, and "curve10" was the last one to be edited so they line up both by name and last modified. – hburazin Jan 15 '20 at 13:17
  • `Dir$` isn't guaranteed to loop in alphabetical order... one option is to generate a list of filenames and sort them, e.g. as demonstrated [here](https://stackoverflow.com/questions/8690792/use-dir-to-return-files-from-a-folder-in-file-system-order). – BigBen Jan 15 '20 at 13:22

1 Answers1

0

Found it folks! I just needed to step through the files in order by calling them one by one and stepping through the numbers (The naming convention can even be anything followed by a number using this method) Sweet Giblets, I did it!

 Sub CopyDataBetweenWorkbooks()

    Dim wbSource As Workbook
    Dim shTarget As Worksheet
    Dim shSource As Worksheet
    Dim strFilePath As String
    Dim strPath As String

    ' Initialize some variables and
    ' get the folder path that has the files
    Set shTarget = ThisWorkbook.Sheets("6")
    strPath = GetPath
    Filename = InputBox("What is the name of this File")
    FileCount = InputBox("How many file are you looking for")
    ' Make sure a folder was picked.
    If Not strPath = vbNullString Then

        ' Get all the files from the folder
        'strfile = Dir$(strPath & "*.xlsx", vbNormal)

        'Do While Not strfile = vbNullString
            For FileNumber = 1 To FileCount Step 1

                strfile = Filename & FileNumber & ".xlsx"

                ' Open the file and get the source sheet
                Set wbSource = Workbooks.Open(strPath & strfile)
                Set shSource = wbSource.Sheets("Points")


                'Copy the data
                Call CopyData(shSource, shTarget)

            'Close the workbook and move to the next file.
                wbSource.Close False
                'strfile = Dir$()
            Next 'FileNumber
        'Loop
    End If

End Sub
hburazin
  • 1