Check if Excel file is already open

I need to check if an Excel file is already open.
How do i do this?

thank u


0
Robert
12/27/2009 6:14:33 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1371 Views

Similar Articles

[PageSpeed] 46

Hi

Look at this:

Sub Test()
If Not IsWbOpen("Book1.xls") then
    Workbooks.Open("Book1.xls")
End If
End Sub

Function IsWbOpen(WbName As String) As Boolean
For Each wb In Application.Workbooks
    If wb.Name = WbName Then
        IsWbOpen = True
        Exit For
    End If
Next
End Function

Regards,
Per

"Robert Crandal" <nobody@gmail.com> skrev i meddelelsen 
news:SpEZm.119911$gg6.52009@newsfe25.iad...
>I need to check if an Excel file is already open.
> How do i do this?
>
> thank u
>
> 

0
Per
12/27/2009 8:11:28 AM
Hi Per Jessen

Does your function need to have the path along with
the filename itself??  I'm just curious if it would better
to use "C:\Mydata\Book1.xls" as opposed to just
"Book1.xls" as the parameter to your function.

thank u


"Per Jessen" <per.jessen@mail.dk> wrote in message 
news:%233TuwwshKHA.1456@TK2MSFTNGP06.phx.gbl...
> Hi
>
> Look at this:
>
> Sub Test()
> If Not IsWbOpen("Book1.xls") then
>    Workbooks.Open("Book1.xls")
> End If
> End Sub
>
> Function IsWbOpen(WbName As String) As Boolean
> For Each wb In Application.Workbooks
>    If wb.Name = WbName Then
>        IsWbOpen = True
>        Exit For
>    End If
> Next
> End Function
>

0
Robert
12/27/2009 9:07:08 AM
Try this

Sub nn()
MsgBox IsBKOpen("MyBook.xls")
End Sub


Function IsBKOpen(ByRef BName As String) As Boolean
On Error Resume Next
IsBKOpen = Not (Application.Workbooks(BName) Is Nothing)
End Function

Mike

"Robert Crandal" wrote:

> I need to check if an Excel file is already open.
> How do i do this?
> 
> thank u
> 
> 
> .
> 
0
Utf
12/27/2009 10:49:01 AM
Hi Robert

No path needed in my function.

Regards,
Per

"Robert Crandal" <nobody@gmail.com> skrev i meddelelsen 
news:%sFZm.12952$ft1.4182@newsfe10.iad...
> Hi Per Jessen
>
> Does your function need to have the path along with
> the filename itself??  I'm just curious if it would better
> to use "C:\Mydata\Book1.xls" as opposed to just
> "Book1.xls" as the parameter to your function.
>
> thank u
>
>
> "Per Jessen" <per.jessen@mail.dk> wrote in message 
> news:%233TuwwshKHA.1456@TK2MSFTNGP06.phx.gbl...
>> Hi
>>
>> Look at this:
>>
>> Sub Test()
>> If Not IsWbOpen("Book1.xls") then
>>    Workbooks.Open("Book1.xls")
>> End If
>> End Sub
>>
>> Function IsWbOpen(WbName As String) As Boolean
>> For Each wb In Application.Workbooks
>>    If wb.Name = WbName Then
>>        IsWbOpen = True
>>        Exit For
>>    End If
>> Next
>> End Function
>>
> 

0
Per
12/27/2009 10:54:23 AM
Just to add to Per's response.

His function doesn't need and can't use the path. 

This kind of thing would fail to do what you want:
workbooks("C:\somepath\somename.xls")

The workbooks collection only cares about the name of the workbook.

You could enhance Per's function to look to see if there is any book1.xls open. 
And if there is, look to see if it's the one from the folder you want.

Option Explicit
Sub testme()
    MsgBox IsWorkBookOpen(myPath:="C:\my documents\excel", _
            WkbkName:="Book1.xls")
End Sub
Function IsWorkBookOpen(myPath As String, WkbkName As String) As String

    Dim TestWkbk As Workbook
    Dim myMsg As String
    
    Set TestWkbk = Nothing
    On Error Resume Next
    Set TestWkbk = Workbooks(WkbkName)
    On Error GoTo 0
    
    If TestWkbk Is Nothing Then
        myMsg = "Not Open"
    Else
        'remove the trailing slash if there
        If Right(myPath, 1) = "\" Then
            myPath = Left(myPath, Len(myPath) - 1)
        End If
        If LCase(myPath) = LCase(TestWkbk.Path) Then
            myMsg = "It's Open!"
        Else
            myMsg = "A file with the same name is open!"
        End If
    End If
    
    IsWorkBookOpen = myMsg

End Function

