Getting a filename into VBA and putting it into cells

Basically what I have is a button that creates a new column that should be 
full of linked values. When the user hits the button, he is asked to select a 
file from his hard-drive; the code then takes the name of that file and 
creates a number of links in the new column. So for example:

1) User hits button and selects the closed excel file 
C:\Tempfiles\Testsheet.xls
2) Excel creates the new column and populates it with formulas that link to 
the file; in A1 it enters 
='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters
='C:\Tempfiles\[Testsheet.xls]Budget'!$A$4
And so forth...

I've come so far as to let the user select the file, by using the code:

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Microsoft Excel Files (*.xlsx; *.xls), *.xls", _
      MultiSelect:=False, Title:="File to open")

This creates a string in VBA with the path of the file I want to open, for 
example C:\Tempfiles\Testsheet.xls. The problem is that if I want to create a 
new link, I need to add brackets around the filename, as far as I can tell, 
to separate it from the path, ie I need to change 
C:\Tempfiles\Testsheet.xls into 
='C:\Tempfiles\[Testsheet.xls]
 Any idea how I could do this?

Thanks,
Babymech

0
Utf
2/27/2010 1:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
668 Views

Similar Articles

[PageSpeed] 31

we can split the string up, and put brackets aroung the last part, and then 
put it back together.  Here is an example:

Sub brackIt()
Dim s As String
s = "fds\f4f\bbgd\nhfnh\zxc.xls"
v = Split(s, "\")
v(UBound(v)) = "[" & v(UBound(v)) & "]"
s = Join(v, "\")
MsgBox s
End Sub

-- 
Gary''s Student - gsnu201001


"Babymech" wrote:

> Basically what I have is a button that creates a new column that should be 
> full of linked values. When the user hits the button, he is asked to select a 
> file from his hard-drive; the code then takes the name of that file and 
> creates a number of links in the new column. So for example:
> 
> 1) User hits button and selects the closed excel file 
> C:\Tempfiles\Testsheet.xls
> 2) Excel creates the new column and populates it with formulas that link to 
> the file; in A1 it enters 
> ='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters
> ='C:\Tempfiles\[Testsheet.xls]Budget'!$A$4
> And so forth...
> 
> I've come so far as to let the user select the file, by using the code:
> 
>     FilesToOpen = Application.GetOpenFilename _
>       (FileFilter:="Microsoft Excel Files (*.xlsx; *.xls), *.xls", _
>       MultiSelect:=False, Title:="File to open")
> 
> This creates a string in VBA with the path of the file I want to open, for 
> example C:\Tempfiles\Testsheet.xls. The problem is that if I want to create a 
> new link, I need to add brackets around the filename, as far as I can tell, 
> to separate it from the path, ie I need to change 
> C:\Tempfiles\Testsheet.xls into 
> ='C:\Tempfiles\[Testsheet.xls]
>  Any idea how I could do this?
> 
> Thanks,
> Babymech
> 
0
Utf
2/27/2010 1:44:01 PM
You can parse the string looking for the last backslash and build the string
that way.

But if you allow the user to just select the workbook/filename, how are you
gonna be sure that there is a worksheet with the name your code wants to use:

Option Explicit
Sub testme()

    Dim FileToOpen As Variant
    Dim LastSepPos As Long
    Dim myStr As String
    Dim WksName As String
    Dim Addr As String

    FileToOpen = Application.GetOpenFilename _
                    (FileFilter:="Microsoft Excel Files " _
                        & "(*.xlsx; *.xls), *.xls", _
                    MultiSelect:=False, Title:="File to open")
                    
    If FileToOpen = False Then
        Exit Sub
    End If
    
    LastSepPos = InStrRev(FileToOpen, "\")
    
    WksName = "sheet1"
    Addr = "$a$1"
    
    '='C:\My Documents\Excel\[hi there.xls]Sheet1'!$A$1
    myStr = "='" & Left(FileToOpen, LastSepPos) _
        & "[" & Mid(FileToOpen, LastSepPos + 1) & "]" _
        & WksName & "'!" & Addr
        
    ActiveCell.Formula = myStr
    
End Sub

Babymech wrote:
> 
> Basically what I have is a button that creates a new column that should be
> full of linked values. When the user hits the button, he is asked to select a
> file from his hard-drive; the code then takes the name of that file and
> creates a number of links in the new column. So for example:
> 
> 1) User hits button and selects the closed excel file
> C:\Tempfiles\Testsheet.xls
> 2) Excel creates the new column and populates it with formulas that link to
> the file; in A1 it enters
> ='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters
> ='C:\Tempfiles\[Testsheet.xls]Budget'!$A$4
> And so forth...
> 
> I've come so far as to let the user select the file, by using the code:
> 
>     FilesToOpen = Application.GetOpenFilename _
>       (FileFilter:="Microsoft Excel Files (*.xlsx; *.xls), *.xls", _
>       MultiSelect:=False, Title:="File to open")
> 
> This creates a string in VBA with the path of the file I want to open, for
> example C:\Tempfiles\Testsheet.xls. The problem is that if I want to create a
> new link, I need to add brackets around the filename, as far as I can tell,
> to separate it from the path, ie I need to change
> C:\Tempfiles\Testsheet.xls into
> ='C:\Tempfiles\[Testsheet.xls]
>  Any idea how I could do this?
> 
> Thanks,
> Babymech

