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

[Code]....

View 2 Replies


ADVERTISEMENT

Move / Rename File On Mapped Drive?

Jul 25, 2014

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:

[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?

View 2 Replies View Related

Shared File Prints Differently For Different Users - Autofit Wrapped Cells

Apr 16, 2014

Here's the details:

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...

View 1 Replies View Related

Worksheet Change Macro Runs Differently

Dec 3, 2008

When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?

Original
Sub sessions()
Dim a As Integer
Dim x As Long
x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row

Sheets(1).Unprotect Password:="password"

For a = 2 To x

If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = True
ElseIf Cells(a, "m") Cells(a, "d") Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = False

End If

Next a

View 9 Replies View Related

VB Code To Change DIR Drive

Oct 24, 2007

I have part of the code below and need to modify it so that others who only have C: instead of D: to use it

Filename = "D:TempBudgetMonthly.xls"

FileExists = (Dir(Filename) "")
If FileExists = False Then
msg = "The data file Monthly.xls does not exist !! Load the required file and run the report again !"

If MsgBox(msg, vbExclamation + vbOKCancel) = vbOK Then
End If
Exit Sub
End If

I need code to replace all D: to C: for the entire PROJECT not MODULE.

View 9 Replies View Related

Change Unc Links To Drive Letter Mapping

Jan 31, 2008

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

View 2 Replies View Related

Margins Change For Some Users

Mar 26, 2014

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.

View 6 Replies View Related

Prevent Users From Edit/change Formats Of Unlocked Cells

Sep 30, 2007

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'

View 9 Replies View Related

Mapped Driver Macro Option

Feb 3, 2009

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)

View 10 Replies View Related

Loop Through Mapped Network Folder

May 27, 2008

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

View 2 Replies View Related

Create List Of Drives (local And Network Mapped) On Computer

Nov 5, 2012

how to create a list of the drives on my computer using VBA. Maybe I missed something.

View 2 Replies View Related

Variable In Filepath Name

Jun 20, 2013

I have defined part of a file path as a string variable so that I don't have to type in it everytime I need it. I then try and use this variable to open a workbook, but I get an error message saying:

Expected: list separator or )

I use the code:

Workbooks.Open(filepath1&"filename.xls")

where filepath1 is the string variable

Can't work out why it doesn't work...

View 3 Replies View Related

Allow Users To Print Hidden Sheet But Not Change Print Settings

Jul 2, 2009

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..............................

View 5 Replies View Related

Browse For Filepath From Userform

Aug 7, 2013

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.

Is this possible and if so how?

View 1 Replies View Related

Update Filepath In A Cell With VBA

Mar 21, 2014

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

View 11 Replies View Related

Extracting Filepath From A String

Mar 18, 2008

In A1 I have C:SpreadsheetfolderSubfolderTestSpreadsheetA.xls as a string.

In A2, using VBA I want to extract the string C:SpreadsheetfolderSubfolderTest i.e. take A1 value and exclude the SpreadsheetA.xls.

View 9 Replies View Related

Filepath String Is Legitimate?

Jul 11, 2008

i have a userform where a user is able to specify a filepath via textbox. I would like to find a way of determining whether or not the filepath which is given is a legitimate path...

how to determine through vba if a filepath string is legitimate?

View 9 Replies View Related

Obtain/Return FilePath/Name In VBA

Sep 4, 2006

Does anyone know the code for obtaining the filepath of the current workbook? Tried a search and can't find anything on this.

View 9 Replies View Related

Export To HTML Filename And Filepath

Jul 24, 2009

I need a macro to export a range of cells to .html. The Save as Web Page works fine for me except a few thing that I need to automate. The macro I use now looks like this (recorded):

View 3 Replies View Related

Find And Replace Part Of Filepath

Apr 20, 2012

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.

View 3 Replies View Related

VBA Use Filepath In A Range To Put Into SaveAs Statement

Nov 24, 2012

I am trying to use cell C6 in my control sheet

Which is this

C:UsersNeilWin7DBTradingETFfiles

And put as a string into SaveAs

wbNew.SaveAs Filename:=Worksheets("Control Sheet").Range("C6") & TickerIndex & "ProSharesNAVRatio" & ".txt", FileFormat:=xlTextWindows

View 3 Replies View Related

Using Shape To Call Macro (Filepath Error)

Apr 9, 2014

I am changing command buttons over to shapes for aesthetic purposes. I went to assign a macro to call useform4 using a rectangle with the following code:

[Code] .....

It throws me some kind of file path error which makes no sense. Do I have to dim the rectangle2 for active sheet or something?

View 11 Replies View Related

Display Workbook Name Not Found In Array Set Without Filepath

Jun 6, 2014

display workbook not found in Array set without Filepath, As for workbooks found we can use Dir function, but it is not work for workbook not found. The Dir() will return Blank.

e.g. in my filepath only has Book1.xlsx.

[Code] ....

MsgBox CountFound = 1
MsgBox CountNotFound = 3
MsgBox FileFound = Book1.xlsx
MsgBox FileNotFound = Blank

View 4 Replies View Related

Linking Cells Globally To Allow Users Ability To Change Cells On Separate Sheet / Cells?

Feb 18, 2014

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?

View 1 Replies View Related

Making Filepath Hyperlink In Email Generated With Code

Mar 14, 2014

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.

[Code] ....

View 10 Replies View Related

Save File / Workbook As Cell Reference Filepath & Name

Feb 8, 2008

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

View 2 Replies View Related

Calculate Differently Based On Value

Nov 26, 2008

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.

View 3 Replies View Related

Copy Cells Into Workbooks Names Mapped From Copy Cell

Aug 20, 2008

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.

View 5 Replies View Related

If Negative Number Display It Differently

Jun 8, 2009

I currently have the following in a cell:
=BDH(AHD!A3,"px_last","-5cm","-5cm")

This currently displays as -32 pulling this number out of bloomberg.

I now want to write a formulae in this cell that will display this as 32c if it is negative... and 32b if it is positive.

So if the number pulled out is negative I want it to have a c after it (and please have it delete the minus sign) and if positive, then have a b after it.

View 9 Replies View Related

Weighted Averages Across 2 Differently Sorted Ranges

Aug 16, 2014

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).

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved