Changing Network Printers
Apr 15, 2009I have a command button in a spreadsheet with the following code attached to it
View 4 Replies
ADVERTISEMENT
VBA List Of Available Printers
Oct 23, 2007Can VBA be used to get a list of available printers? I'm not talking about a browse window. I want to get the list of all printers attached to the PC and then use code to set the default printer.
View 9 Replies View RelatedNetwork Print Is Required
Feb 23, 2014I 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] .....
Working With PC Network Names?
Jun 3, 2014Is it possible to retrieve the network name of the local workstation and if so how?
View 2 Replies View RelatedSaving To A Network Drive
Jan 6, 2009We 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.
VBA - Opening A Workbook On A Network
Sep 29, 2009As 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".
How To Get To A Computer On Our Network Using Code
Jan 29, 2007I 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
How Can I Identify A User Over A Network
Apr 28, 2008I 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?
Call A Sub From An Xls File On The Network
Oct 1, 2008I'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
Active Network Printer
Oct 30, 2008Is 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.
Use Network Id To Unprotect Sheet
Jun 1, 2006We 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.
Template Read Only On Network
Jan 3, 2007I 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.................
Macro Does Not Run On Different Computer In Same Network
Jan 20, 2007I 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
Is File Open On Network
Jun 28, 2007when 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.
Install Network Printer Via VBA
Jul 26, 2007I 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.
Is File Open Over Network
Sep 6, 2007I 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 RelatedCheck If Connected To Network
Jan 11, 2008I 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............................
Sharing Excel File Over Network
Oct 22, 2003Sharing 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.
Document Saved On Network Share?
Feb 27, 2013My 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.
Date Format - Network Days
Mar 9, 2013I 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.
Call A Subroutine From An Xls File On The Network
Sep 30, 2008I'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 RelatedSaving To A Network Drive Macro...
Jan 26, 2010I currently have the following ...
View 8 Replies View RelatedWorkday And Network Day Formula Challenge
Feb 11, 2007I 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.
VBA Coping File Onto Network Diretory
Aug 10, 2007I’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
Macro To Move Files Across A Network
Jan 12, 2008I 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.
Deleted Both Companies Cant Have Same Network IP Addresses
Apr 9, 2009I 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:..................
How To Open Xls File In A Network Drive
Dec 1, 2009If 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
Open Network Path On A Button
May 30, 2006I have created a button on an excel form in the 2003 version and i want to create a macro that opens up a network path in windows explorer such as 'open W:
ew folder
ew share'
Track Changes Disabled On Network Workbook
Jul 31, 2006when i put a file across a network, it should be shared among all the users, but it appears as read only file and the option of track changes in the tools function is disabled. However, when i open an excel file in a stand alone pc, and use the track changes option, i get the file as (shared) as the header. what is the significane or meaning of this word "shared" in a stand alone pc?
View 2 Replies View RelatedOpen Network Location Macro
Aug 10, 2007In my macro I need to open to paths. the 1st path I I have working. Here is the
Dim OpenA As Workbook, OpenB As Workbook
Dim TheFile As String
Dim TheFile2 As String
ChDrive "H:"
ChDir "H:Treasury Project"
TheFile = Application. GetOpenFilename("Excel Files (*.xls), *.xls", , "Select the file and choose open.")
If TheFile = "False" Then
Exit Sub
End If ...............
The "????" are where I don't know what to do. I used a test path to make sure all code works and it does, but the real path is a network location. When I go to it manually with excel this is where it shows me to have gone: My Net Work Places, Entire Network, Microsoft Windows Network, Company2, datawhse, root, LAW81, Lawson, Print, Name. If I open a file and do =cell("filename") it gives me \datawhse
ootLAW81LAWSONprintNameanrvwfins111thSSFRPMOEDTL.csv. Is it possible to get excel to open me up to ...\datawhse ootLAW81LAWSONprintName.