Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
I am creating a pivot table in workbook1 from data held in workbook2. This
works just fine. Both workbooks are in the same directory. If I now move that directory either on the same computer or, as I need to be able to do, on to a second computer the source data cannot be found when I refresh the pivot table. Obviously the path has changed though I do always keep the 2 files together in the one directory. I think my problem is that the data source is defined via an absolute file path but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory. Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them so long as they both stay in the same directory? As always any advice will be greatly appreciated. I am using Excel 2003 on Vista Thank you, Peter |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
hi, Peter !
if you ask through vba-editor / immediate window pane what/where your connection is directed to (i.e.) copy/paste (or type) the following and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify the DefaultDir= string to any path (like: thisworkbook.path) and once modified, execute the above instruction without the question mark or... you could do the above by code (i.e.) in your workbook_open event or... make some sort of tricky procedure (i.e. by ms-query) using parameters for the query and linking cells as parameters hth, hector. __ OP __ ... creating a pivot table in workbook1 from data held in workbook2 ... works just fine. Both workbooks are in the same directory. If I now move that directory either on the same computer or ... to a second computer the source data cannot be found when I refresh the pivot table. Obviously the path has changed though I do always keep the 2 files together in the one directory. I think my problem is that the data source is defined via an absolute file path but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory. Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them so long as they both stay in the same directory? As always any advice will be greatly appreciated. I am using Excel 2003 on Vista Thank you, Peter |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
Hector
Thank you for your reply. It looks like what I need. Unfortunately I am stumbling at the first step. The code... ? activesheet.pivottables(1).pivotcache.connection when pasted into the vbe editor is giving error 1004 Application defined or object defined error. I have checked that the pivot table is called PivotTable1 and that the cursor was in the table when I opened the vbe if that matters. It is on Sheet1. I know this is me not properly understanding the vbe but can you give me just a little more detail on this first bit? (Beleive it or not I have written a few procedures in the past so I am embaressed that I cannot do this). With many thanks, Peter "Héctor Miguel" wrote: hi, Peter ! if you ask through vba-editor / immediate window pane what/where your connection is directed to (i.e.) copy/paste (or type) the following and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify the DefaultDir= string to any path (like: thisworkbook.path) and once modified, execute the above instruction without the question mark or... you could do the above by code (i.e.) in your workbook_open event or... make some sort of tricky procedure (i.e. by ms-query) using parameters for the query and linking cells as parameters hth, hector. __ OP __ ... creating a pivot table in workbook1 from data held in workbook2 ... works just fine. Both workbooks are in the same directory. If I now move that directory either on the same computer or ... to a second computer the source data cannot be found when I refresh the pivot table. Obviously the path has changed though I do always keep the 2 files together in the one directory. I think my problem is that the data source is defined via an absolute file path but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory. Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them so long as they both stay in the same directory? As always any advice will be greatly appreciated. I am using Excel 2003 on Vista Thank you, Peter |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
hi, Peter !
... I am stumbling at the first step. The code... ? activesheet.pivottables(1).pivotcache.connection when pasted into the vbe editor is giving error 1004 Application defined or object defined error. I have checked that the pivot table is called PivotTable1 and that the cursor was in the table when I opened the vbe if that matters. It is on Sheet1. make active the sheet where your pivot table is... once in vba-editor, use the short-cut {ctrl}+G to show the immediate window code pane once in the immediate window, copy/paste (or type) and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify/adapt/... the DefaultDir= string part and re-enter above line (without "?") if any doubts (or further information)... would you please comment ? hth, hector. __ previous posts __ if you ask through vba-editor / immediate window pane what/where your connection is directed to (i.e.) copy/paste (or type) the following and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify the DefaultDir= string to any path (like: thisworkbook.path) and once modified, execute the above instruction without the question mark or... you could do the above by code (i.e.) in your workbook_open event or... make some sort of tricky procedure (i.e. by ms-query) using parameters for the query and linking cells as parameters hth, hector. __ OP __ ... creating a pivot table in workbook1 from data held in workbook2 ... works just fine. Both workbooks are in the same directory. If I now move that directory either on the same computer or ... to a second computer the source data cannot be found when I refresh the pivot table. Obviously the path has changed though I do always keep the 2 files together in the one directory. I think my problem is that the data source is defined via an absolute file path but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory. Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them so long as they both stay in the same directory? As always any advice will be greatly appreciated. I am using Excel 2003 on Vista Thank you, Peter |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
Hector,
I must be being very silly but that is exacly what I did and I have tried it again but still with the same error message. I am definitely in the Immediate window ? 4+5 returns 9 and I was on the sheet with the pivot table when entered vbe. This is so frustrating as what you suggest seems to be the way to go. Any other thoughts? Thanks again, Peter "Héctor Miguel" wrote: hi, Peter ! ... I am stumbling at the first step. The code... ? activesheet.pivottables(1).pivotcache.connection when pasted into the vbe editor is giving error 1004 Application defined or object defined error. I have checked that the pivot table is called PivotTable1 and that the cursor was in the table when I opened the vbe if that matters. It is on Sheet1. make active the sheet where your pivot table is... once in vba-editor, use the short-cut {ctrl}+G to show the immediate window code pane once in the immediate window, copy/paste (or type) and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify/adapt/... the DefaultDir= string part and re-enter above line (without "?") if any doubts (or further information)... would you please comment ? hth, hector. __ previous posts __ if you ask through vba-editor / immediate window pane what/where your connection is directed to (i.e.) copy/paste (or type) the following and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify the DefaultDir= string to any path (like: thisworkbook.path) and once modified, execute the above instruction without the question mark or... you could do the above by code (i.e.) in your workbook_open event or... make some sort of tricky procedure (i.e. by ms-query) using parameters for the query and linking cells as parameters hth, hector. __ OP __ ... creating a pivot table in workbook1 from data held in workbook2 ... works just fine. Both workbooks are in the same directory. If I now move that directory either on the same computer or ... to a second computer the source data cannot be found when I refresh the pivot table. Obviously the path has changed though I do always keep the 2 files together in the one directory. I think my problem is that the data source is defined via an absolute file path but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory. Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them so long as they both stay in the same directory? As always any advice will be greatly appreciated. I am using Excel 2003 on Vista Thank you, Peter |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
hi, Peter !
I must be being very silly but that is exacly what I did and I have tried it again but still with the same error message. I am definitely in the Immediate window ? 4+5 returns 9 and I was on the sheet with the pivot table when entered vbe. This is so frustrating as what you suggest seems to be the way to go. Any other thoughts? ... revise in vba-editor (menu) tools / references... (perhaps) missed or crossed references to object libraries in the vba project ? hth, hector. ... I am stumbling at the first step. The code... ? activesheet.pivottables(1).pivotcache.connection when pasted into the vbe editor is giving error 1004 Application defined or object defined error. I have checked that the pivot table is called PivotTable1 and that the cursor was in the table when I opened the vbe if that matters. It is on Sheet1. make active the sheet where your pivot table is... once in vba-editor, use the short-cut {ctrl}+G to show the immediate window code pane once in the immediate window, copy/paste (or type) and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify/adapt/... the DefaultDir= string part and re-enter above line (without "?") if any doubts (or further information)... would you please comment ? hth, hector. __ previous posts __ if you ask through vba-editor / immediate window pane what/where your connection is directed to (i.e.) copy/paste (or type) the following and press enter to execute: ? activesheet.pivottables(1).pivotcache.connection you will be able to modify the DefaultDir= string to any path (like: thisworkbook.path) and once modified, execute the above instruction without the question mark or... you could do the above by code (i.e.) in your workbook_open event or... make some sort of tricky procedure (i.e. by ms-query) using parameters for the query and linking cells as parameters __ OP __ ... creating a pivot table in workbook1 from data held in workbook2 ... works just fine. Both workbooks are in the same directory. If I now move that directory either on the same computer or ... to a second computer the source data cannot be found when I refresh the pivot table. Obviously the path has changed though I do always keep the 2 files together in the one directory. I think my problem is that the data source is defined via an absolute file path but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory. Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them so long as they both stay in the same directory? As always any advice will be greatly appreciated. I am using Excel 2003 on Vista Thank you, Peter |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative filepaths?
Hector
I have found that if I place the files on the second machine in a folder of *exactly* the same name as they were created in then the links between the files are maintained. This is enough for my purposes. For interest I have just spent some 90 mins trying to understand why I cannot execute your code in the immediate window but with no luck. ? Activesheet.Name works fine although Intellisense does not kick in whereas it does for ? ActiveWorkbook.Name I also found that ? activesheet.pivottables(1) works ok So, I am confused and frustrated but, as I said at the top, I do have a way forward. I think I'll leave it at that and not take up any more of your time. Many thanks for you help. Peter "Héctor Miguel" wrote: hi, Peter ! I must be being very silly but that is exacly what I did and I have tried it again but still with the same error message. I am definitely in the Immediate window ? 4+5 returns 9 and I was on the sheet with the pivot table when entered vbe. This is so frustrating as what you suggest seems to be the way to go. Any other thoughts? ... revise in vba-editor (menu) tools / references... (perhaps) missed or crossed references to object libraries in the vba project ? hth, hector. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What relative row I'm in? | Excel Worksheet Functions | |||
Relative Reference help | Excel Discussion (Misc queries) | |||
Relative references | Excel Worksheet Functions | |||
Relative Reference | Excel Discussion (Misc queries) | |||
Relative reference | Excel Worksheet Functions |