Results 1 to 10 of 10

Thread: [RESOLVED] import multiple files into one worksheet. Crate a loop.

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Resolved [RESOLVED] import multiple files into one worksheet. Crate a loop.

    Hi.

    I wish to import multiple files into one worksheet. When importing. I want to start copying from a specific point (B9) upto Column F9 and then keep copying down to when the data comes to the end. (highlighted) My data isn't fixed. One file may have 10 rows of data, another 15.

    Once the data has copied. I want to paste (values) into a specific worksheet. When it pastes the next file. I want the data to be pasted under the previous data along with the correct client id.

    The files I want to import will all be saved down in the same folder.

    .Team Documents\Freehold\HomeLarge\Reporting\Reports\

    File 1.

    Name:  Capture1.jpg
Views: 173
Size:  31.1 KB

    File 2.

    Name:  Capture2.jpg
Views: 206
Size:  30.8 KB


    File 3.

    This is how I wish the files to be pasted in my active file. Note the tab name and the header only gets copied once. The client ID also gets copied down in column J alongside the relevant data. I need the data pasted in the exact starting point. Slight mistake on my copydown in column J. New client id should start in J18

    Name:  Capture4.jpg
Views: 212
Size:  28.6 KB

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: import multiple files into one worksheet. Crate a loop.

    you can try this
    Code:
    Dim wb As Workbook, rng As Range, targ As Range
    Dim mypath As String, frw As Long, lrw As Long, f As String
    mypath = "c:\Team Documents\Freehold\HomeLarge\Reporting\Reports\"
    frst = True
    f = Dir(mypath & ".xlsx")
    Do Until Len(f) = 0
        Set wb = Workbooks.Open(mypath & f)
        With wb.Sheets(1)
            frw = 10
            Set targ = ThisWorkbook.Sheets("SVB Raw Data 3").Cells(Rows.Count, 4).End(xlUp).Offset(1)
            If frst Then
                frw = 9
                Set targ = ThisWorkbook.Sheets("SVB Raw Data 3").Cells(8, 4)
            End If
            lrw = .Cells(.Rows.Count, 2).End(xlUp).Row
            Set rng = .Cells(frw, 2).Resize(lrw - frw + 1, 5)
            rng.Copy targ
            targ.Offset(, 6).Resize(rng.Rows.Count).Value = .Cells(3, 7)
            If frst Then targ.Offset(, 6).Clear
        End With
        wb.Close False
        frst = False
        f = Dir
    Loop
    i can not test this, but the copying works correctly, make sure the path i copied is correct
    if you need to set column widths that could be incorporated into the code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: import multiple files into one worksheet. Crate a loop.

    Hello,

    Thanks for the above.

    I've run the code as you specified and nothing happened. Just returned blanked. No copying or pasting was performed and no break in the code occured. Would that suggest I'm not picking up the file to copy?

    I did change the .xlsx part to .xls. I presume this part of the code is saying to pick up any file in mypath with an .xls type.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: import multiple files into one worksheet. Crate a loop.

    Would that suggest I'm not picking up the file to copy?
    certainly one possibilty
    for testing add a line as below
    Code:
    Do Until Len(f) = 0
        Debug.Print f
        Set wb = Workbooks.Open(mypath & f)
    you should see the names of any files found in the immediate window

    is the code in the workbook you want the result?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: import multiple files into one worksheet. Crate a loop.

    Hello.

    I typed in the Debug code and no immediate window appeared. Which i find very strange. What does that mean?

    Code is in the desired workbook because I've put a timestamp at the bottom of the code and its updating the cell I defined.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: import multiple files into one worksheet. Crate a loop.

    Which i find very strange. What does that mean?
    look in the view menu
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: import multiple files into one worksheet. Crate a loop.

    what do you mean look in the view menu? To do what exactly? Sorry showing my newbie side here.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: import multiple files into one worksheet. Crate a loop.

    what do you mean look in the view menu?
    in the vb6 menu select view then go to immediate window

    To do what exactly?
    to show the immediate window, which would have the output from any debug.print statements, it does not open automatically, even though the results would already be there when you open the window
    you could also use Ctrl G and there is also a tool bar button in the debug toolbar, which also show the immediate window

    my guess is that your path to the files is wrong, which would be confirmed if there is nothing printed to the immediate window

    the immediate window is great for debugging, i have it open all the time (also the locals window), in it you could type directly
    Code:
    ?dir("c:\temp\*.xls")
    change the path to suit, when you press enter it will return the first xls file in the path, note that with the wildcard * it will also return all files starting .xls, so xlsx xlsm xlst etc
    ?dir("c:\temp\*.xls")
    31-10-2020.xlsx
    again if nothing is returned then your path will be incorrect or no xls type files in the folder
    to confirm if the path exists, you can use
    ?dir("c:\temp",vbDirectory)
    temp
    which returns (as shown) the name of the folder, blank line if the path is incorrect
    Last edited by westconn1; Nov 13th, 2020 at 03:16 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: import multiple files into one worksheet. Crate a loop.

    Hello.

    That's brilliant. Really helpful and now found what's the problem.

    I have of course found a new issue whilst testing which i didn't originally think off. I need to clear the sheet.SVB Raw Data 3 prior to importing the .xls files.

    Thanks

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: import multiple files into one worksheet. Crate a loop.

    try like
    Code:
    Dim wb As Workbook, rng As Range, targ As Range
    Dim mypath As String, frw As Long, lrw As Long, f As String
    mypath = "c:\Team Documents\Freehold\HomeLarge\Reporting\Reports\"
    ThisWorkbook.Sheets("SVB Raw Data 3").usedrange.clearcontents
    frst = True
    f = Dir(mypath & ".xlsx")
    Do Until Len(f) = 0
        Set wb = Workbooks.Open(mypath & f)
        With wb.Sheets(1)
            frw = 10
            Set targ = Cells(Rows.Count, 4).End(xlUp).Offset(1)
            If frst Then
                frw = 9
                Set targ = ThisWorkbook.Sheets("SVB Raw Data 3").Cells(8, 4)
            End If
            lrw = .Cells(.Rows.Count, 2).End(xlUp).Row
            Set rng = .Cells(frw, 2).Resize(lrw - frw + 1, 5)
            rng.Copy targ
            targ.Offset(, 6).Resize(rng.Rows.Count).Value = .Cells(3, 7)
            If frst Then targ.Offset(, 6).Clear
        End With
        wb.Close False
        frst = False
        f = Dir
    Loop
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width