Updating workbook from closed sources

[redpipe67]

Good day,
I would like to be able to update a workbook from another spreadsheet kept in the same folder. The spreadsheet is generated by a data collection program we use and overwrites the previous spreadsheet generated with the exact same name. Ideally I would like to not have to open this spreadsheet to update any links and since it's a new file each time it is generated, any name ranges would be lost the next time it was generated as it would overwrite the previous spreadsheet so using import external data to update by name range is no good although the results this gives me is exactly what I am after. I have managed to import data from the spreadsheet but since I need to import the data into a specific range of cells that already have headers this also does not work. Does anyone have any ideas how I can take a specific range of cells from the spreadsheet without a named range and pop it into the workbook in a specific area, without opening the spreadsheet to update links and without needing to specify headers since they are already in the workbook the data is going into?

Does that make sense?

Thank you for any assistance.

 

[ravishankar]

HI
paste the following codes in the macro window (Alt F11)

for a = 1 to 25
for b = 1 to 10
cells(a,b) = "='filpath[filename.xls]sheet1'!" & chr(b+64) & a
next b
next a
example of formula in cell ='D:\my documents\[ABC.xls]sheet1'!C25

Run the macro. it will pull data from A1 to J25
Ravi

 

[redpipe67]

Hi Ravi,
Thanks for your reply, it's appreciated. Unfortunatley the code you supplied did not work. I changed the path to reflect the correct path to the sheet that I want to pull the data from and inserted the code into the macro window of the file that I want the data to end up in. but when I try to close the file i recieve an error message that states "Compile Error, Invalid Outside Procedure" with the first line of your code highlighted.

I was going to attach 2 seperate sheets to demonstrate my requirement which is pulling information from a specific area in spreadsheet B.xls and having that information copied to a different and specific area in Spreadsheet A.xls all without opening spreadsheet B.xls and without using a named range but I can't find the option to attach??