-- 

Dave Peterson
0
Dave
2/27/2010 1:48:13 PM
Reply:

Similar Artilces:

ScrollIntoView Non-Selected Cell ..?
How do i.. Without selecting it.. ScrollIntoView a Cell.. Say... R120C140 ...? ActiveWindow.ScrollintoView .. but how to find the Document Coords in Pixels of that Cell ..? when Rows/Columns are uneven sized ..? Any Feedback would be Appreciated.. Andrew ;-) sub dk() ActiveWindow.ScrollRow = 120 ActiveWindow.ScrollColumn = 140 End Sub This would put row 120, column 140 in the upper left corner of the screen. "Andrew" <NoToSpam@ReadItYourSelf.com> wrote in message news:BeydnRXlerMJfhLWnZ2dnUVZ8gOdnZ2d@bt.com... > How do i.. >...

how do I copy several lines of word text into one excel cell?
In a word doc I need to copy several lines of text and paste them into one excel cell. I can't seem to find any help telling me how to do this (each line pastes into a different cell) You can either double click the cell or press F2 key to get into edit mode for the cell, then the text pasted would all go into the cell. -Simon "jhh" wrote: > In a word doc I need to copy several lines of text and paste them into one > excel cell. I can't seem to find any help telling me how to do this (each > line pastes into a different cell) thank you SO much Simon CC - ...

curly quotes became straight, can't get curly bakc
something changed curly quotes to straight, and now I can't resume curly quotes. I assume you've already checked your AutoCorrect options to make sure replace straight quotes with curly quotes is checked, so let's look at the other simple fixes first. Have you recently changed to a new font? Some fonts don't support curly quotes. What version of Word are you using? Jerry "John Worsley Simpson" wrote: > something changed curly quotes to straight, and now I can't resume curly > quotes. And in some fonts it's really hard to recogni...

Protecting of cells
Hi Can someone help? I have been doing a spreadsheet which has some pretty complex formulas included in it. It will be used by people who don't have a great understanding of excel and all i want them to do is enter a value and allow the program to make the calculations for it. If they delete any formulas it may stuff the whole thing up. My question: Am I able to protect particular cells so that the formulae and content cannot be altered? Thanks Yes. Look up Protection in Help. First you lock the cells to be locked (that's the default), or unlock the cells where they can ...

get xml option in data menu
i can't find the xml option in the data menu? any ideas? using office 2003 sbe. ...

