CELL("filename") doesn't work on shortcut workbook link?

Hi,
I got a shortcut link of master workbook and I"m trying to find the location 
of shortcut linked file.

E.G.
1) C:/master file.xls
2) C:/MyFolder/shortcut to master file.xls

Now when I add this formula to shortcut file =Cell("filename") it shows 
C:/master file.xls but I'm interested in C:/MyFolder/shortcut to master 
file.xls .

Is it possible?

Thanks
Kevin

0
Utf
2/1/2010 6:37:57 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

12 Replies
790 Views

Similar Articles

[PageSpeed] 59

No. The shortcut in My Folder is simply that, a shortcut to the actual file 
which resides on the C drive. Nothing is actually being opened from the 
MyFolder.

-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kevin" wrote:

> Hi,
> I got a shortcut link of master workbook and I"m trying to find the location 
> of shortcut linked file.
> 
> E.G.
> 1) C:/master file.xls
> 2) C:/MyFolder/shortcut to master file.xls
> 
> Now when I add this formula to shortcut file =Cell("filename") it shows 
> C:/master file.xls but I'm interested in C:/MyFolder/shortcut to master 
> file.xls .
> 
> Is it possible?
> 
> Thanks
> Kevin
> 
0
Utf
2/1/2010 7:16:07 PM
So is there any workaround to solve this problem?
What I'm trying to do is:

1) I'm trying to read the contents of a folder and place them in a workbook. 
The vba code I use works fine but it only reads the filenames of native 
folder, where the master file.xls is residing.

2) So generally I copy (not shortcut) this master file.xls to the required 
directory and open from there to make it working. 

Problem is this master file is quite big and filling up h/disk for no 
reason. I thought must have smarter way to overcome this problem.

Any pointer will be useful.

Thx
Kevin
 

"Luke M" wrote:

> No. The shortcut in My Folder is simply that, a shortcut to the actual file 
> which resides on the C drive. Nothing is actually being opened from the 
> MyFolder.
> 
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "Kevin" wrote:
> 
> > Hi,
> > I got a shortcut link of master workbook and I"m trying to find the location 
> > of shortcut linked file.
> > 
> > E.G.
> > 1) C:/master file.xls
> > 2) C:/MyFolder/shortcut to master file.xls
> > 
> > Now when I add this formula to shortcut file =Cell("filename") it shows 
> > C:/master file.xls but I'm interested in C:/MyFolder/shortcut to master 
> > file.xls .
> > 
> > Is it possible?
> > 
> > Thanks
> > Kevin
> > 
0
Utf
2/2/2010 8:27:02 AM
Hi Kevin

Why not just change directory within your code with the ChDir command

ChDir "C:\MyWork\Accounts"

-- 
Regards
Roger Govier

"Kevin" <Kevin@discussions.microsoft.com> wrote in message 
news:5D6CF68B-1D58-4B85-8BB8-36B6E07853CE@microsoft.com...
> So is there any workaround to solve this problem?
> What I'm trying to do is:
>
> 1) I'm trying to read the contents of a folder and place them in a 
> workbook.
> The vba code I use works fine but it only reads the filenames of native
> folder, where the master file.xls is residing.
>
> 2) So generally I copy (not shortcut) this master file.xls to the required
> directory and open from there to make it working.
>
> Problem is this master file is quite big and filling up h/disk for no
> reason. I thought must have smarter way to overcome this problem.
>
> Any pointer will be useful.
>
> Thx
> Kevin
>
>
> "Luke M" wrote:
>
>> No. The shortcut in My Folder is simply that, a shortcut to the actual 
>> file
>> which resides on the C drive. Nothing is actually being opened from the
>> MyFolder.
>>
>> -- 
>> Best Regards,
>>
>> Luke M
>> *Remember to click "yes" if this post helped you!*
>>
>>
>> "Kevin" wrote:
>>
>> > Hi,
>> > I got a shortcut link of master workbook and I"m trying to find the 
>> > location
>> > of shortcut linked file.
>> >
>> > E.G.
>> > 1) C:/master file.xls
>> > 2) C:/MyFolder/shortcut to master file.xls
>> >
>> > Now when I add this formula to shortcut file =Cell("filename") it shows
>> > C:/master file.xls but I'm interested in C:/MyFolder/shortcut to master
>> > file.xls .
>> >
>> > Is it possible?
>> >
>> > Thanks
>> > Kevin
>> >
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4826 (20100202) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
2/2/2010 9:09:11 AM
Roger,
Actually directory name is changing all the time.
e.g.
ChDir "C:\MyWork\Customer1
ChDir "C:\MyWork\Customer2 .....etc.

So whenever I need to generate new Quote, I drag my master file to new 
customer's folder and continue.

Cheers,
Kevin


"Roger Govier" wrote:

