How do get a path name from a cell?

Greetings,

I have a macro that needs a file (from an inactive workbook) calle
�data123.xls�; but my users move this file pretty much where ever the
want on their PC. 

Is there a way to have the user fill out cell A1 (Sheet1) with the pat
name (e.g. �C:\My Documents\ACME�) and have the macro search for the xl
file according to the cell patch name given by the user?

Appreciate any hel

--
Fabl

-----------------------------------------------------------------------
Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=218
View this thread: http://www.excelforum.com/showthread.php?threadid=26668

0
10/5/2004 11:01:26 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
343 Views

Similar Articles

[PageSpeed] 3

You'd want to use a function called =indirect() to build that string and return
the value.  But the bad news is that =indirect() won't work with a closed
workbook--you get an error.

But Harlan Grove wrote a UDF that allows you to do this.
http://www.google.com/groups?selm=sH1La.4495%24cJ5.436%40www.newsranger.com

You build a formula that looks like this:

B1=Path
B2=workbook
b3=sheetname
b4=address.
(Your addresses would be different.)

=pull("'"&B1&"["&B2&"]"&B3&"'!"&B4)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Fable wrote:
> 
> Greetings,
> 
> I have a macro that needs a file (from an inactive workbook) called
> �data123.xls�; but my users move this file pretty much where ever they
> want on their PC.
> 
> Is there a way to have the user fill out cell A1 (Sheet1) with the path
> name (e.g. �C:\My Documents\ACME�) and have the macro search for the xls
> file according to the cell patch name given by the user?
> 
> Appreciate any help
> 
> --
> Fable
> 
> ------------------------------------------------------------------------
> Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2185
> View this thread: http://www.excelforum.com/showthread.php?threadid=266685

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/6/2004 12:35:52 AM
Hi
in addition to Dave's suggestion you may also download tjhe
morefunc.xll addin  (http://longre.free.fr/english) and use the
function INDIRECT.EXT:
=INDIRECT.EXT("'"&B1&"["&B2&"]"&B3&"'!"&B4)

--
Regards
Frank Kabel
Frankfurt, Germany

"Fable" <Fable.1doeqm@excelforum-nospam.com> schrieb im Newsbeitrag
news:Fable.1doeqm@excelforum-nospam.com...
>
> Greetings,
>
> I have a macro that needs a file (from an inactive workbook) called
> �data123.xls�; but my users move this file pretty much where ever
they
> want on their PC.
>
> Is there a way to have the user fill out cell A1 (Sheet1) with the
path
> name (e.g. �C:\My Documents\ACME�) and have the macro search for the
xls
> file according to the cell patch name given by the user?
>
> Appreciate any help
>
>
> --
> Fable
>
>
> ---------------------------------------------------------------------
---
> Fable's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=2185
> View this thread:
http://www.excelforum.com/showthread.php?threadid=266685
>

0
frank.kabel (11126)
10/6/2004 6:02:19 AM
Reply:

Similar Artilces:

How to get ride off this warning ?
Hi: I have a string resource defined. I need to pass this string ID to another function like this: void foo (UINT string_id) { CString str ((LPCTSTR)string_id); } During comiling, got error at casting ((LPCTSTR)string_id. But it works fine. Just wonder, how to get ride off this warning? Thanks In Advance! Polaris use reinterpret_cast<LPCTSTR>(string_id) > Hi: > > I have a string resource defined. I need to pass this string ID to another > function like this: > > void foo (UINT string_id) > { > CString str ((LPCTSTR)string_id); > } > ...

How to get free traffic to your website
The facts you should know about making money online! Hi , my name is Ben Durandt. -Discover how to get 1 Million visitors without paying a dime in advertising! -Discover how a young 31 years old man just became a millionaire on the Internet... in weeks! He paid cash for a Porsche 911 Carrera convertible and a giant domain with private lake in only 28 days. More than 20,000 people have been raging on his website every day! ... Because he just revealed HOW to do it! Heck, go there TODAY and See what he has to say! Click here : http://www.freeadguru.com/cgi-bin/i.pl?c=a&i=34180 -...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

How do I fix this so I don't get an "Error 2015"
Dim x x = Application.CountIf("E5,E6000", "T13065") How do I fix this so I don't get an "Error 2015" Thanks, Rick Not tried it but I would think x=Application.Countif(Range("E5:E6000"),"T13065") Providing T13065 is a string you are counting. If it's a range reference x=Application.Countif(Range("E5:E6000"),Range("T13065")) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "2D Rick" <rbrowniii@compuserve.com> wrote in m...

Getting an Error when there is nothing to show
-- The bottom of my report which is a statement shows #Error when there is no Invoices to add up. Can I have it blank instead. this my text Box Control Source thanks for any Help....Bob =Reports!rptOwnerPaymentMethod!subChildOwnerInvoiceAmount!tbAmount.Value Try: =IIf([subChildOwnerInvoiceAmount].[HasData], [subChildOwnerInvoiceAmount].[tbAmount]) Explanation in: Bring the total from a subreport onto a main report at: http://allenbrowne.com/casu-18.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.h...

Outlook 2003 Cant get to newsgroups
I dragged the NEWS icon from tools/customize/commands/go to the GO button on the tool bar. It worked fine then I got on a newsgroup and a posting that read "payback" multiple times. I didnot open the attachment. Next time I tryied to go to NEWS it was not there. How can I get newsgroup access back? Tryied reinstalling Outlook2003 and Office 2003 with no luck. Expect the answer to be in registry. Help please. I got the similar problem and got the fix. Here is what you need to do: - add the NEWS button under Go menu - accept Outlook Express is your default NEWS client - c...

I've got a formula for stones and pounds
Hello, I've got a column that displays a series of weights in stones & pounds (both in the one cell). How do I get a chart to recognise and display in the same format? Regards, mikelenno What is the format of the cells with stone & pounds It is best to enter such values a 10 3/14 and format the cells with # ??/14 best wishes from one who is INT(275,14) stone & MOD(250,14) lbs -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mikelenno" <mikelenno@discussions.microsoft.com> wrote in message news:5AD608FA-5367-4CED-AD76-1F54228EFDBA...

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

hyperlinking to an item in Global Address List Options
I have had success linking via html from web page to opening a specific contact record from Microsoft Outlook. I have done it like this: outlook://Big%20Contacts/Contacts/~Peter%20Crawford However, my very large contact database also has people with basically the same name, like John Smith. In the current "Address Card" view inside Microsoft Outlook, one of the John Smith's is always sorted first. If I enter outlook://Big%20Contacts/Contacts/~John%20Smith the first John Smith in the sort order is always opened. However, I want to specify one of the other John Smith's....

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

display name #2
hi i have this problem: when i send a mail massage from an application by exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and not display name (Customer Care) how can i resolve it? Thanks An application should allow you to specify display name for sender. Does it send via SMTP or MAPI? Matrix_pd wrote: > hi > i have this problem: when i send a mail massage from an application by > exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and > not display name (Customer Care) > > how can i resolve it? > > Thanks -- Please re...

I get COMException while exporting chart on IIS 6.0.
I have this code that works perfectly on my development machine when I deployed it at customer site it gets exception. Here is the error and my code: thanks error ========== Exception from HRESULT: 0x800A03EC. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC. Source Error: An unhandled exception was generated during the execution of the c...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

How to get a macro to do what i want?
I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. Calculate the last row ...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

automatically naming files for savingb
Hi, for delivery notes i would like excel to look in the relative directory for a given customer, ie if C4 contains superspecs, files relating to this account would be in c:\work\superspecs\ files are named superspecsdel0001,superspecsdel0002.......etc superspecsinv0001...etc for delivery notes and invoices. what i would like is a line (or more) to add to a macro which will look in the appropriate directory, add 1 to the last four digits of the last file, enter this number into a cell on the sheet and save the new file with the appropriate name, then close the file. is this possible, or alte...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...

change exchange name suffix
Hi We need to change our exchange server 'name' suffix. The situation is that we have a local DNS domain 'bs.si' where all our company computers and server are (Win2k srv AD) We have also external domain 'bs-group.si' that is also for delivering mail (mx record). The Exchange server name is 'posta' Now when someone connects to Exchange server from outside via port 25 it responds like '220 posta.bs.si ...', but I want that the response will be like '220 posta.bs-group.si'. So that mail that is coming from our server will be seen like that i...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

OWA and Domain Names
Hi, I added header fields so that users of my website would be directed to the default web site in IIS. However, now when trying to go to mydomain.com/exchange when I try to login as I did before it will not accept any logins. I tried KJM/loginname loginname mydomainname.com/loginname and always iwth the same password. But it accepts none of these. If I remove the headers and use the IP address it works fine. Is there a way to make teh authenticaiton work when using mydomainname.com/exchange What type of authentication are using? FBA? The use of host headers should not be causing...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...