Updating workbook from closed sources

redpipe67

Board Regular
Joined
Aug 7, 2002
Messages
70
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

Board Regular
Joined
Aug 7, 2002
Messages
70
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??
 

Forum statistics

Threads
1,181,607
Messages
5,930,884
Members
436,764
Latest member
avalladarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top