> Hi Kevin
> 
> Why not just change directory within your code with the ChDir command
> 
> ChDir "C:\MyWork\Accounts"
> 
> -- 
> Regards
> Roger Govier
> 
> "Kevin" <Kevin@discussions.microsoft.com> wrote in message 
> news:5D6CF68B-1D58-4B85-8BB8-36B6E07853CE@microsoft.com...
> > So is there any workaround to solve this problem?
> > What I'm trying to do is:
> >
> > 1) I'm trying to read the contents of a folder and place them in a 
> > workbook.
> > The vba code I use works fine but it only reads the filenames of native
> > folder, where the master file.xls is residing.
> >
> > 2) So generally I copy (not shortcut) this master file.xls to the required
> > directory and open from there to make it working.
> >
> > Problem is this master file is quite big and filling up h/disk for no
> > reason. I thought must have smarter way to overcome this problem.
> >
> > Any pointer will be useful.
> >
> > Thx
> > Kevin
> >
> >
> > "Luke M" wrote:
> >
> >> No. The shortcut in My Folder is simply that, a shortcut to the actual 
> >> file
> >> which resides on the C drive. Nothing is actually being opened from the
> >> MyFolder.
> >>
> >> -- 
> >> Best Regards,
> >>
> >> Luke M
> >> *Remember to click "yes" if this post helped you!*
> >>
> >>
> >> "Kevin" wrote:
> >>
> >> > Hi,
> >> > I got a shortcut link of master workbook and I"m trying to find the 
> >> > location
> >> > of shortcut linked file.
> >> >
> >> > E.G.
> >> > 1) C:/master file.xls
> >> > 2) C:/MyFolder/shortcut to master file.xls
> >> >
> >> > Now when I add this formula to shortcut file =Cell("filename") it shows
> >> > C:/master file.xls but I'm interested in C:/MyFolder/shortcut to master
> >> > file.xls .
> >> >
> >> > Is it possible?
> >> >
> >> > Thanks
> >> > Kevin
> >> >
> >
> > __________ Information from ESET Smart Security, version of virus 
> > signature database 4826 (20100202) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> > 
> 
> __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________
> 
> The message was checked by ESET Smart Security.
> 
> http://www.eset.com
> 
> 
> 
0
Utf
2/2/2010 11:11:02 AM
Hi Kevin

So why are you moving the Master file?
If the code within it is changing folder before listing the files, it does 
not matter at all where the master file exists.

-- 
Regards
Roger Govier

"Kevin" <Kevin@discussions.microsoft.com> wrote in message 
news:8CD256E6-DCB6-4286-8117-839C1BA96AC1@microsoft.com...
> Roger,
> Actually directory name is changing all the time.
> e.g.
> ChDir "C:\MyWork\Customer1
> ChDir "C:\MyWork\Customer2 .....etc.
>
> So whenever I need to generate new Quote, I drag my master file to new
> customer's folder and continue.
>
> Cheers,
> Kevin
>
>
> "Roger Govier" wrote:
>
>> Hi Kevin
>>
>> Why not just change directory within your code with the ChDir command
>>
>> ChDir "C:\MyWork\Accounts"
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "Kevin" <Kevin@discussions.microsoft.com> wrote in message
>> news:5D6CF68B-1D58-4B85-8BB8-36B6E07853CE@microsoft.com...
>> > So is there any workaround to solve this problem?
>> > What I'm trying to do is:
>> >
>> > 1) I'm trying to read the contents of a folder and place them in a
>> > workbook.
>> > The vba code I use works fine but it only reads the filenames of native
>> > folder, where the master file.xls is residing.
>> >
>> > 2) So generally I copy (not shortcut) this master file.xls to the 
>> > required
>> > directory and open from there to make it working.
>> >
>> > Problem is this master file is quite big and filling up h/disk for no
>> > reason. I thought must have smarter way to overcome this problem.
>> >
>> > Any pointer will be useful.
>> >
>> > Thx
>> > Kevin
>> >
>> >
>> > "Luke M" wrote:
>> >
>> >> No. The shortcut in My Folder is simply that, a shortcut to the actual
>> >> file
>> >> which resides on the C drive. Nothing is actually being opened from 
>> >> the
>> >> MyFolder.
>> >>
>> >> -- 
>> >> Best Regards,
>> >>
>> >> Luke M
>> >> *Remember to click "yes" if this post helped you!*
>> >>
>> >>
>> >> "Kevin" wrote:
>> >>
>> >> > Hi,
>> >> > I got a shortcut link of master workbook and I"m trying to find the
>> >> > location
>> >> > of shortcut linked file.
>> >> >
>> >> > E.G.
>> >> > 1) C:/master file.xls
>> >> > 2) C:/MyFolder/shortcut to master file.xls
>> >> >
>> >> > Now when I add this formula to shortcut file =Cell("filename") it 
>> >> > shows
>> >> > C:/master file.xls but I'm interested in C:/MyFolder/shortcut to 
>> >> > master
>> >> > file.xls .
>> >> >
>> >> > Is it possible?
>> >> >
>> >> > Thanks
>> >> > Kevin
>> >> >
>> >
>> > __________ Information from ESET Smart Security, version of virus
>> > signature database 4826 (20100202) __________
>> >
>> > The message was checked by ESET Smart Security.
>> >
>> > http://www.eset.com
>> >
>> >
>> >
>>
>> __________ Information from ESET Smart Security, version of virus 
>> signature database 4826 (20100202) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4828 (20100202) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
2/2/2010 4:30:13 PM
"Roger Govier" wrote:

