-
Nov 10th, 2020, 04:58 PM
#1
Thread Starter
Addicted Member
-
Nov 11th, 2020, 04:47 AM
#2
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
-
Nov 11th, 2020, 02:41 PM
#3
Thread Starter
Addicted Member
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.
-
Nov 12th, 2020, 05:07 AM
#4
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
-
Nov 12th, 2020, 06:46 AM
#5
Thread Starter
Addicted Member
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.
-
Nov 13th, 2020, 02:02 AM
#6
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
-
Nov 13th, 2020, 08:44 AM
#7
Thread Starter
Addicted Member
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.
-
Nov 13th, 2020, 03:11 PM
#8
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 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
-
Nov 16th, 2020, 06:05 AM
#9
Thread Starter
Addicted Member
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
-
Nov 16th, 2020, 03:25 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|