Define a specific Printer using VBA (Access 2000)
Hello, I am using Access 2000. (No way to change Access version for now) I know that In Access XP can be done this request. I Would like to print to a specific Printer when I open a report in NormalView without changing the default printer (I know how to reset default Printer using VBA. There is an option in PageSetup of every report "Print to a specific printer" but I would like this option to be set for every report by VBA. I thank you in Advance Kon. ...

Tab Jumps 8 Cells
When ever I click on Tab, it jumps over 8 cells. This happpens on all worksheets even new ones. Any ideas? Thanks, Brandon Brandon Have you got Transition navigation keys checked in Tools | Options ? Regards Trevor "Brandon Cramer" <brandon@molalla.net> wrote in message news:1d9a901c45495$e67fc510$a601280a@phx.gbl... > When ever I click on Tab, it jumps over 8 cells. This > happpens on all worksheets even new ones. > > Any ideas? > > Thanks, > > Brandon That worked! Thank you!!!!!!! >-----Original Message----- >Brandon > >Have y...

Searching (Find) in Merged Cells
How can you search in merged cells for text content? The Find Method works on single cells but I can't get it to work on merged cells. William, Excel treats merged cells as if they were the cell at the upper left of the merged area. In VBA: Sub SimpleFind() Dim myCell As Range Set myCell = Cells.Find("find this", , xlValues, xlPart) If Not myCell Is Nothing Then MsgBox "Found in " & myCell.Address Else MsgBox "Not Found" End If End Sub In Excel, simply select all the cells using the select all button to the upper left of A1, then use Ctrl F. HTH, B...

Setting Value Of One Cell Equal To Value Of Selected Cell
Without using VBA - If cell A1=red, A2=white and A3=blue, can I set C1 to be the value of the selected cell so that if A1 is selected, C1 will equal red, if A2 is selected, C1 will equal white, etc.? Thanks, Sheldon Potolsky Sheldon, In a word, no. No worksheet thing comes to mind that works as a function of the active cell. You might want to use some radio buttons or a list box (Forms Toobar or Control Toolbox) instead of cell selection. They'll let you click to make such choices, and you can use formulas that will change a cell value as you wish. -- Regards from Virginia Beach, ...

VBA using an array
I have an array Array(100) and I want to set Array() = 10 for 1-100 Is there another way to set = 10 rather then using For i = 1 to 100 Array(i) = 10 Next I Thanks for your help Not really. But why worry? Even if that loop went to 100000 it'd be a fraction of a second! "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:473989B9-140D-4E1C-B914-76FFA2B6BFC5@microsoft.com... > I have an array Array(100) > > and I want to set Array() = 10 for 1-100 > > Is there another way to set = 10 > > rather then using > > For i = 1 to 100 > A...

Macro to remove contents of cell and move all other contents up one row
I've very lillte or no experience of macros and got some very usefu help from a kind user of this forum a couple of days back and I'm bac looking for more help. Its a bit long winded but here goes. I need a macro which checks the contents of a couple of ranges o cells, starting with the first range of cells lets say it checks cell A4,B4,C4,D4 down to cells A20,B20,C20,D20. Now if for example cell A7,B7,C7 and D7 have values (note the formulas used in these cell means that if A7 has a value then B7,C7 and D7 will also have a valu if A7 is blank then B7,C7, and D7 will be blank) and no...

Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a scientifc equation. The numbers are rounded off after 15 characters. Cannot get Excel to display all 20 numeric numbers. Any ideas on how to get Excel to read all 20 numbers in a cell? Hi not possible. Excel only supports 15 significant digits -- Regards Frank Kabel Frankfurt, Germany "Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag news:4324528D-BBAB-493C-A1B0-EC108BCB6571@microsoft.com... > When I export data having 20 numeric characters, Excel will put in place a > scientifc e...

In excel can you select certain cells which contain the same text
If I have several cells with the same text in them can I filter these out and select them. I know you can do this for formula etc but can it be done for text? try data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ade" <ade@discussions.microsoft.com> wrote in message news:937AD9E1-668B-4E32-B194-29146DF0A60A@microsoft.com... > If I have several cells with the same text in them can I filter these out and > select them. I know you can do this for formula etc but can it be done for > text? ...

Count values either vba or formula
Sample of what I am trying to achieve: Worksheet1: (column A, B, C, D, E) - source data id,primary region, secondary region, tertiary region, status 111,americas,new york, NA, increase 111,americas,canada,NA, increase 111,americas,mexico,LATAM,increase 111,americas,peru,LATAM,decrease 112,europe,france,WEST,increase 112,europe,spain,WEST,decrease 112,europe,uk,WEST,decrease 112,europe,portugal,WEST,decrease etc.... Worksheet2: (contains summary sheet) - summarize data 111,americas,NA, increase, 2 <----- count instances found from worksheet1 111,americas,LATAM,increase, 1 111,americas,LA...

Splitting names from cells
My first question to the hordes of folks better equipped to answer than the people around me. I'm a policy advisor and I only use Excel and Access as they relate to research. I'm trying to figure out if there is a method for splitting first and last names from a cell where both are contained. We have a huge fleet database with thousands of operator names, but when some moron first set up the system, they put entire name (JOHN A DOE) in one cell. Is there a way I can search for the string following the final space, or some such thing, so I can separate the names? Thanks. -- Goe...

How can i to put a SharePoint web server in my DMZ zone.
I want to expose just one of my SharePoint web servers into my DMZ zone, how can i do this? ------=_NextPart_0001_B573D3EC Content-Type: text/plain Content-Transfer-Encoding: 7bit -------------------- Thread-Topic: How can i to put a SharePoint web server in my DMZ zone. thread-index: AcqsG05Zq7kgx6kqQe6fWBwYUxg65Q== X-WBNR-Posting-Host: 63.147.159.188 From: JACR <JACR@discussions.microsoft.com> Subject: How can i to put a SharePoint web server in my DMZ zone. Date: Fri, 12 Feb 2010 11:41:01 -0800 Lines: 2 Message-ID: <ACE6C606-D323-42E2-880B-A4815E253E87@microsoft....

In SUM cells the # sign keeps showing instead of total
AYE! my head... i'm sure it is simple, but i cannot get the SUM cells to show the currency amount. instead, the cells keep showing ####### after i enter the formula. can anyone advise me in how to stop this from happening? what's more annoying is that one SUM cell IS working properly. what have i done? thank you! frustrated student Widen the cell size Regards, Peo Sjoblom "fyremunki" wrote: > AYE! my head... > > i'm sure it is simple, but i cannot get the SUM cells to show the currency > amount. instead, the cells keep showing ####### after ...

Best way to get data from a string in to an array
looking for the best way to get the time values in to an array. I know of how it could be done using typical coding practices, but i know PS has a lot of power for doing things like this so i wanted to see what i could learn from you guys :) ## string sample Pinging 172.27.255.152 with 32 bytes of data: Reply from 172.27.255.152: bytes=32 time=1ms TTL=250 Reply from 172.27.255.152: bytes=32 time=2ms TTL=250 Reply from 172.27.255.152: bytes=32 time=4ms TTL=250 Reply from 172.27.255.152: bytes=32 time=2ms TTL=250 Ping statistics for 172.27.255.152: Packets: Sent = 4, Rece...