> Hi Kevin
> 
> So why are you moving the Master file?
> If the code within it is changing folder before listing the files, it does 
> not matter at all where the master file exists.
> 
> -- 
> Regards
> Roger Govier

Hi Roger,
I've copied my code below if that helps. I'm not sure how it will read 
contents of native folder if hardcoded ChDir ?
Cheers,
Kevin.
-------------

Sub Auto_Open()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This procedure will list, in alphabetical order, all the txt
' files that are in the same folder as the Active Workbook.
' If the active workbook has never been saved (Path = vbNullString)
' the user will be prompted to save the workbook before
' listing the files. The workbook should be saved to the same
' folder in which the text files to list reside.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DestRng As Range        ' starting point to list files
Dim FolderName As String    ' the folder containing the text files
Dim FileName As Variant     ' will contain the name of each text file
Dim SaveDir As String       ' we'll save the default drive and folder
                            ' to restore at the end of the procedure
Dim FirstCell As Range      ' saves the first cell reference
Dim LastCell As Range       ' the last cell in the file list
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Change the destination range variable DestRng to the
' cell in which you want the file list to start.
Set DestRng = ActiveWorkbook.Worksheets("MAIN SETUP PAGE").Range("AB7")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' save the first cell to use in sorting later
Set FirstCell = DestRng
' save the current drive and folder to restore at end of procedure.
SaveDir = CurDir
' if ActiveWorkbook.Path = vbNullString, the Active Workbook
' has never been saved. Prompt the user to save the workbook.
If ActiveWorkbook.Path = vbNullString Then
    FileName = Application.GetSaveAsFilename( _
        filefilter:="Excel Files (*.xls),*.xls", _
        Title:="Save the workbook to the same folder" & _
        "that contains the text files to list.")
    If FileName = False Then
        ' user clicked cancel and didn't select a SaveAs filename.
        ' get out now.
        Exit Sub
    Else
        ' user selected a SaveAs Filename. Turn off DisplayAlerts
        ' so the user won't get a "Do you want to replace..." message.
        Application.DisplayAlerts = False
        '  save the workbook
        ActiveWorkbook.SaveAs FileName:=FileName
        ' turn alerts back on
        Application.DisplayAlerts = True
    End If
    
End If
' get the folder name of the active workbook
FolderName = ActiveWorkbook.Path
' change the default drive and directory to that named by FolderName
ChDrive FolderName
ChDir FolderName
' get the first text file name
FileName = Dir("*.*")
' loop until FileName = vbNullString. The Dir() command, with nothing
' within the parentheses) will return the next text file name or
' vbNullString when there are no more text files. You cannot rely
' on Dir() to return the file names in any particular order. We'll
' sort the filenames at the end of the procedure.
Do Until FileName = vbNullString
    ' put the full filename in the destination range
    DestRng.Value = FolderName & Application.PathSeparator & FileName
    ' move the destination range down one row
    Set DestRng = DestRng(2, 1)
    ' get the next text file name from the Dir() function. If there
    ' are no more text files, Dir() will return vbNullString
    FileName = Dir()
 
Loop
' restore the saved default drive and path info
ChDrive SaveDir
ChDir SaveDir
' DestRng will refer to the blank cell after the end of the file list.
' Set LastCell one row above DestRng, so LastCell points to the end
' of the file list.
Set LastCell = DestRng(0, 1)
' now, sort the filenames, from FirstCell to DestRng. Use DestRng.Parent
' to ensure we're pointing to the proper worksheet.
DestRng.Parent.Range(FirstCell, LastCell).Sort key1:=FirstCell, _
    order1:=xlAscending, header:=xlNo

End Sub
0
Utf
2/3/2010 1:51:01 PM
Hi Kevin

The code as it stands is et to only read the contents of the folder where 
the workbook with the code is stored.
If you added the code created by Ken Puls as a function 
http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
BrowseforFolder, then you could amend your code to let the user choose the 
folder for listing

Change

' get the folder name of the active workbook
FolderName = ActiveWorkbook.Path

to

' choose the folder to be used for listing the files
FolderName = BrowseForFolder

The code for BrowseforFolder is

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
' written by Ken Puls
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level

    Dim ShellApp As Object

     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.Self.Path
    On Error GoTo 0

     'Destroy the Shell Application
    Set ShellApp = Nothing

     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function

Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False

End Function


You might also want to consider clearing your Destrange before you start, as 
otherwise any files already listed will not be overwritten if the list from 
the new folder is less than that last created.
-- 
Regards
Roger Govier
 


