import data from multiple excel files

[sambrierley]

Hi all,

I have a requirement to create a piece of vba that will open all xlsx files in a folder one at a time then import the data in a range (sheet1!A1:G14) into a table named Weekly Input.

seems quite simple when i wrote it down lol

thanks for any help

 

[pr2-eugin]

Hello sambrierley, Welcome to AWF

Could you show some code you currently have? What is working? What is not working?

 

[sambrierley]

thanks for the quick reply, my problem is im not familiar with how access objects work. the below code will work in excel no probs (I have added excel to the object lib) I really would have a few questions relating to it;
1) will this code need to change at all to function properly with access objects that will come later in the code?
2) how do i then take date from a set range in each excel file and collate them all onto a common table?

Sub Open_My_Files()
 Dim MyFile As String
 Dim MyPath As String
 MyPath = "C:\Users\samuel.brierley\Desktop\Password\"
 MyFile = Dir(MyPath)
 Do While MyFile <> ""
 If MyFile Like "*.xlsx" Then
 Workbooks.Open MyPath & MyFile
 End If
 MyFile = Dir
 Loop
 End Sub

 

[namliam]

docmd.transferspreadsheet is used for importing excel files

 

[sambrierley]

thanks i will have a play with that tomorrow but right now its home time