You could just pass it the fullname (path and name) and do some parsing in the
function if you wanted.



Robert Crandal wrote:
> 
> Hi Per Jessen
> 
> Does your function need to have the path along with
> the filename itself??  I'm just curious if it would better
> to use "C:\Mydata\Book1.xls" as opposed to just
> "Book1.xls" as the parameter to your function.
> 
> thank u
> 
> "Per Jessen" <per.jessen@mail.dk> wrote in message
> news:%233TuwwshKHA.1456@TK2MSFTNGP06.phx.gbl...
> > Hi
> >
> > Look at this:
> >
> > Sub Test()
> > If Not IsWbOpen("Book1.xls") then
> >    Workbooks.Open("Book1.xls")
> > End If
> > End Sub
> >
> > Function IsWbOpen(WbName As String) As Boolean
> > For Each wb In Application.Workbooks
> >    If wb.Name = WbName Then
> >        IsWbOpen = True
> >        Exit For
> >    End If
> > Next
> > End Function
> >

-- 

Dave Peterson
0
Dave
12/27/2009 2:04:03 PM
Reply:

Similar Artilces:

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

How can I delete PART of a .wmv file?
I have recorded an interview. Unfortunately, I didn't properly turn the recording off and I captured an additional 3 hours of my daily life that I don't want to archive with this recording. How can I do that? You need an audio editor A nice piece of freeware like http://audacity.sourceforge.net/ will do -- http://thewelltemperedcomputer.com "kenyadee" wrote: > I have recorded an interview. Unfortunately, I didn't properly turn the > recording off and I captured an additional 3 hours of my daily life that I > don't want to archive ...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

Outlook (and Excel) stops working after MSXML installation
I installed MSXML 4.0 as per the "critical updates" message on my W98 today. After re-boot, when I try to open Outlook I get message "Unable to open your defauilt mail folder - the information store could not be opened" and then "Would you like to open your default file system folder instead?" - this is no good and I can't find anything. I can't open Excel either, I get messages about Visual Basic and when I close it, I get message "This process will stop the debugger". I now have a shortcut on my desktop "Microsoft MXL 4.0 parser&...

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

Easiest way to create forms with Excel?
Hi all - apologies if this is a FAQ question, was unable to find good answers there. My organization often needs to distribute various 'forms' designed in Excel. People will fill them in, (often print a copy and) send the filled-in sheets back to us. There are a lot of problems with this: people will modify stuff where they're not supposed to, will leave 'mandatory' options blank, and the process of copying/pasting data for further processing once we get the filled in sheet is laborious. For various reasons we need to stay with Excel (i.e. Access, or redoing everything a...

Outlook would not open...
I am running Outlook 2002. As soon as It opens , I get a message: Microsoft Visual C++ Runtime Library. Runtime Error! c:\program files\microsoft office\office10\outlook.exe When I click OK, outlook shuts down. Thanks ...

I cannot open Word files for no apparent reason
I can't open files that I've used frequently, recently...then I can't close the blue window! I have to End Task. Possibly it may be the problem below. However, if this doesn't work, please let us know the version of Word and Windows and are they patched fully up to date? Also, are you able to open Word but not the documents? Details were sadly lacking in your post. That's a symptom of a Word Data Key corruption - usually because Word has been running when updates are installed. Run Regedit and expand by double-clicking to the key below. With DATA selecte...

Excel won't load
Excel begins to load(I see my name below logo), but then closes with 'Microsoft Excel has encountered a problem and needs to close'. I can, however, open Excel only in Safe Mode but only in the Administrator log-in, not my own user acct. I am not part of network. Other Office XP programs ok. Files are there as I can copy them and open on another computer. I have run several virus/adware scans, tried clean boot(not sure if successful), have deleted all .xlb files, I had no add-ins checked. There are no files in any of my 3 XL Start folders. I have donwloaded latest Installer 3.0...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

setting a dedicated place to open an "xls" file
Gentlemen, I do work at the office evey day on spread sheets and save to a disk, which I bring home. Is there any way that I can "lock in" a dedicated point to retrieve my files once I have saved them to "c:\documents & Settings\my documents. When I click "Open and go to "A" drive to get the file from the disk that I took home, my computer "remembers" that this was the last place I opened a file from. And when I am in Excel and want to open a file, the computer looks in "a". Thanks Raymond Tools / Options / General Tab - Default File loc...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

Excel; tabbing through protected worksheet
I have a protected worksheet with several sections of merged cells. When selecting cells prior to protecting I select in the order I want the user to tab. When leaving the first merged cell by tabbing, and the user is moved to the next cell tabbing from that cell bring you back to the previous merged cell. I am thinking that Excel thinks it needs to go to next row of the merged cell. Can I get over this hurddle and convince Excel to continue moving forward? Easy workaround. Remove the merged cells which cause no end of problems and limit functionality...............one ...

