How to do this in a simple way?

[deuce]

I am trying to automatically update the past 20 working days of data from an excel source CSV workbook into a target worksheet using the external data procedure but I am in the dark on how to get this done. Can someone kindly give a detailed procedure on how to do this?

The source workbook is updated in real time while it is closed but cannot be updated/written to if it is open. When I go to the source workbooks file location I can view the actual file only if I click on the view compatibility files option in windows 7, but if I try to browse to the source workbooks file location using the browse function the file is not visible at all.

What can I do to accomplish my task?

thanks and regards.

 

[Aladin Akyurek]

hi thanks for your response, can it be done to retrieve only the last N rows into the target workbook from the source workbook?

The source workbook contains 160,000 rows for every dataset and there are like 9 different sets.

i am trying to keep only the relevant data in the target workbook.

kind regards,
deuce.

Yes. The set up I suggested needs no change. You just need to prefix the relevant ranges with the relevant workbook and sheet names.

 

[deuce]

hi aladin, thanks for the help so far, i put in the formula and it looks like it will work(need to test) but can you tell me how I replace the sheet name reference in the formula you provided with that of my source workbook in another folder?

target workbook and source workbook are in different folders.

thanks and regards,

deuce

 

[Aladin Akyurek]

hi aladin, thanks for the help so far, i put in the formula and it looks like it will work(need to test) but can you tell me how I replace the sheet name reference in the formula you provided with that of my source workbook in another folder?

target workbook and source workbook are in different folders.

thanks and regards,

deuce

WB1

Activate A1
Type =
Go to WB2, Sheet1
Click on E2
Hit Enter

Copy the prefix you see on the Formula Bar.
Paste/Append the copied prefix in/to the target formulas.

 

[deuce]

hi that is what i was trying all along but when i click on WB2 it just clicks normally without the activation ring.

I know how to switch sheet references for different worksheets in the same workbook but it i cannot figure out what i am doing wrong where the multiple workbooks are concerned.

i even tried to type in = then clicked on the windows icon to display the most recent documents but the source book was greyed out as well as all the documents in there, however i removed the = and clicked on the windows icon and they were not greyed out at all.

i also tried to swap by attempting to link WB1 with WB2 then WB2 to WB1.

I cannot seem to get it right. what do you suggest?

 

[deuce]

hi, just an update, i managed to detect the problem that was causing it, the work book had been saved in binary format so i just saved it to reflect as a macro enabled workbook.

Hopefully this solves my problems. i will test out during the course of the following week and provide updates if required.

thanks.

aladin your the best!

 

[deuce]

hi I have been experiencing this error all of a sudden and here is the description of it.

when i open the workbook i get the popup message


this workbook contains one or more links that cannot be updated.

to change the source of the links, or to attempt to update values again, click edit links.

to leave the links as is click continue.

i click on continue, but the rows in the new workbook are not being updated with the latest data from the source workbook.

if i click on edit links option under status i get the option open source to update values.

I need to update values without having to open the source workbooks. What can i do?

thanks and regards,
deuce.

 

[Aladin Akyurek]

hi I have been experiencing this error all of a sudden and here is the description of it.

when i open the workbook i get the popup message


this workbook contains one or more links that cannot be updated.

to change the source of the links, or to attempt to update values again, click edit links.

to leave the links as is click continue.

i click on continue, but the rows in the new workbook are not being updated with the latest data from the source workbook.

if i click on edit links option under status i get the option open source to update values.

I need to update values without having to open the source workbooks. What can i do?

thanks and regards,
deuce.

The formulas we are invoking ordinarily would not lead to such problems. The location of the target book is probably changed from one session to another. Would you check for that?

Posted from Güzelçamlı at the Aegean cost.

 

[deuce]

hi thanks for your response. when you say target workbook I think you mean the destination workbook? if so it is kept on my desktop the source workbook is kept in the program files directory.

I tried to create another workbook using the same formulas that you gave.

the data updates when the source workbook is open (meaning that in the formula that you have given) it is showing the name and cell references of the source workbook like Sheet1!A:A etc.

But when the source workbook is closed and the destination workbook is reopened then the same error is popping up and also I notice that the entire path down to the source workbook is given instead of sheet1!A:A i.e. ('c:\Program files\etc etc\Sheet1!A:A).

I have also set the proper settings in the data>edit links tabs but despite this the error is still coming up.

Now I don't know what to do to fix this error as it is very puzzling despite having the correct settings. What do you suggest?