+ Reply to Thread
Results 1 to 6 of 6

Refer to a particular workbook

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Refer to a particular workbook

    I have the following code that is intended to save and close a particular workbook after 30 mins of inactivity. I get an error however when it is run and I think its because I have other excel workbooks open. I think i need to reference the workbook I am interested in but dont know how to do so. The workbook I want to refer to is P:\Departments Online\Property\Activity\Activity.xlsm

    Sub Inactivity()
    Dim x As Integer
    Dim MyTxtFile
    
    x = Application.Match(Environ("username"), ActiveWorkbook.Sheets("Users").Columns("A"), 0)                                <<<<<<Error is on this line of code
            FullName = ActiveWorkbook.Sheets("Users").Cells(x, 2)
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile("P:\Departments Online\Property\Activity\Inactivity.txt", True)
        a.WriteLine (FullName & " you have been kicked out of Activity due to over 30 mins of Inactivity. Please close this message.")
        MyTxtFile = Shell("C:\WINDOWS\notepad.exe P:\Departments Online\Property\Activity\Inactivity.txt", 1)
        End Sub

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Refer to a particular workbook

    If the error is with the line Application.Match, it is only possible if the username is not listed on col. A of Users Sheet assuming that your Activeworkbook has a sheet named "Users".
    Manually check if it meets all three requirements i.e. Activeworkbook contains a sheet Users and col. A of this has the username listed.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Refer to a particular workbook

    sktneer

    My workbook does have a sheet named Users, but my issue is (I believe) that the fact is I have many workbooks open at once, so the "active" workbook at any one time is not the one with the sheet called Users on it. Which is why i want to specifically reference the workbook instead of the active one.

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Refer to a particular workbook

    You're welcome Nick!
    Hope that will resolve this issue.

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Refer to a particular workbook

    In that case user Thisworkbook instead of Activeworkbook.
    And that only if your code is on the workbook which has sheet users.
    Otherwise you may declare a variable like WB As Workbook and then set the WB and use it like WB.Sheets("Users")......

  6. #6
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Refer to a particular workbook

    ah that makes sense. Yes it would be "This" workbook that has the sheet with Users in it. Will be testing later today - thank you sktneer!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to refer to the workbook where the macro when another workbook is open during runtime?
    By jonelamora in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2012, 11:21 PM
  2. Refer to active workbook with VBA
    By Mayweed in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2011, 10:57 AM
  3. how to refer to new workbook
    By aretai in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-06-2010, 07:26 AM
  4. how to I refer to current workbook without using its name?
    By confused in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1