ShellExecute() multiple files to open to one instance
I am opening DWG files to AutoDesks TrueView. When i loop to open these files, a new instance is created for each file i open. Is there a way to create a process and send files to it only? Or a registry key in windows i can set to not allow multiple instances of this program? My goal is to open multiples files to one instance of this viewer, it can handle many files at once. Thanks! check if the program has any command line switch or automation interface to reuse an existing instance. -- Regards Sheng Jiang Microsoft Most Valuable Professional in Visual C++ http://www.jiangsheng.net ...

Linking Chart items to files
Anyone got a clue on how to link each stacked bar(or similar) to a different file, so when someone views the file, he can click on the bar, to get a relevant document on his web-browser/similar(pdf, whatever). Any suggestions? You can write event procedures that determine which point was clicked. Within the procedure you need some kind of lookup to see what document should be launched according to the point which was clicked. Here's an article on chart events: http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

Trendlines in Excel
I would like to use the equation of a trendline through a set of datapoints to interpolate a value from a chart in Excel. Is there a way to pull the trendline equation into a cell to return a value? The data set could change, so entering an equation for the trendline will not really work well. Any help would be appreciated. See Trendline coefficients http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm as well as the lightly tested http://groups- beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr ead/3186b43d9dc84ebd/0eda30f29434786d?q=trendline+group...

Mail merge and excel
I need to merge a list of names, adddresses, and phone numbers from an excel spreadsheet and print them on a directory booklet with color photos using Publishers 2003 . Could anybody point me to the right directionss. I will be great if someone could work me throught vis telephone. Caveboy wrote: > I need to merge a list of names, adddresses, and phone numbers from > an excel spreadsheet and print them on a directory booklet with color > photos using Publishers 2003 . Could anybody point me to the right > directionss. I will be great if someone could work me throught vis > t...

pop up error from Outlook, window opens in tab
Hello, I'm using crm 4.0 with the Outlook client on about 20 computers. On 4 of them, all with Vista, when a button is pushed that would open a CRM window (such as advanced search) the window opens in a tab inside an existing internet explorer instance. If there is no existing internet explorer, one is started and the window is opened in a full screen browser instead of the smaller window it should start in. Outlook then gives an error message stating that it has been blocked, likely by a pop up blocker. The pop up blocker has been disabled, no 3rd party plug-ins are running...

excel 97 problems
Each time i try to save my changes i get a message stating there has encountered a problem and needs to shut down. My pc is on a local network, where I am primary. The other machine can save without a problem and those changes do appear on my machine but I am unable to save Clean out your temp files, clean out your Temp Internet Files (include offline content) "Ken" <cls@peoplepc.com> wrote in message news:001801c381e4$d0ed8720$a301280a@phx.gbl... : Each time i try to save my changes i get a message stating : there has encountered a problem and needs to shut down. My : p...

Linking workbooks in Excel
I'm trying to link two workbooks. The information I enter in the first workbook is the master copy and I would like this same information to be updated in the second workbook. I use the second workbook as the updated status which I send to clients. I usually create two workbooks and then enter the information twice, once in the master workbook and again in the client's copy - tedious and incorrect, I'm sure. Thank you for your help. Just some thoughts .. if the 2 books were identical (probably not?), just send over a copy* of your master would satisfy ? *say, a frozen copy ...

Opening Excel Problem
Reply to windsurf222@hotmail.com Hi I was trying to open excel on my Pentium 2, windows 98. I have office 2000 premium, the excel version. As soon as I click on the icon, and the the opening page appears, all of a sudden the screen changes rapidfire speed and it starts going thru the sheets up to about 500 or however much it goes up to. I can stop it by cntrl, alt, dlt and them hitting end task. What is going on ? I tried uninstalling and reinstalling same thing. If I look at excel on the cd, it's fine. I turned off norton anti virus. George Try opening Excel in safe mode, then go to t...

offline files
My laptop is set up to connect with the company lan at work. When I take the laptop home, some of my email files are available and some are not. This is aggravating when I'm at a meeting and expect to find the email with the previous minutes, but can't. What can I do? Check your sync settings while online, and make sure it's set to sync *all* the folders you want - and to do so automatically while online every X minutes, and automatically when you close Outlook. Note - non-default folders will need to be setup for offline use manually, and new subfolders will not inherit thos...

Can I create custom forms to add data to a Excel Spreadsheet? How.
see this site http://www.contextures.com/excelfiles.html#UserForm it has a sample WB that you download. good online instructions. >-----Original Message----- > >. > ...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...