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