How Can I Identify A User Over A Network
Apr 28, 2008
I have written a function (with the help of the good folk on this wonderful message board) that is able to check whether a given spreadsheet is currently being used by someone at my work. There are potentially multiple users for the workbooks that my system needs to access. FYI, I work in a bank so we have a pretty good network.
The code for my function, as well as the TestOpen(-) function that it uses, is below.
Ideally, if a workbook my system needs is currently being used on someone else's computer (Case=1 in Select statement, then Err0) I would like to be able to have a userID returned to me so that I know who I need to call to shut the model.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function Open_Selected_Drone__EXEC(fs, fileLocation, fileToOpen, testOnly)
Application.ScreenUpdating = False
myName = ActiveWorkbook.Name
Application.StatusBar = "Checking availability of " & fileToOpen
iOpen = TestOpen(fileLocation & "" & fileToOpen)
Select Case iOpen
Case 0: ' the file has been found and is not in use
rv = "not in use"
If Not testOnly Then Workbooks.Open Filename:=fileLocation & fileToOpen, WriteResPassword:="j"
The old version of this message board used to have an "insert code" button. I cant see it anymore. Am I simply meant to paste code into this message box now?
View 9 Replies
ADVERTISEMENT
Jul 28, 2012
I want a simple VBA code that when run will check if a particular spreadsheet that exists on a Network Drive is already open or not by another user. Imagine there are 3 users A, B & C
The network path for example is : S:Department1TeamAConsolidated Report"
The spreadheet name is : Consolidated Database.xls
If the spreadheet that is on S:Department1TeamAConsolidated ReportConsolidated Database.xls is already open by User A then User B should get a message that "File is already open"
I have the below code which I got from a website which I kind of tweaked but it doesnt work.
Dim WbookCheck As Workbook
On Error Resume Next
Set WbookCheck = Workbooks("Consolidated Database.xls ")[code]......
View 5 Replies
View Related
Apr 24, 2009
I am having an issue with Excel creating a duplicate file when logged in as a user with restricted permissions on our network. If I modify a file while logged in as another user, excel will save the file but will also create a duplicate file with an eight character random name and no file extension. For example if user "Dave" opens a file called test.xls and saves it, in that same folder on the server a file named 296E9E20 will be created. Users are using Windows XP SP3 boxes and the server is running Win 2003. I am not sure why this only happens with Excel.
View 3 Replies
View Related
Dec 20, 2006
VBA code Identify which user has a particular file open. This file is on our shared drive and recently we've had people open the file and leave their desk. They haven't input an Excel username so frequently we cannot tell who has the file open to ask them to close it.
I'm trying to write a macro that will tell me if the file is open and if it's open, who has it open (using their computer login name).
View 9 Replies
View Related
Jun 14, 2007
I am trying to identify the current user, similar to Environ("Username") in VBA
I have a vague recollection of a formula from an old version of Excel (4.0??) that I've read about that might acomplish the task.
I seem to recall reading about a function, I think it was Get
that is not supported, and not that well documented.
Does anyone out there know what I'm talking about.
Otherwise does anyone know of a native worksheet function that would return the current username?
View 9 Replies
View Related
Feb 23, 2014
I recorded a print macro which prints to my default printer, others will be using this spreadsheet so I really need it to print to the same network printer. The printer address and name is: ironyan-fileBI44
How do I incorporate this into my code?
[Code] .....
View 7 Replies
View Related
Jun 3, 2014
Is it possible to retrieve the network name of the local workstation and if so how?
View 2 Replies
View Related
Jan 6, 2009
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.
Now the problem I have is that we all have the network drive mapped to a different letter.
Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
View 10 Replies
View Related
Apr 15, 2009
I have a command button in a spreadsheet with the following code attached to it
View 4 Replies
View Related
Sep 29, 2009
As above i have a macro which opens a workbook which is on a network drive. This works fine for my multiple users...however i have discovered that if they do not have the drived mapped with the same drive letter at the start...it will throw up an error advsing that the file does not exist. Is there any way to by pass this.
Ex - "TestFile.xls"
File is stored in "E:NetworkTeam1Testfile.xls"
However, some users have access to this file and it is mapped as E:, but some users have been mapped as F: The advisors who have been mapped as F: cannot access the file. Is the VBA/Macro smart enough to bypass the drive letter and just search the rest of the past...NetworkTeam1Testfile.xls".
View 2 Replies
View Related
Jan 29, 2007
I have code below that works well most of the time. One of the cases below will not work. It insists on requiring a name and password.
(note: it is the only Windows 2000 operating systems)
I want to enter this in my case line to automatically add the username and password.
Let's say the username is: "dave" and the password is: "happy"
How can I add this to this part of the code?
Case "DJ"
strfilename = "\DAVEJONESDavesProposals" & strfilename
Sub Save_and_SaveSalesman()
Dim strPath As String, strPath2 As String, CurrPath As String
Dim WB1 As Workbook
Dim WB2 As Workbook
View 9 Replies
View Related
Oct 1, 2008
I'm trying to call a sub in an exce. file that resides on our network drive so that multiple users can access the programs that I develop and I can get VBA to open the file but I can't get it to open the sub titled "DCU". I keep getting the following error.
Cannot run the macro DCU. The macro may not be available in this workbook or all macros may be disabled.
Here is my code that errors.
Option Explicit
Sub DESCRIPTION_CLEANER_UPPER()
Application.Visible = True
Workbooks.Open Filename:= _
"\Cansvp01grp_01fCommonCommon-PartsPrcngMacrosMacros.xls"
Application.Run "DCU"
End Sub
View 9 Replies
View Related
Oct 30, 2008
Is there a way to test for an active default network printer?
In my module the code sets the paper size and a few other printer settings, if my default network printer is down or offline the code fails.
If I could test for active default printer I could remove this error.
View 9 Replies
View Related
Jun 1, 2006
We have created a spreadsheet that needs to be protected as it is viewed by several users. We would like, if possible, to update this daily with as little human intervention as possible.
Can we use VBE and the on worksheet open event to unlock the password protection, update the sheet and then lock it again? I know that you can read who opens the sheet using code similar to the following but don't know whether the password protection can be "unlocked" utilising this method.
View 6 Replies
View Related
Jan 3, 2007
I have 2 workbooks, one containing one worksheet that acts as a Template a second that has some code attached to a button that uses the Template to create new worksheets based on the Template. The problem I have is that when I run the code over a network I always get a read only worksheet created. I need it to be readwrite. I have used sharing on the Folder where the 2 workbooks are located so it accessable over a network. There is no problem when I run the code on a standalone machine.
Private Sub CommandButton1_Click()
Dim tClient
Dim tAddress
Dim tPhone
Dim tItem
Dim cOriginalLoan
Dim cAgreedInst
tClient = Client
tAddress = Address
tPhone = Phone
tItem = Itm
cOriginalLoan = loan
cAgreedInst = Inst
Sheets("swbrd").Select
PathName = Range("A196").Value 'Here is where the full path to the Template is found.................
View 3 Replies
View Related
Jan 20, 2007
I wrote a macro that runs perfect when started from my pc, but if started from a different ps which is logged into the same network it doesn't run, also the macro is stored on the server. I have a code in the private section of the workbook:
Private Sub Workbook_Open()
Call FINAL
End Sub
then the macro 'FINAL' is stored in the 'Modules' section and starts with:
Sub FINAL()
'this part opens the Edit/Links dialog from which the user has to choose the relevant model
Dim Which_financial_model_contains_the_data As String
ActiveWorkbook.ChangeLink Name:= _
"T:DEALSSSalsa (Project)Financial modelsProject Salsa Model - BASED ON SPONSOR CASE - b - 20061214.xls" _
, NewName:= _
"Which_financial_model_contains_the_data" _
, Type:=xlExcelLinks
View 6 Replies
View Related
Jun 28, 2007
when I open a file, called "A", I want it to automatically open another file, which is shared on the network, called "B". I've tried using the "IsFileOpen" routine and a few other sets of code that I've found on the internet but none of them solve my problem.
If "B" isn't open on any computer and I open "A", there is no problem, and if "B" is already open on my computer when I open "A" again, there is no problem, but if "B" isn't open on my computer and is open on somebody else computer, "B" won't open on mine when I open "A", the code assumes that because "B" is open on someone elses computer, "B" is already open on mine.
View 9 Replies
View Related
Jul 26, 2007
I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed. The logic, not in true VBA syntax, is as follows;
' Check Printer
If <This Printer> Is installed Then
Printout using <This Printer>
Else
Install <This Printer>
Printout using <This Printer>
I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.
View 7 Replies
View Related
Sep 6, 2007
I want to be able to check when workbook opens if a particular Excel file is open or not by having a true or false in a cell. This works fine if I have it open on same computer, but if I log on to another computer over a network and run same workbook it won't pick up if file open or not. I have tried various ways from this forum for file paths over network including eg. C:file or '\path on networkfile or N:pathlocfile etc. Nothing apears to work. If I check to see if file open from a different computer than the one the file is open on I receive standard message file already in use etc.
View 2 Replies
View Related
Jan 11, 2008
I have created an application for users that requires the user be connected to the network. I wanted to do a check to see if they are connected, and if the user is not connected they would get a message box telling them that they need to be connected to the network, then the workbook would close. Below is the code I have, but when a user is not connected they do not get my messagebox, instead they get the excel error message "excel run-time error '52': bad file name or number", and the debugger line that gets highlighted is in the function DirExists that checks the length of the directory of the file path: Len(Dir(strpath))
Option Explicit
Function DirExists(strpath As String) As Boolean
If Len(Dir(strpath)) = 0 Then 'this is where the debugger highlights
DirExists = False
Else
DirExists = True
End If
End Function
Private Sub Workbook_Open()
Dim strpath As String
Dim strfile, strfile2 As String
strpath = "\n530fs1PCLFileSharespcl_repositPricing_ToolsPAT"
If Not DirExists(strpath) Then............................
View 2 Replies
View Related
Oct 22, 2003
Sharing excel file over network for use by multiple persons. File has suddenly stopped opening giving message "File cannot be found" etc.... I back the file up daily to my hard drive so didn't lose much.
This is the second time this has happened. File size is only 300Kb.
Excel seems to be creating temp files to the same folder, then gave a message saying changes could not be saved to the excel file.
Ran the corruption program from this site, it could not open the file.
View 2 Replies
View Related
Feb 27, 2013
My coworkers and I use a couple of Excel documents to store statistics. The document is on a network share. We all have Office 2010, my coworkers are on Windows XP, I am on Windows 7.
If I open the document and make changes, then save it to the network location I can open the document later and see the changes but my coworkers do not see any changes I have made. Also If my coworkers make changes to the document the 3 of them see the changes but I do not.
If I go straight to the network share the timestamp on the document only changes for changes I made.For example if I go to servernamefoldernamestats.xls and add data to the file. Then save and close it. I can reopen the file and my changes are there.
Tomorrow Coworker 2 opens the same file from the same location but it has data from 2 days ago, with a timestamp of 2 days ago and my data is missing. They then reimput the data I entered, and add todays data. Then they save it and close.I open the file it has my data from yesterday, but not the new data entered by coworker 2.
View 3 Replies
View Related
Mar 9, 2013
I am trying to display the number of network days ( Mon - Fri) from start to finish in a colour format
I have got so far with a spredsheet which is shown in table 1.
On table 2, I have shown what I would like to happen but I am baffled by what formula / conditional formatting is required.
View 5 Replies
View Related
Sep 30, 2008
I'm trying to put some visual basic out on the network drive at work so I created a book and called it Macro.xls. I then saved the code within that book and saved it out on my network. I'm now trying to run that code by calling the sub and don't know how to do that.
View 7 Replies
View Related
Jan 26, 2010
I currently have the following ...
View 8 Replies
View Related
Feb 11, 2007
I am trying to create a sheet for a project that will identify dates a project will conclude omitting weekends and holidays. I keep getting an error with the formula I have.
In my spreadsheet:
A1 is the start date
B1 is the number of days after the start date.
The formula I am using:
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{""4/6/2007","5/28/2007", "7/4/2007","9/3/2007","11/22/2007","12/25/2007","1/1/2008","1/21/2008","3/21/2008","5/26/2008", "7/4/2008","9/1/2008","11/27/2008","12/25/2008"}"}+0)
The error I get is with the parens (A1,A1+B1). - at least the parens are highlighted in purple.
View 9 Replies
View Related
Aug 10, 2007
I’ve tried the code below to copy a file onto an intranet directory, when I manually do its fine but with the code below I get the error message ‘Path Not Found’, I’ve copied the file path from the actual directory which is on ‘ My Network Place’,
Sub Output ()
Dim FS As Object
Dim F As Object
'Saves Copy of PPJ log In directoy
Set FS = CreateObject("Scripting.filesystemobject")
Set F = FS.getfile("S:Output FilesOutput.xls")
F.Copy [url]
End Sub
View 9 Replies
View Related
Jan 12, 2008
I would like to include code to move or copy various csv files from one computer across a network to other computers.
To date the best I have come up with is a method of running a batch file that executes the action.
Sub moveTKRS()
Call Shell("C:MoveTKRS.bat", vbNormalFocus)
End Sub
I would rather a more direct code that does not require a batch file.
View 9 Replies
View Related
Apr 9, 2009
I have a spreadsheet with two worksheets, sheet 1 and sheet 2
Sheet one has the company 1 and company 2 with various networks address and that need to be deleted based on overlaps.
I am trying to mark on sheet 2 or even better sheet 3 on what can be deleted since both companies cant have same network IP addresses.
I have started it, but its to manual and will take some time to complete.
On Sheet 2 I have IP addresses that coincide with networks that overlap
So Sheet 2 needs to reference Sheet 1 based on what can be marked as deleted if they overlap
Sheet 1 contents;
******** ******************** ************************************************************************>Microsoft Excel - QIP_Subnet_Overlap.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1CompanySubnetMaskKeep/Delete/ResizeNotes 2Comp110.0.5.0255.255.255.0Delete 3Comp210.0.5.0255.255.255.0Keep 4Comp110.0.6.0255.255.255.0Delete 5Comp210.0.6.0255.255.255.0Keep 6Comp110.0.15.0255.255.255.0Delete 7Comp210.0.15.0255.255.255.0Keep Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Sheet 2 Contents:..................
View 9 Replies
View Related
Dec 1, 2009
If you don't know what the drive letter will be, because on other people's computer it could be different than what you have.
I only have the folder address
\ROGERSDAILYREPORTSXLSDM
View 9 Replies
View Related
