Call A Sub From An Xls File On The Network
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
ADVERTISEMENT
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
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
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
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
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
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
Jan 5, 2014
I am an extreme novice to using macros.
I used the 'record macro' feature to create the following: I created a rectangle box on sheet1 of NEW TEST.xlsm to link to the macros incidated below. Niether macro moves on after reaching the Workbooks.Open statement.
I am at a loss. Is there something in Excel that needs to be set? Is there something else that I need to do version of Excel is 14.0.6129.5000 (32 bit) MS Office Professional Plus 2010
What I am trying to accomplish is this: From 'NEW TEST.xlsm', open an excel file on the network, add a new sheet to this network file, calling it 'Pivot'. Then create a pivot table in this new sheet from the data in sheet data1 of this same network file. Then move control of macro / processing back to the NEW TEST file. Eventually I will do more, but for starters, I cannot even get the macro to add / name a new sheet in the network file.
[SIZE=3][COLOR=#000000][FONT=Arial]PS I have attached the NEW TEST.xlsm fikle as we as the data file. NOTE: the data file has been reduced to one day's worth of data, to to the upload file size.Also, my Excel Trustred locations has "Allow trusted locations on my network" checked, and the network location to the data file in trusted user locations.
VB:
Sub Open_Add_Sheet()
Dim filename, filelocation As String
Dim wb As Workbook
filename = "11 Nov, 2013 GDO AHS Agent Productivity Report.xls"
filelocation = "R:acharukAHS ReportsGDOGDO CSR ACTIVITY Report2013" & filename
Set wb = Workbooks.Open(filelocation) '
' Open_Add_Sheet Macro
[Code]....
View 9 Replies
View Related
Aug 21, 2013
getting the username of an unshared file on network. When I click on file I'll get this message "...already open by " its blank.
I have found 1 code but it is not working on office 2013. VBA function to get username of open file on network?
View 4 Replies
View Related
Oct 16, 2013
I am having trouble creating a hyperlink to a place in the current document for a file stored on a network drive. My workbook has a lot of sheets (50 or so), so I created a 'Navigation' sheet that contains hyperlinks to all the worksheets located therein, and pasted a link back to the Navigation sheet in cell A1 on every tab.
Setting up these Navigation sheets has never a problem until I started posting the file on a network drive. As soon as I did that, I noticed that the internal hyperlinks on all sheets in the workbook would stop working after I closed the file in Excel. Note that this network drive is not set as a drive letter on my computer, and is only accessible by entering the server ip address in Windows Explorer.
An example address the hyper is trying (and failing) to open is: "file:///\ IP ADDRESS sub-folders activefile.xlsm"
I've tried a few options, using the HYPERLINK formula, using the HYPERLINK feature in the Insert ribbon, etc, but all to no avail.
View 8 Replies
View Related
Dec 9, 2009
The script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.
It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.
I cant for the life of me work out a cause.
More baffling, the wording of the error says that the file 'R:DirCancellations.xls' could not be found.
At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?
Sub Search()
ActiveSheet.unprotect
ActiveWorkbook.unprotect
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim DirLoc As String
Dim PolNum As String
ActiveSheet.Range("C13:G25").ClearContents................
View 9 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
Jan 29, 2014
I am currently creating a simple macro to save out files to a specific folder on one of our network drives. Since we will be doing this often I would like to amend the date the the file name saved for sorting/organizational purposes.
Unfortunately I have been running into a few issues, this is what I have tried so far but keep getting a SaveAs error:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation TeamDC Tools" &
"Copy DC Conversion WB_2014 " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlNormal
I have also tried:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation Team
DC ToolsCopy DC Conversion WB.2014_" & _
Format(Now(), "YYYYMMDDhhmmss") & ".xlsx", FileFormat:=56
But still no avail. I believe it may have something to do with the file format but I am not sure. I am running Excel 2010.
View 1 Replies
View Related
Jun 19, 2014
I have an add-in called GeoDesiX. It is a geocoder/mapping tool that uses Google Maps API. It works fine, even on my machine, but I think it was built using 32-bit, while I have 64-bit Excel. When I open the add-in for the first time, an example workbook is below, it works great.
The problem is, that when I save any file with the map in it, the VBA no longer works, and an error pops up in the below code that says: "Object Reference not set to an instance of an object". This happens at the Err.Raise 65000, "GeodesiX", Result part. It looks like it just cannot find the DLL file, which is the "GeodesiX" in that error part. I was reading that it might be caused by switching between 64 bit and 32 bit, but could not figure out how to do it. I am guessing you need to put Private Function Declare PtrSafe, but I could not figure out syntax on how to get that to work. I understand that you will not be able to use these as it is an add-in, but maybe you just know what I need to do in order for the vba to call to the DLL file.
View 1 Replies
View Related
Apr 23, 2007
I found a function to set the password a an excel file:
=========================================
Function SetPwd(strNoPwdFile As String, _
strPwdFile As String, _
Optional strOpenPwd As String, _
Optional strModPwd As String) ' As Boolean
' This function requires the following arguments:
' strNoPwdFile - The path to a document without a password.
' strPwdFile - The path and name to save the password-
' protected document.
'
But the compiler said that it is syntax error.
How can i rectify the error?
View 9 Replies
View Related
Dec 9, 2011
I'm working on my first VBA project that requires 12 fields to be manually input of which one field is a file path. I need this path for multiple reasons, but the one reason I'm having a problem with is using the location of the path in conjunction with calling a bat file or executing a cmd prompt that is at that path.
Path Location, minus file name:
Code:
sheets("b. Fill Out Required Info").Select
Range("B18").Select
Actual Path found in B18:
C:UsersNickDesktopSubmission ToolTest Files
File attempting to execute:
CopyFileNames.bat
I can manually put all of this together and get what I want by using:
Code:
Shell "C:UsersNickDesktopSubmission ToolTest FilesCopyFileNames.bat", 1
The problem is, every time this will be used, a different path will be used, but the same batch file name will be used. So, rather than having to manually input the file path every time, is there a way to replace "C:UsersNickDesktopSubmission ToolTest Files" with the cell reference of the path listed above? I've tried different methods of this but I cannot seem to get it.
It would be even better if I could just use the cmd prompt found within the batch and run it directly from vba; the prompt I'm running is as follows:
Code:
cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt
How can I put that cmd into VBA? I tried different versions of this with no success:
Code:
Shell "cmd.exe /c cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt"
Notice that the path is the same as the path shown in B18 above. It would be great if that path would be dependent on the cell referenced above.
View 9 Replies
View Related
Apr 27, 2012
I would like to have something that will auto run the code in Module1 when the Excel File is opened, but I do not want to code tansfered into the file when it is saved in its location.
I don't mind if the code is tranfered to saved file if in fact I can make sure the code is not run when the new file is opened...
Everything is working as intended at the moment, but when openeing the saved file the entire macro tried to run again. This is what I do not want...
I currently have the following code in Module1.
Private Sub Workbook_Open()
'
' Format_Copy_&_Paste
'
Application.OnKey "^+r", "Warehouse_Cost_Summary"
'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DataSet"
[Code] ..........
View 4 Replies
View Related
Mar 3, 2007
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View 6 Replies
View Related
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 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
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
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