__________ Information from ESET Smart Security, version of virus signature database 4832 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
2/3/2010 5:46:20 PM
Hi Roger,
Thanks a lot, it works like a charm.
There are couple of very basic issues, could you pls resolve those as well. 
Sorry I'm only good in copy/pasting.

1) How can I change default folder so that popup window opens in following 
folder E:/2010/customers/  

2) I got a following macro which I use to save the file. But now It saves in 
the root folder instead of saving in specific folder which we set above with 
BrowseForFolder function.

Sub SaveAsCell()
Dim strName As String

On Error GoTo InvalidName
    strName = Sheet1.Range("B1")
    ActiveWorkbook.SaveAs strName
    
Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name.", vbCritical, " "
End Sub

Thanks,
Kevin

"Roger Govier" wrote:

> Hi Kevin
> 
> The code as it stands is et to only read the contents of the folder where 
> the workbook with the code is stored.
> If you added the code created by Ken Puls as a function 
> http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
> BrowseforFolder, then you could amend your code to let the user choose the 
> folder for listing
> 
> Change
> 
> ' get the folder name of the active workbook
> FolderName = ActiveWorkbook.Path
> 
> to
> 
> ' choose the folder to be used for listing the files
> FolderName = BrowseForFolder
> 
> The code for BrowseforFolder is
> 
> Function BrowseForFolder(Optional OpenAt As Variant) As Variant
> ' written by Ken Puls
>      'Function purpose:  To Browser for a user selected folder.
>      'If the "OpenAt" path is provided, open the browser at that directory
>      'NOTE:  If invalid, it will open at the Desktop level
> 
>     Dim ShellApp As Object
> 
>      'Create a file browser window at the default folder
>     Set ShellApp = CreateObject("Shell.Application"). _
>     BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
> 
>      'Set the folder to that selected.  (On error in case cancelled)
>     On Error Resume Next
>     BrowseForFolder = ShellApp.Self.Path
>     On Error GoTo 0
> 
>      'Destroy the Shell Application
>     Set ShellApp = Nothing
> 
>      'Check for invalid or non-entries and send to the Invalid error
>      'handler if found
>      'Valid selections can begin L: (where L is a letter) or
>      '\\ (as in \\servername\sharename.  All others are invalid
>     Select Case Mid(BrowseForFolder, 2, 1)
>     Case Is = ":"
>         If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
>     Case Is = "\"
>         If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
>     Case Else
>         GoTo Invalid
>     End Select
> 
>     Exit Function
> 
> Invalid:
>      'If it was determined that the selection was invalid, set to False
>     BrowseForFolder = False
> 
> End Function
> 
> 
> You might also want to consider clearing your Destrange before you start, as 
> otherwise any files already listed will not be overwritten if the list from 
> the new folder is less than that last created.
> -- 
> Regards
> Roger Govier
>  
> 
> 
> __________ Information from ESET Smart Security, version of virus signature database 4832 (20100203) __________
> 
> The message was checked by ESET Smart Security.
> 
> http://www.eset.com
> 
> 
> 
0
Utf
2/4/2010 2:24:03 PM
Hi Kevin

