RSS

Tag Archives: msexcel

Excel: GetFilePath()

Somethings you need to get the current file path of the active workbook. Here is a function you can use. It returns a folder path ending with “\”, e.g. c:\winnt\system32\.

Public Function GetFilePath()

Dim FullPathFN

FullPathFN = ActiveWorkbook.FullName
GetFilePath = Mid(FullPathFN, 1, InStrRev(FullPathFN, “\”))

End Function

 
Leave a comment

Posted by on March 16, 2007 in Scripts

 

Tags:

Excel: SheetExists()

Excel VBA does not provide a method to checking if a worksheet exists or not. A simple function below provides one such solution:

Public Function SheetExists(ByVal SheetName)

Dim wsTestSheet As Worksheet

On Error Resume Next
Set wsTestSheet = Sheets(SheetName)

If wsTestSheet Is Nothing Then

SheetExists = False

Else

SheetExists = True

End IfSet wsTestSheet = Nothing
On Error GoTo 0

End Function

 
Leave a comment

Posted by on March 16, 2007 in Scripts

 

Tags:

Slow copy & paste in Excel 2002

One of the trading support guys have an excel application that has the following problem:

– When logged on via a citrix session, trying to copy (via Ctrl-C) a cell with formula hangs excel for about 20 seconds or so.

– When logged on via a terminal session, the same operation is almost instantaneous.

We could easily blame the additional citrix session for slowing down excel, but I found that if I use the menu, file | Edit | Copy, excel does not hang!

Even though these 2 operations are supposed to do the same things, it appears that they affect excel differently. Server is a W2K running Citrix Metaframe XP. Yes, the application has a lot of links and formula, but if that is the case any copy operation would show similar slow response, but it doesn’t

So far I have not found any solution to this problem yet, except to recommend using the file menu as a workaround. If you know of any or if you know what is the difference between a CTRL-C and a File | Edit | Copy, do drop me a comment.

Thanks!

 
1 Comment

Posted by on March 8, 2007 in Windows

 

Tags:

MS Excel showing wrong user locking file

Occasionally, I will receive trouble tickets from the helpdesk that some user ID is locking an excel file, but either that user does not have that XLS file opened or the user ID has been disabled long ago. Occassionally, I get this coming my way too. My account is being shown (wrong) as the one locking the XLS file and the user can only open it in READ-ONLY mode. Most of these files are new to me and there are no reasons for me to open them in the first place!

Apparently, MS Excel would sometimes wrongly report the user ID when a file is being locked. The best way to know who actually locked that file is to look for the open files in your compmgmt.msc and see who has read+write access to that file.

Here is an interesting discussion on it. According to this, the name reported is from the applicaton options, but why would my ID appear on someone else’s computer (I will need to use that computer’s excel for my name to appear, right?) in another country, although we are in the same domain.

If anyone has a clue please let me know.

 
Leave a comment

Posted by on January 3, 2007 in Windows

 

Tags: