I've found several examples on this and other forums of how to read data from a directory of closed XLS spreadsheets (thanks all!). But I'm not making progress due to numerous differing errors. Some of it will be due to me being completely new to VBA. I suspect some of it is due to the fact I'm using an XLSM file to read other XLSMs where these scripts assume XLS - when the script errors it shows the script from the source file it is reading (and that script works fine), so perhaps there's a script conflict? Is this possible? There is no need for that source script to run but the result is the same when I disable it from running. The issue of specifically targeting a sheet is also a problem for me.
Time to put up my hand for a little help.
What I trying to do is:
- Read a directory of source XLSM files, opening each file one at a time
- Extract data from each source XLSM file at known fixed points, and place it into a target XLSM:
- Read Sheet "Field Assessment", Cell E3 (called "Title"); then write to target XLSM, sheet "Summary", Cell 6A (6 will increment to 7 for each source file read)
- Read Sheet "Field Assessment", Cell E4 (called "Team"); then write to target XLSM, sheet "Summary", Cell 6B
- Read Sheet "Field Assessment", Cell C54 (called "Result"); then write to target XLSM, sheet "Summary", Cell 6C
The target XLSM can be the one running the script. The target location will increase it's row number by 1 for each new file read. The data will be overwritten each time the script is run.
I assembled the following, but it is wrong and errors around . I'm not even sure if the "Sub" syntax is appropriate.
Sub Generate_Summary_Figures() ' This procedure reads through a set directory looking for all XLS file varieties. (Expect XLSM). ' It reads the files for specific field locations, then rolls the data into a sheet ' for the purposes of reporting figures. Dim myDir As String, fn As String ' Set the directory of XLSM Assessments to be scanned myDir = "K:\your\source\directory\" ' Set the file type on the end of the directory fn = Dir(myDir & "*.xls*") ' Set the starting row for output of raw data lifted from the directory of assessments currentRow = 6 Do While fn <> "" ' Read the data in from each source file Workbooks.Open (myDir & fn) title = Sheets("Field Assessment").Range("[(myDir & fn)]total!E3").Value ' Is the above how we properly target a specific named sheet's cell in the newly opened source file????? team = Sheets("Field Assessment").Range("[(myDir & fn)]total!E4").Value result = Sheets("Field Assessment").Range("[(myDir & fn)]total!E54").Value Workbooks(myDir & fn).Close False fn = Dir() ' Write the data out to the sheet that is running the script. ActiveSheet.Range("A" & currentRow).Value = title ActiveSheet.Range("B" & currentRow).Value = team ActiveSheet.Range("C" & currentRow).Value = result ' I suspect the above is wrong too!!! ' Increment the row number for the next line currentRow = currentRow + 1 Loop End Sub
Any pointers will be appreciated! Thanks.