FolderName = BrowseForFolder("E:\2010\Customers\")

(Note all paths have a backslash \ not a forward slash / as you have been 
typing)

for the Saving, I think you will need to select folder again by inserting 
the line of code in your sub

Sub SaveAsCell()
Dim strName As String, FolderName as string
FolderName = BrowseForFolder("E:\2010\Customers\")

On Error GoTo InvalidName
    strName = FolderName & Sheet1.Range("B1")
    ActiveWorkbook.SaveAs strName

Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name.", vbCritical, " "
End Sub

-- 
Regards
Roger Govier

"Kevin" <Kevin@discussions.microsoft.com> wrote in message 
news:A9C12645-4EA6-4387-8F5C-5A80F2EC421F@microsoft.com...
> Hi Roger,
> Thanks a lot, it works like a charm.
> There are couple of very basic issues, could you pls resolve those as 
> well.
> Sorry I'm only good in copy/pasting.
>
> 1) How can I change default folder so that popup window opens in following
> folder E:/2010/customers/
>
> 2) I got a following macro which I use to save the file. But now It saves 
> in
> the root folder instead of saving in specific folder which we set above 
> with
> BrowseForFolder function.
>
> Sub SaveAsCell()
> Dim strName As String
>
> On Error GoTo InvalidName
>    strName = Sheet1.Range("B1")
>    ActiveWorkbook.SaveAs strName
>
> Exit Sub
> InvalidName: MsgBox "The text: " & strName & _
>        " is not a valid file name.", vbCritical, " "
> End Sub
>
> Thanks,
> Kevin
>
> "Roger Govier" wrote:
>
>> Hi Kevin
>>
>> The code as it stands is et to only read the contents of the folder where
>> the workbook with the code is stored.
>> If you added the code created by Ken Puls as a function
>> http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
>> BrowseforFolder, then you could amend your code to let the user choose 
>> the
>> folder for listing
>>
>> Change
>>
>> ' get the folder name of the active workbook
>> FolderName = ActiveWorkbook.Path
>>
>> to
>>
>> ' choose the folder to be used for listing the files
>> FolderName = BrowseForFolder
>>
>> The code for BrowseforFolder is
>>
>> Function BrowseForFolder(Optional OpenAt As Variant) As Variant
>> ' written by Ken Puls
>>      'Function purpose:  To Browser for a user selected folder.
>>      'If the "OpenAt" path is provided, open the browser at that 
>> directory
>>      'NOTE:  If invalid, it will open at the Desktop level
>>
>>     Dim ShellApp As Object
>>
>>      'Create a file browser window at the default folder
>>     Set ShellApp = CreateObject("Shell.Application"). _
>>     BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
>>
>>      'Set the folder to that selected.  (On error in case cancelled)
>>     On Error Resume Next
>>     BrowseForFolder = ShellApp.Self.Path
>>     On Error GoTo 0
>>
>>      'Destroy the Shell Application
>>     Set ShellApp = Nothing
>>
>>      'Check for invalid or non-entries and send to the Invalid error
>>      'handler if found
>>      'Valid selections can begin L: (where L is a letter) or
>>      '\\ (as in \\servername\sharename.  All others are invalid
>>     Select Case Mid(BrowseForFolder, 2, 1)
>>     Case Is = ":"
>>         If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
>>     Case Is = "\"
>>         If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
>>     Case Else
>>         GoTo Invalid
>>     End Select
>>
>>     Exit Function
>>
>> Invalid:
>>      'If it was determined that the selection was invalid, set to False
>>     BrowseForFolder = False
>>
>> End Function
>>
>>
>> You might also want to consider clearing your Destrange before you start, 
>> as
>> otherwise any files already listed will not be overwritten if the list 
>> from
>> the new folder is less than that last created.
>> -- 
>> Regards
>> Roger Govier
>>
>>
>>
>> __________ Information from ESET Smart Security, version of virus 
>> signature database 4832 (20100203) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4836 (20100204) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
2/4/2010 8:24:13 PM
Hi Roger,
FolderName = BrowseForFolder("E:\2010\Customers\") works now, I's using 
forward slashes. Is this popup window will come up everytime when I open new 
renamed workbook?

Secondly that SaveAsCell macro still doesn't work

FolderName = BrowseForFolder("E:\2010\Customers\")
it just opens popup window again.

On Error GoTo InvalidName
>     strName = FolderName & Sheet1.Range("B1")
>     ActiveWorkbook.SaveAs strName

FYI in Cell "B1" I got a formula which reads the drive letter + concatenate 
text
Formula in Cell B1 
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) + some other 
text which I use to save the workbook.

Is this stuffing up something?

As per my understanding: 
Sub Auto_Open() functions knows the Foldername where we are now that's why I 
can read the files in that specific folder
But 
Sub SaveAsCell()  macro doesn't know the current directory where we are. So 
need to pick some code from Sub Auto_Open() ??

Thanks once again Roger,
Cheers,
Kevin 

"Roger Govier" wrote:

> Hi Kevin
> 
> FolderName = BrowseForFolder("E:\2010\Customers\")
> 
> (Note all paths have a backslash \ not a forward slash / as you have been 
> typing)
> 
> for the Saving, I think you will need to select folder again by inserting 
> the line of code in your sub
> 
> Sub SaveAsCell()
> Dim strName As String, FolderName as string
> FolderName = BrowseForFolder("E:\2010\Customers\")
> 
> On Error GoTo InvalidName
>     strName = FolderName & Sheet1.Range("B1")
>     ActiveWorkbook.SaveAs strName
> 
> Exit Sub
> InvalidName: MsgBox "The text: " & strName & _
>         " is not a valid file name.", vbCritical, " "
> End Sub
> 
> -- 
> Regards
> Roger Govier
> 
>
0
Utf
2/5/2010 3:50:19 PM
Hi Kevin

The Browse for folder is giving you the opportunity to choose where you want 
to save the file.
If your folder name in cell B1 does not begin with a backslash, then change 
the line of code to be

strName = FolderName & "\" & Sheet1.Range("B1")

-- 
Regards
Roger Govier

"Kevin" <Kevin@discussions.microsoft.com> wrote in message 
news:D6B39DF7-D579-4ED8-B5D2-07DD71B1228A@microsoft.com...
> Hi Roger,
> FolderName = BrowseForFolder("E:\2010\Customers\") works now, I's using
> forward slashes. Is this popup window will come up everytime when I open 
> new
> renamed workbook?
>
> Secondly that SaveAsCell macro still doesn't work
>
> FolderName = BrowseForFolder("E:\2010\Customers\")
> it just opens popup window again.
>
> On Error GoTo InvalidName
>>     strName = FolderName & Sheet1.Range("B1")
>>     ActiveWorkbook.SaveAs strName
>
> FYI in Cell "B1" I got a formula which reads the drive letter + 
> concatenate
> text
> Formula in Cell B1
> =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) + some other
> text which I use to save the workbook.
>
> Is this stuffing up something?
>
> As per my understanding:
> Sub Auto_Open() functions knows the Foldername where we are now that's why 
> I
> can read the files in that specific folder
> But
> Sub SaveAsCell()  macro doesn't know the current directory where we are. 
> So
> need to pick some code from Sub Auto_Open() ??
>
> Thanks once again Roger,
> Cheers,
> Kevin
>
> "Roger Govier" wrote:
>
>> Hi Kevin
>>
>> FolderName = BrowseForFolder("E:\2010\Customers\")
>>
>> (Note all paths have a backslash \ not a forward slash / as you have been
>> typing)
>>
>> for the Saving, I think you will need to select folder again by inserting
>> the line of code in your sub
>>
>> Sub SaveAsCell()
>> Dim strName As String, FolderName as string
>> FolderName = BrowseForFolder("E:\2010\Customers\")
>>
>> On Error GoTo InvalidName
>>     strName = FolderName & Sheet1.Range("B1")
>>     ActiveWorkbook.SaveAs strName
>>
>> Exit Sub
>> InvalidName: MsgBox "The text: " & strName & _
>>         " is not a valid file name.", vbCritical, " "
>> End Sub
>>
>> -- 
>> Regards
>> Roger Govier
>>
>>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4839 (20100205) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4839 (20100205) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
2/5/2010 11:02:29 PM
Hi Roger,
I had to tweak a bit but eventually it's working. Thank you very much for 
your help, I got more than what I asked for. Glad to sign off ...
Cheers,
Kevin

"Roger Govier" wrote:

> Hi Kevin
> 
> The Browse for folder is giving you the opportunity to choose where you want 
> to save the file.
> If your folder name in cell B1 does not begin with a backslash, then change 
> the line of code to be
> 
> strName = FolderName & "\" & Sheet1.Range("B1")
> 
> -- 
> Regards
> Roger Govier
> 

0
Utf
2/6/2010 7:33:01 PM
Reply:

Similar Artilces:

Excel issue saving files to hexadecimal filenames on terminal server...
Hi, I have a customer who has an issue with Excel. He has people connecting to a terminal server where the spreadsheets and excel is installed and they open the sheet, make changes, and then save/close the sheet. The issue is that sometimes the saved sheet ends up with an 8 digit hexadecimal filename. The users do not get an error and we don't see anything in an eventlog. Any ideas? We read something about excel and the way it saves files. First to the hex name, then it deletes the original file, then it renames the hex name to the normal name. I have to assume something is m...

Locating file://<filename>
My program needs to get the following file "file://<filename>. Can someone advise where is actually located? Is it the default folder, the root folder, the IIS folder. Anyhow I couldn't locate the file . Thanks Ronny Hi Ronny, > My program needs to get the following file "file://<filename>. > Can someone advise where is actually located? > Is it the default folder, the root folder, the IIS folder. > Anyhow I couldn't locate the file . Each process has a current directory. I would expect the above url to access that directory. You can get it by ...

Re: Unable to open Excel/XLS files with very long paths/filenames.
No one knows? :( On 2/11/2010 1:29 PM PT, Phillip Pi typed: > Hello. > > Does Office 2007's Excel have a problem in not being able to open very > long paths/filenames? Excel says it could not find that file. I tested > several scenarios: > > c:\Users-Phillip-Pi-whatever-whatever.whatever.what1234-phillip-piSomethingSomething-Depot-testing1234567890abcdefghijklmnopqrtuxyz12345678901234567890foobatestblahblahtest12345678901234567890-12\1234567890abcdefghi.xls > = 219 characters failed. > D:\Users-Phillip-Pi-whatever-whatever.whatever.what1234-phillip-piSomething...

Filename cannot be accessed
I am using Excel 97 with Windows 98. I am able to open, edit and save Word 97 and PowerPoint 97 documents. However, everytime I try to open ANY Excel 97 document, I receive a message stating, "Filename cannot be accessed. the file may be read only or you may be trying to access a read only location or the server the document is stored on may not be responding." I then have to save it locally and work on it there. However, if I put it back on the network, the same issue exists. Any ideas? Thanks! Lisa One possible cause....... Excel stores in Windows\Temp(win98) fold...

missing filenames in Excel 2000
All of a sudden, when I open workbooks in Excel 2000, I can no longer see the name of the file that I am looking at in the upper left hand corner of the display. The bottom of the screen, containing the left/right scroll arrows runs off the page as well. I have no idea why this is the case all of the sudden but it is driving me crazy. Anyone ever see this before and/or know how to fix this prob? When I have several versions of the same spreadsheet open in different windows I need to see the filename so I know which version I am looking at. I no longer have that luxury. Please h...

how do i display the "path" and filename?
hi, whats the function where it shows the location of the file? th -- Message posted from http://www.ExcelForum.com Hi try the following: File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) HTH Frank > hi, > > whats the function where it shows the location of ...

Outlook 2003 hangs when receiving attachments with non-Western (Simplified Chinese) characters in the filename
We have a problem with Outlook 2003 hanging / locking up when receiving certain emails. We have narrowed it down to emails with attachments containing non-Western characters in the attachment's filename; specifically, Simplified Chinese characters. I should also note if you rename the attachment to remove the non-Western characters before attaching/sending, there is no lock up. If we use Task Manager to kill Outlook and relaunch, everything is fine (meaning, the "suspect" emails are visible and they can be opened / read / replied to / etc). The type of attachment appea...

1 appended to Excel filenames
Good day. When I open a spreadsheet, a 1 is being appended to the end. For example, when I open test.xls it is opening as test1.xls. When I hit Save I get a Save As dialog box, as the file name does not exist yet. Any ideas? Thanks Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Excel user" <Daniel Druff@discussions.microsoft.com> schrieb im Newsbeitrag news:AC0B7294-ACF3-425F-B153-E275DADCE235@microsoft.com... > Good day. > When I open a spreadsheet, a 1 is being appended to the end. For example, > when I open test.xls it is opening as test...

Filename field path display with mapped drive
How do I get MS Word to display the complete path when I use the filename field. When I check the box to include the path, it displays the mapped drive letter and abbreviated path, not the complete path. For example, MS Word 2007 now displays the path as y:\mfg\tooling guide.doc instead of the full path \\svr05\Documents\mfg\tooling guide.doc I did not have this problem in Office 2003. I'm now running Office 2007 and Windows 7 Quote from June 2009: -------------------- The registry setting described in http://support.microsoft.com/kb/322955 still seems to function in ...

Automated way to Replace a filename link with a new filename
I have an Excel file that is a summary file of select pieces of dat from 40-60 other Excel subsidiary files. Every month we add a few ne files that need to be added on to this summary tab. The thought process I was using was to do a macro that takes a row o data linked to a template, copy & paste the row, then replace the link on the new row from the template file to the newly created file. I am trying to figure out a simple (BY SIMPLE I MEAN VERY SIMPLE) wa for people in my group to be able to replace this link. I have create a macro that does all of the steps (including an input box...

Automatic filename generation template
Hi Is it possible to make a template that will create a workbook with the current date as filename if it doesn't exists, or just opens the file if it does? Astrodude shared this with us in microsoft.public.excel.misc: > Hi > Is it possible to make a template that will create a workbook with > the current date as filename if it doesn't exists, or just opens the > file if it does? This is how I do it weekly: (and I'm too lazy to edit the code for readability) -----CODE STARTS HERE----- Option Explicit Private Const mstrInitialen As String = "AVG" Private C...

Displaying Filename and File Path, best way to deploy to organisation Excel2K ?
Hi all, What's the best and easiest way to deploy the ability to insert the Filename and Filepath to our organisation ? any help would be greatly appreciated. cheers, Adam Try a formula =CELL("filename",a1). This displays the file name and path the next time the file is saved. Also see help for the cell worksheet function. I don't think a1 is critical, it just has to be a cell in the worksheet. Hope this helps. Paul >-----Original Message----- >Hi all, > >What's the best and easiest way to deploy the ability to insert the Filename >and Filepath ...

Filename length in Excel limited to 92 characters
Is the filename length in Excel (office XP) limited to 92 characters? I have found this limitation today and do not no whether it is a bug or not. Just tried this, I can easily go beyond 100 as the file name. Could be a specific set-up on you ur machine. Out of curiosity how does one end up finding that out? Are gonna save a filename with more than 100 letters? if so why...what would the file be called. (Totally curious here) J "PARaine" wrote: > Is the filename length in Excel (office XP) limited to 92 characters? I have > found this limitation today and do not no whe...

WaitForMultipleObjects, file change notification, filename
How do you get the filename that has changed on a FindNextChangeNotification or am I using the wrong function? I have done this before but lost the code. The examples I found using google used ReadDirectoryChangesW, but I don't remember using that, I remembered getting the changenotification giving me the filename. I am using Visual C++ 6.0. "amccombs" <amccombs@discussions.microsoft.com> wrote in message news:672715D4-9748-4E4C-AD29-6F233EC423F3@microsoft.com... > How do you get the filename that has changed on a > FindNextChangeNotification > or am I u...

using excel to change filenames
i have several hundred files on my HD for a certain program i use and i would like to change all of their filenames, is it possible to import them into a spreadsheet, manipulate the file names with some formulas ans use the manipulated names to rename the files? spence It's fairly easy to do it in DOS, but it would mean doing it one directory at a time. Is that any use? Andy. "spence" <spence524@carolina.rr.com> wrote in message news:1a2d01c352b0$9ca90470$a001280a@phx.gbl... > i have several hundred files on my HD for a certain > program i use and i would like ...

Macro for automatically adding filename without .doc
I have a document that I would like to embed the filename into the header. The first page of the document has a blank header & the second page has the same header as the remainder of the document. I initially inserted the field [file name] with Uppercase but it included the ".doc" at the end which I would like to remove. I looked online and most sites say this can't be done. (Plus the field must be updated to take effect) Instead is it possible to insert a macro that upon saving the file would create a text box in both headers with the filename and the last 4 lett...

save as filename
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveWorkbook.SaveAs FileName:=ActiveSheet.Range("o6") End Sub I am using the above code to save on a click. However, I am pulling th file from a toolbar on Attachmate EXTRA!. I have to open excel vi c:\microsoft excel, but I want it to save to J:\agency. No matter what I try, it keeps wanting to save to C:\My Documents. Can anyone show me how to have this save to J:\agency? Thanks in advance. Vat -- Message posted from http://www.ExcelForum.com Would this work? Private Sub Worksheet_SelectionChange(ByVal Tar...

How do I make the filename in a link variable based on a cell valu
Hello, Please bear with me - I am a long term excel user but I am new to Questions and I may not have phrased this very well.. I am using Excel 2000 on a work computer. I apologise if this has already been answered - I did see some similar questions but none which explained how to do this. I have made an application form spreadsheet to send out to new teachers. When they are sent back they are saved in the same folder under teachers' name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from the form in a single column in a hidden sheet called Data in each workbook....

Using Date in Filename
Can anyone tell me how I can use a date in a filename in an Excel Macro I keep getting a problem with the "/" in the date and cannot see how to get around it. Your help would be appreciated. Philip Amey "Phil Amey" <philamey@btinternet.com> wrote in message news:3F67186F.6793CCCF@btinternet.com... > Can anyone tell me how I can use a date in a filename in an Excel Macro > > I keep getting a problem with the "/" in the date and cannot see how to get around it. > > Your help would be appreciated. > > Philip Amey > Try a different ...

Access 2007 Filename Length
In Access 2007, trying to import a legit dbf file which has a file name greater than 8 characters produces an error. Going to External Data, Import, More, dBASE file if you select a legit dbf file with a file name greater than 8 characters, it gives you an error "The Microsoft Access database engine could not find the object 'zeroreserves05q5'. Make sure the object exists and that you spell its name and the path correctly." The error code is 503011. If that exact same dbf file is simply renamed to 8 or fewer characters, it imports perfectly. I have not been able to find ...

attachment filename "cut off" when printing
Is there any way to adjust the space allowed for email attachments (icons and filenames) when printing? Most of my attachment filenames get truncated followed by "..." Is there any way when printing in memo style to print the whole filename (upto X characters)--and even loose the icon? Thanks for any help-- Wood ...

Problem with long filename in Win 98 while reading the filename from command line using function ParseCommandLine()
Hi All, I am developing an MDI application using MFC. I am facing a problem related to long file name in Windows 98. When I drags a file with longer name of 8 characters (say abcdefghijk.txt), on the icon of my application on the desktop. My application opens the file with modified filename, eg. abcdef~2,txt. This problem does not exist on Win2K. BOOL CIcadApp::InitInstance() { CCommandLineInfo cmfInfo; ParseCommandLine(cmdInfo); ....... Is their any solution to this problem ? Thanks, M# This is not really a problem. MS-DOS does not know if the target can handle l...

Checking FileName
I want to have a macro that is utilised when my workbook is opened. What I need it to do is, look at the current filename, if it is still the same as the template filename, then bring up a message box that will have the option of going to the save as.. function, or clicking cancel will just open the book as normal. If however, the name has changed from the original title, then it will just load up without having a message box. Any help will be greatly appreciated. Andy, If you're really using an Excel template (saved as a template -- xlt file extension), you won't need to do ...

Retrieve filename question, again...
In an other topic I asked about how to retrieve the filename from an Excelfile. Many solutions followed. I tested many of them at home but, when i got to work they didn't work anymore... When i use the function =cell("filename") and i type this into the function wizard, it gives "volatile" as result. When i continue and enter this in a cell, it shows.. nothing... Does this have anything to do with the fact that the title of the spreadsheet is generated by a database program? We get these excel files from within a Lotus Notes environment. Let's say i have this spre...

What does the [Group] designation mean, next to my filename?
I cannot find why this [Group] designation appears next my filename? Seems to be disallownig me to change print areas, and other settings?? You have more than one sheet selected. Right click any sheet, and if available, select the "Ungroup sheets" option. -- Regards Juan Pablo Gonz´┐Żlez "chise@nwbo.com" <chise@nwbo.com@discussions.microsoft.com> wrote in message news:1C0785F9-0EF4-42A4-B9DC-508B26688776@microsoft.com... > I cannot find why this [Group] designation appears next my filename? Seems > to be disallownig me to change print areas, and other sett...