Hi there, and welcome to the forum.
You can, but...
If the workbooks were always open, you could use the INDIRECT function to do this. The problem is that INDIRECT can't pull data from a closed workbook.
There are workarounds, but you'll need to install an add-in to do it. There's more information here, but it also warns that it is quite slow.
So question that I'd have is... can you add a worksheet to each workbook that summarizes the data in the correct format for upload? That way the data would all be local, and we could help you with a macro to consolidate all the records into a single workbook for upload.