Read cells out of folder with severel .xlsm files and insert in an excel target file

[M_Gross]

Hi everyone,

I can not get started with a Problem, I have.

There is a folder with several excel files in it, all of these files have the same structure and the cells are already named.
Now, i want to extract the data of the cells in the folder and fill them in a new "target" excel file. I already created the target file, but do not know, how to start my VBA code.

Happy, about every help and suggestions to start.

Best regards,
Max

 

[jproffer]

If you want to do it without opening the files you're pulling data from, see if the answer(s) in this thread will get you started...or I (or someone) can come back later and see how you did.

https://www.mrexcel.com/forum/excel-questions/1058413-display-contents-worksheet-listbox-without-opening.html


If you just mean you want to open a file, pull some data, and then close the file it would be something similar to this:

Workbooks.Open "Book1" 'you may need the entire path.
Workbooks("Book2").Sheets("Sheet1").Range("A1").Value = Workbooks("Book1").Sheets("Sheet1").Range("A1").Value
Application.DisplayAlerts = False
Workbooks("Book2").Close
Application.DisplayAlerts = True

Oops, link is more than I bargained for, but it still goes to the right page.

 

[M_Gross]

Thank you for your answer, my Problem is that I have serveral files with different names in the folder. So, the code need to do the procedure for every *.xlsm* file in the folder and save it in another direction, aferwards.


To set the path, I thougt of that:

Public Sub Data1()
Dim strPath As String, strFile As String, strTabName As String
Dim lngR As Long
strPath = "path\"
strTabName = "This name can be different" 
strFile = Dir(strPath & "*.xlsm")
lngR = 1
End Sub

Am I going in the right direction, do not know, how to make the file Name variable?

 

[jproffer]

Ooooh....I'm afraid you're moving beyond my knowledge now (that's not hard to do ) . I've never had a reason to "loop" through each file within a folder. I'm almost sure there is a way, and hopefully someone will come along with an answer so we can both learn. I'm imagining something like:

For Each WorkBook in Workbooks

or

For Each WorkBook in Folder

but I just don't know the syntax for such a thing.

 

[Fluff]

You can loop through the files like

Public Sub Data1()
Dim strPath As String, strFile As String, strTabName As String
Dim lngR As Long
Dim Wbk As Workbook
strPath = "path\"
strTabName = "This name can be different"
strFile = Dir(strPath & "*.xlsm")
Do While strFile <> ""
   Set Wbk = Workbooks.Open(strFile)
   'do something
   Wbk.Close
   strFile = Dir()
Loop
End Sub