Macro - Query Active Directory With Multiple Usernames
Apr 13, 2014
I am trying to query Active Directory for a list of user attributes by using a list of usernames and output the results into column B,C,D.....
All the usernames are listed in column A and it ranges from 100 to 1000 usernames.
The macro GetAdsProp works but it is very slow because it's a function and every time it gets called to return a value, it takes a long time to query. It will take forever to get 1000 users.
I've also tried the code below, however one of the AD attribute that I am querying for has a dash (i.e. test-address) and I can't put a dash in the vba code because it automatically puts spaces in between the dash and the text (see red text below) which will fail to find the attribute. Also, I need it to loop the whole column A and not just one account.
Sub LoadUserInfo()
Dim x, objConnection, objCommand, objRecordSet, oUser, skip, disa
Dim sht As Worksheet
' get domain
Dim oRoot
Set oRoot = GetObject("LDAP://rootDSE")
[Code] .....
View 3 Replies
ADVERTISEMENT
Sep 8, 2006
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
View 5 Replies
View Related
Nov 23, 2011
I am trying to query our corporate directory to copy it into excel. The problem is that the table i want to copy has 90 pages. I do not know VBA very well at all. Is there code that I could input that would copy the table on page 1, paste to excel, then move to page 2 and copy/paste to the next empty row?
View 1 Replies
View Related
Jun 18, 2006
I am trying to wirte a macro that will allow my users to import data from a HTML file locatedon their local hard drive. THe directory information will not change however I would like the opporutnity to "choose" the appropriate file prior to completing the macro. Do I need to construct a form first?
Sub QuestionGetExtData()
Range("H11").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;C:foofoo.html" _
, Destination:=Range("A11"))
.Name = "foo.html"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False......................
View 2 Replies
View Related
Dec 11, 2008
From an Excel workbook userform, I want to capture a logon name and password, and then authenticate against Active Directory.
The only information I want from the process is whether the user name/password combination is valid in AD.
I know how to do all the Excel stuff (userform, etc.), and I know how to get information from AD using an administrator binding. What I am missing is being able to verify whether the Excel workbook user is the AD user that they claim to be.
View 9 Replies
View Related
Sep 14, 2012
Was wondering if it is possible to get below information from Active Directory into an Excel Sheet though VBA. All these details are within an OU in AD: UsernameDescriptionUser Logon NameFirst Name + Last NameTelephone Number I have been searching around and i think I need to connect using ADO, but not sure how to get these objects from AD.
View 1 Replies
View Related
Jun 6, 2013
The following code uses the GetAdsProp formula to get the user info properties from the Active Directory.
I am able to get the Firstname,Last name. But not able to get the data for the expiryDate.
[Code] ..........
View 1 Replies
View Related
May 31, 2006
The file that this code is located in is often sent to others to use which means I have to modify the directory and path each time I send this file in order for the VB code to work.
Workbooks.OpenText Filename:= _
"C:My documents-2Monthend Supplemental ReportsDeficiency ReportscurrentC37.txt"
Is there a way to have the file opened from any active directory it happens to reside in?
View 9 Replies
View Related
Mar 3, 2014
Please consider the following:
A
B
1
John.Doe
2
Jane.Doe
3
Mary.Roe
I am looking for a way to query an OpenLDAP instance, using the values in column A and looking up whether or not the username is active or not. The return value would be true/false in column B, which would indicate that yes, the username is active, or no, it is not.
View 1 Replies
View Related
Feb 26, 2013
When using
VB :ActiveSheet.QueryTables.Add
How would you get the code to overwrite whatever is in the worksheet as opposed to adding new columns?
View 1 Replies
View Related
Jul 30, 2007
I am trying to write a code which automatically inserts the users username in a cell once a macro button is pressed.
Sub Approve()
If ThisWorkbook.HasRoutingSlip = True Then
With ThisWorkbook.RoutingSlip
.Delivery = xlOneAfterAnother
Sheets("infosheet").Select
.Subject = "Here is " & ThisWorkbook.Name
.Message = "Here is the workbook. What do you think?"
End With
Workbooks("myfile.xls").Route
End If .....................
View 9 Replies
View Related
Jun 7, 2009
I need some code that will generate passwords from a combination of a master password and other words such as a user name. I have seen references to use of the MD5 algorithm but would not know if or how this could be incorporated into vba.
This would be used in an workbook which is distributed to different users. In order to use the workbook, they would need to enter their user name and password. The macro would check if these were correct or not. Because the password is generated by the macro, it avoids having to have them hidden in the workbook somewhere.
View 5 Replies
View Related
May 14, 2014
I currently have the following macro running to set a chart's data values:
Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _
[Code] ......
When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.
I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.
View 4 Replies
View Related
Nov 17, 2006
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
View 3 Replies
View Related
Feb 5, 2010
I was searching through the internet to look for some help with regards to consolidating data from multiple workbooks from different directories into a single worksheet called Summary.
I would like to copy the entire worksheet from different workbooks in different directories into this workbook I like to call Summary. To manually do it I have to open the workbook I want to copy the worksheet from. Right click on the tab and select Move or Copy, select the sheet I want to copy and select the workbook I want to copy this sheet to. Repeat this process for all the workbooks.
The next step is to copy and paste from these sheets into a main summary sheet. The data range from these sheets are starting from row 13 onwards and they may be of different size between Column A to Column W.
For example the workbooks are coming from :
S:Area 1West.xls from worksheet Andy
S:Area 1East.xls from worksheet Bill
S:Area 1North.xls from worksheet John
S:Area 1North.xls from worksheet Albert etc...
View 9 Replies
View Related
Jul 5, 2007
The title pretty much sums it up, actually. I have a macro, and a beautiful piece of work it is. It selects a particular selection of the active workbook, parses it in a particularly thrilling way, and writes the result to a text file. I'd like it to do this for ALL the files in a particular directory (somewhere over 500 files, if that matters).
View 2 Replies
View Related
May 16, 2007
Here is a project I can’t seem to do on my own if you can point me in the right direction I would be grateful! here is the best suedo code to describe the needs of the script:
In the open workbook named MASTER, on Worksheet named -MASTER- ( Let’s refer to this all as just MASTER)
(In production, the name of this workbook will be an account #_ date, and the worksheet will be a date)
For each numeric value in row 6 (we’ll call these values AD###) of MASTER
'*****Part one of routine*****
Search directory "H:AccountingAdvertising Accounts" for workbook named AD### AX.xls
(Note the AX suffix of the file name)
If match is found open workbook and proceed to Part two
If match is not found, goto Private Sub AD_MISSING
'*****Part two of routine*****
View 10 Replies
View Related
Mar 9, 2013
The code bellow allows me to save a copy of the invoice by way of doPDF using invoice number as the name of the pdf to be saved.
Code:
Else
Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _
[Code]...
As there is the need to, should a quotation or an invoice be wrong, to change it. In other words I have code to cancel the invoice as bellow.
Code:
Dim sResponse As String
Dim rFound As Range
sResponse = InputBox("Enter record you want to change." & vbNewLine _& "ATTENTION!
This will change the current record number and will erase record data from saved records sheet.")
If sResponse = "" Then --cancelled or OK with no entry: do nothing or add message
Else
Set rFound = Sheet1.Columns("L").Find(What:=sResponse, LookIn:=xlValues,
[Code]...
The code above simply removes a line on sheet1 to which yet another macro retrieved some info from an invoice such as date, invoice, customer and total.
Now I just realized that when I change an invoice, I will be again when finished, creating another pdf file that already exists.
How can I have the second macro here changed so that it will go to ""E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _" and delete this pdf file?
View 9 Replies
View Related
Nov 11, 2013
I have a 2 columns.
A: Current location Ex(C:UsersDesktoplabelsA02FAA.jpg)
B: Future location Ex(C:UsersDesktopoxes98300SA02FAA.jpg)
I want to move jpgs from location A to B
is there a way to execute this with a macro?
View 5 Replies
View Related
Aug 11, 2006
I got a task from my lab to write a macro in excel to "automatically email the detailes of the file listing from c: dir "
View 7 Replies
View Related
Oct 13, 2006
I would like to make a button that, when clicked, opens the following directory in a seperate window: "C:Documents and SettingssharkfootDesktopTNT" Here is what I tried...
Sub OpenLocker()
ActiveWorkbook.FollowHyperlink Address:="C:Documents and SettingssharkfootDesktopTNT"
End Sub
Because I am still a novice and because i wrote this myself, it doesn't work.
View 2 Replies
View Related
Jul 18, 2014
I am looking for some VB to import multiple csv files, stored in a single directory, into a single excel worksheet. However I am looking to also include all or part of the filename, so to differentiate each file.
I've attached an example spreadsheet, inc the below code, of what I am trying to achieve.
Current code is (this parsers all .csv files in the specified folder into a single worksheet):
[Code] ......
Attached File : csv_loader.xlsm‎
View 6 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
May 28, 2014
This Macro (Located within "SWMS Auto-Fill.xlsm) scans the folder its in and transfers information into select cells within each .xlsm file it finds. However I need to move the SWMS Auto-Fill.xlsm file to the parent directory.
So I need it to scan: Workbook DirectorySection4
Code is below:
VB:
Sub Autofill_SWMSs()
Dim MyPath As String
Dim MyFile As String
Dim Wkb As Workbook
Dim Cnt As Long
[Code]....
View 1 Replies
View Related
Nov 26, 2009
i'm looking for a macro which within a sheet named "foldernamedump" will list in a column the folder names within a directory I specify. I have seen a couple of sample codes but I just cant seem to get them working at all so I think its best to start from scratch and the work i get supplied here is always perfect. I also want the macro to clear the contents of the sheet before it loads again just to ensure there is no old data within the sheet.
View 5 Replies
View Related
Jun 17, 2014
I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?
ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.
View 1 Replies
View Related
Feb 23, 2010
I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.
The macros do this:
Source and Location directory defined in Sub Archive1.
These are passed to file copy macro Sub ArchiveScript.
This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.
However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)
- Invalid procedure call or element on fn = dir() at the bottom
- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.
View 6 Replies
View Related
Jan 10, 2012
I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.
View 4 Replies
View Related
Oct 3, 2012
Need macro to search approx 1000+ xls files in folder/directory for common text string "see reference" and then output the file number which is located in cell A1 to new spreadsheet for each file the text "see reference" is found.
View 2 Replies
View Related
Oct 23, 2012
What i am trying to do is in 1 workbook (labled as Book1 literally), it needs to copy the sheets out of every .xls file there is in a single directory, we'll call C:MyFolderMySubFolder. There can be anywhere between 1 and 366 files in this particular folder and I need all the sheets in each file labled 'CC' copy that entire sheet, paste that sheet to Book1, go back to that file it was copied from, close it (saving changes is ok), then move on to the next file.. and the next file... and so forth
While pasting into book1, I need each WS copied from each file to paste to a new worksheet in book1 rather than combining them into 1 or overwriting, and lable each of those sheets the file name of which the sheet came from...
The names are in sequence. All files in the folder will be labled as a date such as "9-6-12" so the sheet name in book1 would be named 9-6-12. (so there may result in 366 new worksheets to book1)
I primarily use Microsoft Office Excel 2003
View 5 Replies
View Related
