Place Username In Directory Path

Mar 9, 2007

I've been trying to get the network username and found the solution on a previous thread. I have got to the point where I have copied the network username to my workbook. I now want to save this workbook into My Documents using that value for the username. So, so far i have:

'Get username from network

Dim User As String

CUser = Environ("username")
Cells(5000, 9) = Environ("username")
user = Application.ActiveCell

' Protect workbook and save in My Documents

ActiveSheet.Protect

ChDir "C:Documents and SettingsXXXXXXXMy Documents"

ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsXXXXXXMy DocumentsSalary Review Data 2007 - " & country & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close

The user who saves this workbook, will be doing so on their own PC's so i need the file to save to their 'My Documents' folder. So i need to replace the red X's with the user's network username (which I have extracted and dim'd as User. How do i write this into the path of the directory?

View 2 Replies


ADVERTISEMENT

Get Username & Use To Change Directory

Oct 2, 2007

I have an Excel macro that pulls an external .txt file from a particular directory on a users computer. The directory is different for each user because of the way the computers are configured, see example below.

Bob's computer:
c:obwork

Jill's computer:
c:jillwork

There is a system variable called %username% on each computer that can be used for navigation in an OS enviroment. So if you were at the command prompt on user's computer and typed cd\%username%work you would be put in the desired directory. Is there a way to leverage this system level variable, %username%, in an Excel macro?

View 2 Replies View Related

Save To Directory Based On Username?

May 20, 2013

I have a button that saves my excel workbook to a network drive, but what I'd like to do is have it save the file to a directory named after the user, instead of all the users in the department saving to one folder. For instance, R:customerserviceweight reconstructionusername, where username a directory named after the user.

Code:
Private Sub Save_Click()Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
FileName1 = Range("A2")
FileName2 = Range("A3")
ActiveWorkbook.SaveAs Filename:="R:CustomerServiceWeight Reconstruction" & FileName1 & " - " & FileName2 & " - " & Format(Date, "yyyymmdd") & ".xlsm", FileFormat:=52
End Sub

View 1 Replies View Related

Reference Cell's Value In Directory Path Name?

Apr 23, 2013

I have a worksheet (sheet1 in book1.xlsx) that references another worksheet (sheet2 in book2.xlsx) in a different file. The latter file, however, is stored in a directory that changes. Call it MYPATH for the time being. I'm trying to use a formula (not macros, which aren't allowed in this office) to reference the directory path using the dynamic name.

For example, in the first worksheet, cell A15 contains the word MYPATH. I'd like cell A1 to have a formula like this: ='C:&$A$15&subdir[book2.xlsx]sheet2'!B50

so that if I change the value of cell A15 in book.xlsx to ANEWPATH, the path reference will switch to the book2.xlsx in ANEWPATH, not MYPATH.

View 3 Replies View Related

Dialog Box For Selecting Directory/path

Jul 27, 2007

I've seen many posts advocating the use of BrowseFolder.zip located at
[url]

And some others saying to use the FileDialog object (if you use Excel 2002, or older(?))

I have to admit both are above my prior experience level, but I think I can do it if I understand what their function, requirements, and output are.

All I need is a dialog box that preferably lets me select the default directory, and then allows the user to select the target directory.

View 9 Replies View Related

Create Directory Path From Cells

Jun 10, 2008

This should be so easy, but I know I am missing something. I know that you can only create one directory on one statement line at a time with MkDir. in the "var_dir_path" resides a part number that changes dynamically.

Private Sub CreateFolderFromCellPath2_Click()...

View 6 Replies View Related

Conditional Directory Path From Cells

Jun 12, 2008

Customer directory inside the "masterfile drive". Part number subdirectory of the Customer Directory. 5 subdirectorys of the Part Number Directory. 2 subsubdirectories of one of the 5 subdrectories. That is reference only.

Private Sub CreateFolderFromCellPath8_Click()
Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String, s7 As String, s8 As String, s9 As String, s10 As String
var_path_masterfile_cust_name = Worksheets("RFQ").Range("AF48").Value
var_path_part_number = Worksheets("RFQ").Range("AF49").Value
var_path_Correspondence = Worksheets("RFQ").Range("AF50").Value
var_path_Customer_Service = Worksheets("RFQ").Range("AF51").Value
var_path_Engineering = Worksheets("RFQ").Range("AF52").Value
var_path_Purchasing = Worksheets("RFQ").Range("AF53").Value
var_path_Quality_Assurance = Worksheets("RFQ").Range("AF54").Value
var_path_Sales = Worksheets("RFQ").Range("AF55").Value....................

The variables are grabbing the path directly out of cells in Excel that are concantenated strings. This works great if the customer folder (s1) is not created. I would have thought that the way that I had the If/Then setup, that it would run smoothly, but if the customer folder is already created, the Debug highlights the very first MkDir s1

