How To Change Filepath Or Dir When Users Have Drive Mapped Differently
Oct 23, 2013
I have written code that allows the user to open a file. To make things easier I changed the file path to point directly to the folder where all the needed files will be placed. The problem is everyone that will be using this work book map their drives differently. One user may have it as H, and the other as J. Also users can have the same drive mapped but at two different starting points. Ex: H:DataReportsxxxyyy or H:Reportsxxxxyyy this causes the code to crash. Is there a way do default the path so it will not matter how the drives are mapped?
Here is what I am using so far:
Dim filt As Variant ' flit to be used in the getopenfile. variant is need for its multiple
Dim FilterIndex As Integer
Dim Title As String
I am trying to move and rename an excel template file using a macro. My code works fine when it is moving the file locally on my C: drive. However, when I try to do it on a mapped network drive I get a Path/File access error. Here is the relevant code:
I tried it both ways that are commented out - both give me the error. I have permissions to read/write in all relevant folders. What am I missing here?
Same file shared by many users in the office. File is maybe 2200 rows long and 20 columns wide. Last column is a text field where users can explain variances. Cells in the last column are wrapped and the rows auto-fit on the height. Zoom is set at 100%.
One person opens the file and it looks fine on the screen. Print preview also looks fine. File prints out perfectly.
Another user opens the file and it looks fine on the screen. BUT, print preview shows that the bottom line or lines of text is cut off. File prints exactly like the preview showed with clipped text.
Both users can point the file to the same printer, but that doesn't seem to change anything. Still prints with text clipped for one person and perfectly for another...
I have a user whose links in his spreadsheets are in UNC format. He changes them to drive letter mappings, but when he opens the workbook again, the UNC format returns. how to change the links so that they remain as F: etc.. rather than \servernamesharename The user has MS Excel 2000 and Windows Xp Professional
We use a spreadsheet for developing loans. I can open and print all of them just fine and so can almost all of the other employees. We have two people, though, that have the margins change when they open them. I can open it and have it be just fine, close it, they open the exact same file and see changed margins.
How to change that. I don't want them to have to do it on each spreadsheet. I am looking for a complete, one-time-only fix.
I have a spreadsheet that is very large and formated a certain way. I want to make sure that a user cant put the cursor in the lower left corner and copy cells by dragging, copy & paste, or do anything other than input data and be able to highleght text and reinput data in case they mistyped or misspelled the data.
ex: user clicks on cell E33 and types in '1234568' and then move to next cell. The user can click back on E33 and can correct by retyping. That is all the user is allowed. The cells in question are also in ranges if this helps. EX: 'E33:AJ67'
I finally completed a macro that would loop through selected workbooks in a folder and pull results to be shown in a new workbook.
This works fine by using a worksheet where the files are listed and in a1 of the worksheet, the path to the folder.
We have one workstation on the LAN with restricted access to the server, and therefore has mapped drives. Therefore the path to the folder containing the workbooks is different.
How could I instruct the macro when invoked - so in the case of that workstation - if the macro cannot find the files at the path stated in a1 of the workbook, it is to try a different location (The folder containing the workbooks is actually Drive:T)
I have read your loop instructions under FAQ entitled; Loop through a folder of workbooks. It works very well on a local drive but I need to have it loop through a folder on a network drive. the drive is mapped as h:\computer name2008shareddocs
I am having trouble disabling the "Preview" button when calling Application.Dialogs(xldialogprint) in XL2003. I have an xlSheetVeryHidden worksheet containing a form which is made visible programatically when the user wishes to print a copy (code below). I want the user to be able to have access to the functionality of the xlDialogPrint dialog (i.e. select desired print destination, number of copies, pages per sheet etc.) but I also want to prevent them modifying the page setup of the protected worksheet by initiating a preview from the print dialog.
Private Sub btnPrintReturnForm_Click()
wbkRUSC.Unprotect sysPass 'unprotect workbook to enable changes to sheet visibility ' (sysPass is project constant string containing password)
shtStaffForm.Visible = xlSheetVisible 'make staff form visible
shtCalc.Visible = xlSheetVeryHidden 'hide main calculator in case user gets smart and hits..............................
Upon clicking a button in a userform, I would like a MsgBox to appear, allowing the user to browse their file directory.
Upon selecting a folder in the file directory, a new excel workbook is created with the filepath chosen and named based on a previous MsgBox (which ive already accomplished), and the filepath is recorded in a cell on Thisworkbook.
I have a 4 cells which contain the followng information, To: Email address, Subject: Text, Body: Text, File Path: File path (as Hyperlink).
I need the file path cell to update automatically when the file is saved somewhere new. This filepath is then used in an email that is generated with code so that the person opening the email can click on the link and open the file.
What I cannot seem to do is to get the file path to be something that updates automatically (which is functionality that I must have)but that also remains as a hyperlink for when it goes in the email (I have an additional problem at the moment which is that the hyperlink does not seem to work in the email - it keeps saying file cannot be found. I don't know if this is something to do with our systems and the way I am referring to the file?!?)
The cell with the file path currently loks like this:-
Cell AX Cell AY File path file:///E:PART_TIME_LEAVEPART_TIME_PS_LEAVE_RECORD_EMAIL_VERSION_STATUTORY.xlsm
I'm trying to 'find and replace' part of a filepath which is buried in hundreds of formula, but when I hit 'Replace All' a file navigation window appears. Hitting cancel simply bring up another window, and again and again, each time a replace is executed.
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I have the following code to do something similar to above but I need to add the filepath as a hyperlink to the email along with the body text. I have 4 cells in the Excel workbook that contain 1) Who to sen to:, 2)The Subject:, 3) A sentence for the Body of the email and 4) The filepath of the workbook as a hyperlink.
I want to add both the Body text and the hyperlink to the email so that the person opening the email can click on the link and open the file.
I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.
Option Explicit Sub SvMe() 'Saves filename as value of A1 plus the current date Dim newFile As String, fName As String ' Don't use "/" in date, invalid syntax fName = Range("A1").Value 'Change the date format to whatever you'd like, but make sure it's in quotes newFile = fName & " " & Format$(Date, "mm-dd-yyyy") ' Change directory to suit your PC, including USER NAME ChDir _ "C:Documents and Settings USER NAME Desktop" ActiveWorkbook. SaveAs Filename:=newFile End Sub
I'm selling a product to clients. The first one is $20, and every subsequent one is $10. If they don't order any, I need the cell to read $0. I've been trying to get the IF function to work, and can't quite make it work correctly.
1. I have a list of data (Collated Data) 2. For every row in collated data I want to export the cells into a corresponding cell in my predetermined workbook (TAF Form), i.e, Cell C1 on Collated data goes into Cell D3 on TAF Form, cell D1 to cell I3 etc etc 3. Once all cells in one row have been copied into the TAF Form I want that TAF Form to save as "TAF Form & Employee Name" (which would come from cell D3). 4. I then want "TAF Form & Employee Name" to close. 5. I then want the Macro to do the same thing for Row 2, copy the cells, save the form, close the form 6. I want to do this for every row that I have (which varies).
Is this possible? If you have any more questions in terms of what I need, don't hesitate to ask.
I am looking for a way to get a weighted average to work without the need of helper columns, when my weights are stored in one range, and the actual values are stored in another range, but might not be sorted in the same fashion.
For example; range 1 might be:
Weight Homework 120% Pre-Exam 30% Exam 50%
But the range for student's scores might be in a slightly different order:
Scores Exam 90 Pre-Exam 72 Homework 1 81
(Note that this is a greatly simplified example and that just resorting would be impractical).