Extracting data from a directory of XLSM's

Hi all,

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.

Re: Extracting data from a directory of XLSM's

Hi Richie,

Thanks for your guidance. It is appreciated.

I can figure out where you are going, but my VBA skills are letting me down with what is probably a simple snag. I've bombed out early with an error at:

wsDest = ThisWorkbook.Worksheets("Sheet1")

The error is:
Run-time error '91':
Object variable or With block variable not set

Code is as you've written above.

I figured perhaps it was the name of the worksheet that was the issue, so I changed the code and sheetname to a simple "Sheet1", but it's still an error.

Any ideas?

Thanks
Ent.

Re: Extracting data from a directory of XLSM's

Found the problem. The line of code needs "set" to proceed the rest of it. So it should be:

set wsDest = ThisWorkbook.Worksheets("Sheet1")

I now have a related question: for each "source" sheet that gets opened by this vba script (over 50 files), all of those "source" sheets contain their own unrelated vba macros.

Naturally, Excel warns "Do you wish to run this marco?" for each and every one while the macro runs. I know I can turn the warning off for all scripts, but I'd prefer it if there was some way to "set" the answer to be yes when running the above script.

Any ideas?

Thanks again to Richie for his help.

Ent.

Re: Extracting data from a directory of XLSM's

Hi Richie,

It was set to the default "Ask" level, where it warns you about running macros and prompts you to run or cancel. (I've set it to run by default now, temporarily).

I expect the answer will be that there's no way to do this: logically if there was a way to do it, then the entire concept of prompting to run would be defeated if any script could override it. But I thought it was worth checking on, because it's the unusual case of a macro that was granted explicit permission to run, running another macro.

Thanks again! This has been very helpful.
Ent.

Re: Extracting data from a directory of XLSM's

Thanks for your help Richie- sounds like the only reasonable option.

And thanks again for guiding me onto the right track. Everything works nicely now.

Ent.