View 4 Replies View Related

Extract Data From Files Within The Same Directory Or Below And Place In New Workbook

May 3, 2006

Firstly, I have attached a file called test2 which has two sheets within it, one called list and one called 12345. This is for demonstration purposes only as in real life these are both separate files.

What I am trying to do is create a history in the file called list that extracts certain data from the file called 12345 and places it in a more user friendly format. No problems with the formulas etc, just a case of a few ifs and buts.

My real problem lies in the fact that file 12345 is only one of hundreds of files and in order to create the history, I have to repeat the process on all the other files, so that the list grows as I copy the data.
Again for demo purposes, the file "list" sits in the C root ie C:list and the job files sit in C:files*.*.

Is there a way to automate the process so that formulas or VBA residing in the file "list" searches through the C:files*.* and populates the list as it runs through them.

View 9 Replies View Related

Load Dialog Box In Last Directory Path Opened?

Jul 11, 2014

I have a file which is opened every so often. This file when opened it presents a userform in which you browse two files which then get loaded into the document and some charts/pivots are updated with the information from the loaded files.

When you click on the browse button it opens the browser dialog box in the libraries path. I am wondering if it's possible to have the dialog box open in the last directory path used.

For example:

A week ago I opened the file from the path C:UsersIntiDocumentsProjectsTea Project

What I would like to happen is that when I open the file today and I click the "Browse" button (which opens the dialog box to find files in your computer) for the dialog box to open automatically in the path C:UsersIntiDocumentsProjectsTea Project

Then if tomorrow the file is opened from the path C:UsersIntiPicturesProjectsTea2Project

Then a week from now when you browse for the file the dialog box opens automatically in the C:UsersIntiPicturesProjectsTea2Project path

This is what I have right now and it always opens the dialog box on C:UsersIntiDocuments

[Code] .....

View 1 Replies View Related

Using Excel Cell As Pointer In Directory Path?

Oct 2, 2011

I have a few files all with the same name for a few different members of staff, so I save them in different paths to keep them separate.

eg

c:documentsdatajo blogsexcelfile.xls
c:documentsdatafred smithexcelfile.xls
c:documentsdatajane jonesexcelfile.xls

cells is the sheet contain the persons name

jo blogs
fred smith
jane jones

I want to be able to use this for others in the organisation and make it a simple as possible

Is there a way to use the cell contents in the path name (without opening the sheet first)

I know I can point to each cell in the other file and excel will use it but there must be a way of making this happen more automated by using the cell contents

View 5 Replies View Related

Loop Through Directory And Return Path File And A Value

Mar 15, 2014

I need a macro to loop through a dir and the sub directories to find xlsm files, when it finds one open, go to the sheet named data, look at c1 and if the value is between 12.1 and 13.4 then i need it to write the file path, filename to a1 in my excell sheet and then write the value from c1 in the original file to A2 of line in my excel sheet.

I have hobbled pieces of code together without any good results.

View 2 Replies View Related

Change To Directory Path Located In Cell

Oct 16, 2008

I am trying to use the same VBA code for different applications using variables.

Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.

sometimes I would want it to equal what was in a1 and sometime I would want what was in a2
a1 cell contains "f:filezilla files
a2 cell contains "f:dmc files
This is the code I started and it gives compile error when ChDir = sourceloc is reached

Public sourceloc

Sub filezillasettings()
Dim sourceloc As String
sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =

End Sub

View 7 Replies View Related

Automatically Remove Folder From Directory Path

May 9, 2007

i am wanting to automaticaly remove a file from a directory when this directory is populated and move to another directory that is secure, how would i do this? just say directory 1(where it will be removed from) is s:/ddc/reports

moved too-- s:/ddc/test

test folder will be secure

View 5 Replies View Related

Displaying The Current Opened File's Directory Path?

Sep 25, 2008

What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.

View 5 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

Feb 8, 2014

I have my code here:

VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")

[Code] ....

NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function

My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.

View 6 Replies View Related

Place Excel File Path As Link In Lotus Notes Email Body

Oct 29, 2013

I have an Excel sheet that I use as a mailing automatism for reports. As it currently is it attaches an actual copy of the excel workbook to the email and send them out. The mailer contains several different people, and they get different report each day. Due to the size of some of the files, I am starting to run into an issue where I cannot sent the emails anymore because they are too big, so I am wanting to switch to sending links to the files instead, and I have hit a wall.

I use Lotus Notes 8.5. The VBA will cycle through a range, and each cell has a list of report delimited by a ",". It takes the list and passes it to the mailer as a string. The mailer takes the string, turns it into an array and splits it out, and then checks to make sure the reports are current. One email could have up to 10 different reports in it. I have tried creating an HTML MIME email to include the links.

Here is the code I currently have:

