Import all files from folder instead of one at a time. Loop maybe?

kansfan2001

New Member
Joined
Aug 22, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
I have built a code to import specific areas from one sheet and placing that data into another. But for the life of me I can not figure out how to get it to repeat for all the excel files in the folder. I have to manually add each folder. I was wondering how to make the VBA below function for all file in the folder before it stops?

Thanks for the help its greatly appreciated :)



VBA Code:
Sub ImportFromPickSheet()
'
' ImportFromPickSheet Macro
'

'
' Defines variables Workbook 1 is for the MNF Calculator - Workbook 2 is for the Player Pick Sheet
Dim wb1 As Workbook, wb2 As Workbook

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which (Change "C:\" to pick import sheet location)
Set wb1 = ThisWorkbook
 ChDir "C:\"
  FName = Application.GetOpenFilename
  If FName <> False Then
    Set wb2 = Workbooks.Open(FName)
  End If

' Copy range B6:AM5 from ImportToCalc (Sheet2) of Workbook 2 and Import into ImportFromPickSheet (Sheet3) of Workbook 1
With wb2.Sheets("ImportToCalc").Range("B5:AM5")
wb1.Sheets("ImportFromPickSheet").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With


' Close wb2
wb2.Close


' Re-enable screen updating
Application.ScreenUpdating = True


End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I don't know if I got all of your code right, but it would be something like this using VBA. I know not everyone has access to Power Query.

VBA Code:
Sub ImportFromPickSheet()
'
' ImportFromPickSheet Macro
'

'
' Defines variables Workbook 1 is for the MNF Calculator - Workbook 2 is for the Player Pick Sheet
Dim sourceWB As Workbook
Dim targetWS As Worksheet
Dim sourceWS As Worksheet
Dim sFile As String
'Dim rowColumn As Long

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

'rowColumn = 2
Set targetWS = ThisWorkbook.Sheets("ImportFromPickSheet")

'loop through the Excel files in the folder
sFile = Dir("C:\" & "*.xls*")

Do Until sFile = ""

Set sourceWB = Workbooks.Open("C:\" & sFile)
Set sourceWS = sourceWB.Worksheets("ImportToCalc")

' Copy range B6:AM5 from ImportToCalc (Sheet2) of Workbook 2 and Import into ImportFromPickSheet (Sheet3) of Workbook 1
With targetWS
    .Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = sourceWS.Range("B5:AM5").Value2
End With

sourceWB.Close SaveChanges:=False
'rowColumn = rowColumn + 1
sFile = Dir()
Loop

' Re-enable screen updating
Application.ScreenUpdating = True


End Sub
 
Upvote 0
Howdy, thanks for helping.... but I have tried to run it multiple times but the macro doesn't do anything.
 
Upvote 0
Sorry, I wasn't able to test it since I wrote it from my phone. First off, are all of the files stored directly in your C drive? You will need to add the name of the folder that contains the files otherwise it won't know which folder to loop through.
 
Upvote 0
Hey no worries I appreciate the help. I added the folder to the vba but it still doesn't do anything. I have the macro assigned to a form control button (have tried to run it manually as well and have added the folder location see updated code below.

Here's what I am trying to do. My excel file called "MNF Pool 2021 - Calculator" I has a sheet called "ImportFromPickSheets" that I am trying to get the data imported to from all the files in this folder location: "C:\Users\Kansf\Desktop\Pick Sheets". As you can already tell it needs pull the data from excel files in the folder which all have a sheet labeled "ImportToCalc" and all have area B5:AM5 that needs to be copied then insert the data at the bottom of the list (Column B list starts on Cell B5) on the "MNF Pool 2021 - Calculator" sheet "ImportFromPickSheets". Then repeat the same thing for the next file in the folder until it has done this to all the files. I have tried it multiple ways but nothing happens...... not that I should see much since I have the Screenupdating turned off. Just not sure what I need to do? Again thanks for the help I greatly appreciated it. This code working right will save me HOURS of manually loading each excel file for all the other office pools I use this code on. :)


VBA Code:
Sub ImportFromPickSheet()
'
' ImportFromPickSheet Macro
'

'
' Defines variables Workbook 1 is for the MNF Calculator - Workbook 2 is for the Player Pick Sheet
Dim sourceWB As Workbook
Dim targetWS As Worksheet
Dim sourceWS As Worksheet
Dim sFile As String
'Dim rowColumn As Long

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

'rowColumn = 2
Set targetWS = ThisWorkbook.Sheets("ImportFromPickSheet")

'loop through the Excel files in the folder
sFile = Dir("C:\Users\Kansf\Desktop\Pick Sheets" & "*.xls*")

Do Until sFile = ""

Set sourceWB = Workbooks.Open("C:\Users\Kansf\Desktop\Pick Sheets" & sFile)
Set sourceWS = sourceWB.Worksheets("ImportToCalc")

' Copy range B6:AM5 from ImportToCalc (Sheet2) of Workbook 2 and Import into ImportFromPickSheet (Sheet3) of Workbook 1
With targetWS
    .Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = sourceWS.Range("B5:AM5").Value2
End With

sourceWB.Close SaveChanges:=False
'rowColumn = rowColumn + 1
sFile = Dir()
Loop

' Re-enable screen updating
Application.ScreenUpdating = True


End Sub
 
Upvote 0
Would it matter if the pick sheets in the folder location are saved as excel macro-enabled workbook?
 
Upvote 0
Howdy Alan, Yes I did tried a Query.... I switched everything around in my pool sheets (column list vs row list) to try and get it done but I just don't think it will work for what I need. I would have to specify every file I need pulled over along with setting the parameters for the query each time I had new pool sheets added. The names of the pool sheets vary and the amount of players in each pool vary from 15 to 150. Which setting up the query each time pool updates go out I think might become a bit excessive. But yes I did watch the video and spent about 2 hrs trying to get it all set..... pulled the info in but then realized I would have to do that everytime I wanted new information vs a macro that pulled everything at the push of buttons. Unless you know of a different way I am currently all ears
 
Upvote 0
@kansfan2001
Thanks for your feedback. Without seeing sample data sets, it is difficult to offer up any other PQ solutions that may have worked for you. Good Luck with this issue.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top