Get your ideas on TV
Want to know how to get Free publicity and media exposure for your product, service. idea or cause? Learn what the pros know about getting on TV, radio, and in national newspapers. Never spend another dime on advertising, ever again! go to http://www.MichaelpHart.com ...

Re: Changing a block of cells from relative to absolute references
To the kind person that posted VBA code to do this, my thanks. It appears that this is in fact the easiest (read only) way to do this. I sent it to someone that knows a bit more than me about such things (ie something :) ) and they commented ... This is a very nice tidy piece of code and should work quite well... Except missing a vital "End With" :-) Aint it always the way. :) For the sake of completeness and in the hope that it might prove useful for others, here is the code that worked : '**************************** START OF CODE ****************************** Opti...

Can't get it to print different multiple pages
I am trying to write a 150+ page book using Microsoft Publisher. It is really neat - I am enjoying myself. However I can't get the print options to work correctly. My book settings are 5.5 X 8.5 and I am showing 2 pages at a time like a regular book - that is all nice - however when I go to print a few pages - it doesn't print two seperate pages on each 8 1/2 X 11. It prints the same page twice. ie if I wanted to print pages 50-53 (I expected 2 pages) the first page would have pages 50 and 51 on it and the second page I would have 52 and 53 on it. But I am getting page 50 ...

Data cells ignored by formula
I am using the following formula =COUNTIF(M3:M13,">0") but it is ignoring cells M7 to M9. I have the same formula and data type in the columns on both sides and they are working fine. What is wrong? Liz, Those cells may actually be text - try reformatting the cells as numbers, then reenter the data in those cells. HTH, Bernie MS Excel MVP <liz.harper@s-n.com> wrote in message news:1138975265.281409.63180@o13g2000cwo.googlegroups.com... >I am using the following formula =COUNTIF(M3:M13,">0") but it is > ignoring cells M7 to M9. I have the same...

Contents of cell in footer
I want the contents of a specific cell to be in a footer, can I do this? ...

I want to copy/export a range of cells into another program
I need to copy a range of cells, then export or copy them to another program, they don't have to be functional, i.e. still in Excel format, i just need the picture of the cells. Does that make any sense? Help me if you can... -- David Try Ctrl-C top copy the cells and Ctrl-V to paste them. This should work; if it doesn't post back. If my comments have helped please hit Yes. Thanks. "David" wrote: > I need to copy a range of cells, then export or copy them to another program, > they don't have to be functional, i.e. still in Excel format...

Trouble Populating Graph Object Via VBA #3
Bottom like is that the code works if I break and then resume, but does not work otherwise. I'm coding in MS Access, but this being the Excel NG, I thought maybe somebody here might have been where I am now.... Stepping through the code, it's like the Graph.Datasheet is going "Poof!". Later in the code, I set TitleText, so the pointer to Graph is still there... but sometimes the cell assignments "stick" and sometimes they don't. It's just when I right-click the form's Chart object and select 'Edit' that I can see that the datasheet is empty -...