Code:
Sub Send_HTML_Email(ByRef Name As String, ByRef Address As String, ByRef Reports As String)
Const ENC_IDENTITY_8BIT = 1729
'Send Lotus Notes email containing links to files on local computer
Dim NSession As Object 'NotesSession
Dim NDatabase As Object 'NotesDatabase
Dim NStream As Object 'NotesStream
Dim NDoc As Object 'NotesDocument

[code]...

View 1 Replies View Related

Find Username Match And Pull All Data At Same Row W/username

Oct 5, 2007

I have a thousand names on a column, and I have a several worksheet tabs as locations, such as CA, AZ, TX, and NY.

All names goes to main worksheet, show like this

apitchford 10 100 123
bkishpaugh 9 211 123
blee 14 234 111
cbonny 21 125 412

I need to pull a specific name, example, name = blee, move that name with all data on that row into TX.

I will need to set as automatically, becuase the names add and delete on the main worksheet, and will auto update those tab worksheets. (make sense?)

I have tried INDEX and MATCH, but they keep putting one data in, not all data.

View 9 Replies View Related

Macro To Copy And Place Data To Specific Place

Feb 22, 2007

I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.

View 2 Replies View Related

Save Hyperlink Path For File With Absolute Path And Non-Relative?

Mar 4, 2013

is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?

I notice that the hyperlink is ....pdf ry.pdf

if I change the position of the file excel there is a problem!

I would like to save es. d:invoichepdf ry.pdf

View 1 Replies View Related

Plan Path And Actual Path Distance Calculation?

Feb 11, 2014

I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.

My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.

I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.

View 1 Replies View Related

Macro To Separate File Name And Path From Complete Path

Oct 3, 2012

I want to run a macro to separate File Name and Path from the given complete path

For Example

Code:

In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf

I need it separeted like

Code:

In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1

Is there any way to do this through a macro

View 2 Replies View Related

List All Files From Directory And Sub Directory?

Sep 5, 2013

The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.

Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|

[URL]

View 8 Replies View Related

Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table

Oct 19, 2006

I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.

| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->

| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->

Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #

The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?

View 8 Replies View Related

Prompt User For Directory And Filename; Then Save File In Directory With File Name

Jun 10, 2009

I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.

View 3 Replies View Related

Trim Full File Name & Path To File Path Only

Sep 27, 2006

I have a variable ("DestFile") that defines a path to a file (used in saving the file)...

I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...

How would I go about trimming the "DestFile" address to get a "DestFldr" address?...

An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?

View 4 Replies View Related

API To Get Username

May 29, 2008

I have Timesheet workbooks with 3 levels of access (user, viewer & me as developer).

I want use the GetuserName API function (with If/Then) to auto open the wkbk if I am loged in under my network login so psuecode looks bit like this

If network user is me then open wkbk
else ask for username & password

I have googled & searched the forum & the closest I have is this

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next...............

View 9 Replies View Related

Get UserName Or Log In Name

Nov 10, 2006

I've got a sheet with a "Last Edited By:" field and I want this to be populated with the current users name i.e. I suppose I mean the user name that is assigned to Excel, as opposed to the XP log in... although I guess either would do. I'm OK on the change event thing, but just dont know the function to find the the current users name.

View 3 Replies View Related

Insert Username Using VBA

Jan 17, 2007

I'm trying to use the code listed below on the works shared drive. When I open up the worksheet and save it, my log on identity is left as required in Cell B2 etc.. When someone else uses the sheet it just leaves the company name in this cell and not their log on identity? The date & time function always works ok. Can anyone advise me if what I'm after is achievable.

The code I've used below was used from a previous string on a similar question.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now()
Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Application.UserName
End Sub[/b]

View 9 Replies View Related

Macro To Get Username

Jun 29, 2007

I have copied a macro off another board that get the username of the computer where the excel file is opened. It works fine on my pc but when I have sent the workbook to a colleague my username came up when he opened it. Also what I am trying to do is to modify the macro that does a vlookup on a range in worksheet 1 from where it picks name and surname (and transpose it onto worksheet 2 on cell A2), email (on B2), tel number (on C2) etc etc.

View 3 Replies View Related

Username / Password At Login

May 31, 2013

I have below piece of code which I'm not sure how to finish up. What I'm trying to do is have 'Username' and 'password' entered before logging into the workbook. It does not have to be 'UserForm" if it is possible, but it would be useful if we use Ucase...

I'm just not sure how this code works with

"Sub sUserLogon(strStandardID As String, strPassword As String)"

stated in this way..

VB:
Sub sUserLogon(strStandardID As String, strPassword As String)
Dim strUserName As String
Dim StrMessage As String
Application.DisplayAlertsAlerts = False
StrMessage = "User Logged In"

[Code] ......

View 2 Replies View Related







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